LOOKUP formula in Excel works in a similar way as VLOOKUP and HLOOKUP formula. The formula is entered into a cell in the following format.

The formula will take the your input value (e.g. D4) and try to find that
value in the 1st set of values

(4000,10000,20000,25000,30000,60000,70000,91000,100000).

When the value is found in the 1st set, it will take note
of its position in the first set and use this position to identify
the value that is in the same position in the 2nd set

(300,200,100,400,500,600,700,800,900)

For the formula
to work accurately, you need to organise the data in ascending order.

This is similar to writing the following Nested IF formula

=IF(D4=4000,300,IF(D4=10000,200,IF(D4=20000,100,"#N/A")))

If there is no value that
is equal to your input value, the formula will return a value that is
smaller than your input value.
If more than one
value in the range are smaller than the input value, it will take the largest
value in that range. Its position is used to find the value in the second set that
is in the same position. The formula is entered as follows into cell D8 with
35000 (in cell D4)
being the input value (**take note of the curly brackets and semi colon**).

=LOOKUP(35000,{4000,10000,20000,25000,30000,60000,70000,91000,100000;300,200,100,400,500,600,700,800,900})

30000 (in 5th position) is identified as the largest value in the following set

**4000,10000,20000,25000,30000**

where the values are all smaller than 35000.

The formula will return the corresponding value (500) which is in the
5th position in the second set of numbers

300,200,100,400,500,600,700,800,900

In this case, the formula has the same effect as Nested IF formula below.
The first outcome is #N/A, the same outcome if you input a less than 4000 in our
LOOKUP formula above.

=IF(D4<4000,"#N/A", IF(D4<10000,300, IF(D4<20000,200, IF(D4<25000,100, IF(D4<30000,400, IF(D4<60000,500, IF(D4<70000,600, if(D4<91000,700, if(D4<100000,800, 900)))))))))Note that the above IF formula will not work in Excel because it has more than 7 conditions. In this case, the LOOKUP formula has to be used.

Master the business uses of excel formulas through our
"**Sales Marketing Analytics and Report with Excel eCourse**".

•

## New! Comments

Have your say about what you just read! Leave me a comment in the box below.