Find next highest value in table and output the cell next to it I have a table w

Question

Find next highest value in table and output the cell next to it

I have a table with two columns, the first column is what I want to output and the second column contains areas that I want to find a greater value compared to my input. What I am trying to do is obtain an area by dividing one cell by another, and using this area I want to look into my table, find an area that is greater than what I just calculated, and then output the cell next to this greater area. VLOOKUP is not going to work and I can't seem to get INDEX MATCH to work. My formula is =INDEX(L5:M12,MATCH(I5/C5,M5:M12,-1),1) and I included a snapshot below. I want this formula to return "#6" since the area I want to be gre!aterr than sis 2.88/8=0.36. I wouldd prefer it to not require additional helper columns with intermediate steps that I hid#e.

Explanation / Answer

The issue is with your MATCH function. The -1 type requires the list to be in descending order, your list is in ascending order. If you reverse the order of your list your formula would work as is

If you don't want to (or can't) reverse your list use this

=INDEX(L5:M12,MATCH(I5/C5,M5:M12,1)+1,1)

This will work, since it finds the position of the largest value less that or equal to the lookup value, then adds 1

One thing to watch out for with either case is what happens if the value is a exact match to your criteria. What if #6 was .36? Do you want the result to be #6 or #7 (the next greater)?

The formula I gave you would return #7 in this case, if you want the result to be #6 you need to check for an exact match like this

=INDEX(L5:M12,MATCH(I5/C5,M5:M12,1)+IF(ISERROR(MATCH(I5/C5,M5:M12,0)),1,0),1)

If you resort the list and use the -1 type the result would be #6. If your want #7 you also need to check for an exact match

=INDEX(L5:M12,MATCH(I5/C5,M5:M12,-1)+IF(ISERROR(MATCH(I5/C5,M5:M12,0)),0,-1),1)

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.