I have this spreadsheet of insurance plans. Goal is to identify the cheapest plan given premium and expected annual medical expenditures.
10 columns, one column per plan
Row 2: Plan Name (in cells B2, C2, D2…). There is a text value in Row 2
Rows 3-29: Bunch of mathy stuff in formulas
Row 30: Total Cost (in cells B10, C10, D10…). This is also a formula.
Below this table I have a much smaller table which simply tells me the lowest cost plan of the 10, using a MINIMUM function. Let’s say this function is in A32. (Don’t know if that is necessary for this, but there it is.)
Currently, the function returns the correct value (lets say it’s in E30). But what it returns, of course, is the $ amount in cell E30. However… and this is the question… I also want to return the value in cell E2 (the plan name) as well.
Right now it’s telling me “Your cheapest plan is $3,043.14” (in cell E30).
I want it to “say”, “Your cheapest plan is $3,043.14, and it’s the BCBS-666SUX plan” (a value which can be found in cell E2).
So, effectively, I have an array in row 30 - I want to search that array to find the lowest value, returning the value found in an array in row 2, same column.
Uhhh… what else do y’all need to know? Did I even describe my problem correctly?
I think you can do an hlookup, where you take the value indicated, lookup that value in the total cost row, then ‘offset’ by the correct number of rows to get you to the plan name. You may need to use the “false” indicator at the end of the function to require an exact match.
Index Match may also accomplish this, you can search google for ‘index match excel’ for the exact formulation.
Yep, HLOOKUP. What you’ll want to do is create a row 31 that has the name of the plan in it (You can hide this row if you think it looks ugly. Use =B2 rather than a copy paste in case you want to change the plan name later.
You’ll then type
HLOOKUP(A32,B30:K31,2,0)
The B30:K31 is the Array of values you’re looking in where the value you want to match is in the top row, the ‘2’ tells it that you want it to return the second row from the array, the 0 says exact match only.
You’d love one file I inherited, it had 52 nested IF statements, one for each week of the year. It wouldn’t all fit in one cell though, so they did 8 or 9 weeks in one cell, then if the week wasn’t found, kicked it to the next cell over, which had IF statements for the next 8 or 9 weeks, repeat until you need an aspirin.
That’s not true. If you’re using an exact match (The 0) at the end, then it doesn’t need to be sorted. If you’re looking for ‘closest’ then you would be correct.
Actually, it works just fine using Senoy’s function (modified to fit the actual table, of course). And the values aren’t sorted at all.
Thanks, Senoy! I’m going to use this thing… well, a cleaned-up version of it… to help people choose health plans based upon estimated annual medical expenses. This will make a lot of peoples decisions easier when we are enrolling them. Appreciate it greatly!
Did you accidentally end it with a 1 so something like this?
HLOOKUP(A32,B30:K31,2,1)
or did you omit it?
HLOOKUP(A32,B30:K31,2)
Either way, that causes the ‘Closest match’ functionality to kick on. What that will do is cause Excel to go through the list until it finds the first value greater than your comparison value and return the item listed before it.
So if your data set is
3 5 8 4 2 1
a b c d e f
and you type HLOOKUP(“4”,A1:B6,2) it would stop at 5 and return the value in 3 so ‘a’. If you add on the exact match ,0 at the end HLOOKUP(“4”,A1:B6,2,0) then it will return ‘d’.
I don’t know what I’d done, but I just went back to that spreadsheet and un-sorted the table, and added back the ,0 option and it’s working fine. Thanks…
My “issue” comes from the precision of the calculation.
.
The hlookup/Index Match will give a plan with the lowest value. Precisely.
If there are two matching lowest quotes the formula will always give you the first occurring plan.
This may not is a practical issue if “Rows 3-29: Bunch of mathy stuff in formulas” calculations works so as each answer is unique. But it does mean that “Your cheapest plan” may be cheapest by only millionth of a cent or less. Even if you added round(A32,2) you are forcing decisions on the selection of plan by what may be immaterial amounts.
What might be helpful for your workbook to have some logic which says:
This is your lowest plan, and there are two other plans where the premium is less than (say) $1/year more than that and list those plan names as well.
This may be just pedantry but if that might help you, come back to me and I’ll thunk it out.
“Logic” and “Health Insurance Plan Pricing” are concepts with a passing, at best, familiarity with each other.
Seriously, though, appreciate the heads-up. I don’t think it’s going to be an issue with this spreadsheet given how the agency (and small group health insurance premiums) operates, but I’ll be on the lookout for that.
How does someone like the original creator of that know enough to do 52 nested IF statements, but doesn’t know how too look up WEEKNUM or figure out how to use something like QUOTIENT(X,7)? Or just make a 52 row lookup table.
Worst Workbook I inherited used a week number, calculated on one sheet, to then on another sheet, calculate the budgeted revenue up to the last day of the previous month. You were then supposed to go in and modify the rest of the formula to approximate the budgeted revenue for the (partial) current month. First thing I fixed when I took that over.
Actually no, the worst spreadsheet I ever inherited was actually a Word document. Consisting entirely of two tables. That multiple people were supposed to update as new bookings came in. At the top of the first page were instructions to then manually recalculated the expected number of bookings/headcount and then change the totals in the table. :eek: No one actually involved in the project thought that this was in any way an issue, until I had to come in and explain the variance between expected and actual revenue.
