Search code examples
excelexcel-formulaexcel-2007

Excel function to choose a value greater than or less that a particular value in cell

I have a data set something like this

Units   Price
   1    15
 100    10
 150    9
 200    8
50000   7

I need the output as Price with respect to quantity.

Example- If Input value is 90 it should give price as 15 If input is 210 it should give value as 8.

However,sadly I cannot use IF statement. Thanks in advance.

Solution

  • You can use a combination of INDEX and MATCH

    =INDEX(B1:B5,MATCH(lookup_value,A1:A5,1))

    This assumes Units are in column A and Price is in column B

    Make sure you understand both functions:

    • INDEX
    • MATCH - particularly the reason for the ,1) at the end