ExcelBanter - Return value in table associated with next greatest

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Return value in table associated with next greatest (https://www.excelbanter.com/excel-worksheet-functions/266138-return-value-table-associated-next-greatest.html)

fuji

Return value in table associated with next greatest

I have a table of Data:

HP COST
3 $250
5 $400
7.5 $450
10 $500
15 $650
20 $750
25 $800

User Enters 7.2

I need to return the cost associated with the next greatest HP (7.5), or $450. I know VLOOKUP would return the next less, or $400.

wickedchew

Quote:

Originally Posted by fuji (Post 959839)

Say the user inputs 7.2 in cell C1.

To output $:
=INDEX(B1:B7,MATCH(C1,A1:A7)+1)

To output HP:
=INDEX(A1:A7,MATCH(C1,A1:A7)+1)

All times are GMT +1. The time now is 06:14 AM.