# Excel: Lookup a row of data, return value in another row **URL:** \ **Category:** Factual Questions\ **Created:** [December 14, 2018, 4:30pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125 "2018-12-14T16:30:17Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![JohnT](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnt/32/15048_2.png) [@JohnT](https://boards.straightdope.com/u/JohnT)\ **Post date:** [December 14, 2018, 4:30pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/1 "2018-12-14T16:30:17Z")
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? Thanks in advance! ---
**Author:** ![Cheesesteak](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cheesesteak/32/3694_2.png) [@Cheesesteak](https://boards.straightdope.com/u/Cheesesteak)\ **Post date:** [December 14, 2018, 5:19pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/2 "2018-12-14T17:19:14Z")
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. ---
**Author:** ![senoy](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@senoy](https://boards.straightdope.com/u/senoy)\ **Post date:** [December 14, 2018, 5:44pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/3 "2018-12-14T17:44:18Z")
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. ---
**Author:** ![sitchensis](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sitchensis/32/4892_2.png) [@sitchensis](https://boards.straightdope.com/u/sitchensis)\ **Post date:** [December 14, 2018, 5:48pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/4 "2018-12-14T17:48:28Z")
=IF(B9=(MIN(B9:E9)),B3,IF(C9=(MIN(B9:E9)),C3,IF(D9=(MIN(B9:E9)),D3,IF(E9=(MIN(B9:E9)),E3,"")))) ect. If, like me, you like really long IF statements ---
**Author:** ![Cheesesteak](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cheesesteak/32/3694_2.png) [@Cheesesteak](https://boards.straightdope.com/u/Cheesesteak)\ **Post date:** [December 14, 2018, 5:51pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/5 "2018-12-14T17:51:58Z")
> [@sitchensis](#): > > =IF(B9=(MIN(B9:E9)),B3,IF(C9=(MIN(B9:E9)),C3,IF(D9=(MIN(B9:E9)),D3,IF(E9=(MIN(B9:E9)),E3,“”)))) ect. > > If, like me, you like really long IF statements Gah! 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. ---
**Author:** ![scr4](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@scr4](https://boards.straightdope.com/u/scr4)\ **Post date:** [December 14, 2018, 6:08pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/6 "2018-12-14T18:08:10Z")
HLOOKUP will only work if the table is sorted by row 30 values (i.e. row 30 is monotonically increasing). [This article](http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm) talks about INDEX and MATCH functions which should get around this limitation. ---
**Author:** ![senoy](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@senoy](https://boards.straightdope.com/u/senoy)\ **Post date:** [December 14, 2018, 6:13pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/7 "2018-12-14T18:13:40Z")
> [@scr4](#): > > HLOOKUP will only work if the table is sorted by row 30 values (i.e. row 30 is monotonically increasing). > > [This article](http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm) talks about INDEX and MATCH functions which should get around this limitation. 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. ---
**Author:** ![JohnT](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnt/32/15048_2.png) [@JohnT](https://boards.straightdope.com/u/JohnT)\ **Post date:** [December 14, 2018, 6:24pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/8 "2018-12-14T18:24:13Z")
> [@scr4](#): > > HLOOKUP will only work if the table is sorted by row 30 values (i.e. row 30 is monotonically increasing). > > [This article](http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm) talks about INDEX and MATCH functions which should get around this limitation. 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! ---
**Author:** ![scr4](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@scr4](https://boards.straightdope.com/u/scr4)\ **Post date:** [December 14, 2018, 6:30pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/9 "2018-12-14T18:30:45Z")
> [@senoy](#): > > 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. Huh. Thanks for that correction, I thought I’d tried it that way recently and I couldn’t get it to work, must have been some other problem… ---
**Author:** ![senoy](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@senoy](https://boards.straightdope.com/u/senoy)\ **Post date:** [December 14, 2018, 6:38pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/10 "2018-12-14T18:38:38Z")
> [@scr4](#): > > Huh. Thanks for that correction, I thought I’d tried it that way recently and I couldn’t get it to work, must have been some other problem… 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’. ---
**Author:** ![scr4](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@scr4](https://boards.straightdope.com/u/scr4)\ **Post date:** [December 14, 2018, 6:51pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/11 "2018-12-14T18:51:03Z")
> [@senoy](#): > > Did you accidentally end it with a 1 so something like this? 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… ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [December 14, 2018, 9:01pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/12 "2018-12-14T21:01:35Z")
> [@Cheesesteak](#): > > I think you can do an hlookup, … > Index Match may also accomplish this Yep, Cheesesteak is on the money. My “issue” comes from the precision of the calculation. > [@JohnT](#): > > 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). . The hlookup/Index Match will give **a** plan with the lowest value. Precisely. 1. If there are two matching lowest quotes the formula will always give you the first occurring plan. 2. 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. ---
**Author:** ![JohnT](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnt/32/15048_2.png) [@JohnT](https://boards.straightdope.com/u/JohnT)\ **Post date:** [December 14, 2018, 9:59pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/13 "2018-12-14T21:59:59Z")
“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. ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [December 14, 2018, 10:45pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/14 "2018-12-14T22:45:33Z")
The INDEX/MATCH combo is usually more powerful and flexible than HLOOKUP or VLOOKUP. Honestly I didn’t think HLOOKUP was supported anymore. ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [December 14, 2018, 10:52pm UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/15 "2018-12-14T22:52:18Z")
To late to edit: If A31 contains the $ amount of the cheapest plan, you can get the name of the plan with =INDEX(A2:J2,1,MATCH(A31,A30:J30,0)) ---
**Author:** ![The\_Lurker\_Above](https://avatars.discourse-cdn.com/v4/letter/t/dc4da7/32.png) [@The\_Lurker\_Above](https://boards.straightdope.com/u/The_Lurker_Above)\ **Post date:** [December 15, 2018, 12:53am UTC](https://boards.straightdope.com/t/excel-lookup-a-row-of-data-return-value-in-another-row/826125/16 "2018-12-15T00:53:38Z")
> [@Cheesesteak](#): > > Gah! > > 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. 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.