Retrieve excel row value by checking if value with

问题:

Any idea for an excel formula that should accept an input and return the value of the relevant row in Price column if the value entered is greater than the LowerRange column but lower than the UpperRange column.

i.e. input of 6 would return 5, input of 18 would return 10, input of 52 would return 30.

LowerRange  UpperRange  Price
1           10          5 
11          20          10
21          30          15
31          40          20
41          50          25
51          60          30

InputCell:
InputFormula:

回答1:

Use VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) with range_lookup set to TRUE

From Excel help file:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

So, for your data use =VLOOKUP(B10,$A$2:$C$8,3,1) where value to look up is in B10 (add a value of 61 to col A and =NA() to col C to the end of the data to handle inputs > 60)