LOOKUP Formula - A Greate Alternative To IF Formula

The IF formula, in its simplest form, is to check whether the content in a cell fulfill a condition and return one of the 2 results pre-defined in the formula.

In the example above, the IF formula is used to find out whether the amount in a cell D2 is below 50, known as the condition. If the value in cell D2 is below 50 (i.e. true), return the value 30. If it is 50 and above (i.e. false), return the value 100.

When you have more than 1 condition, you can place a second IF formula within the first IF formula, all in one cell. We called this nested IF.

Instead of 100 in our original formula, it is replaced with another IF formula. This means that the first if formula will check whether cell D2 contains a value below 50. If D2 is below 50, it will return the value 30. Otherwise, the second IF formula is processed. In the same way, the second IF formula will check whether D2 contains a value less than 90. If cell D2 contains a value more than 90, the second IF formula will return 100. Otherwise, 450 will be returned as a result. Do take note of the positions of the brackets in the formula.

You can add more IF formulas to the nested IF formula. A maximum of 7 IF formula (i.e. 7 conditions) are allowed in one cell. What if you have more than 7 conditions? You have to use the LOOKUP formula as an alternative to IF formula.

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.

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)))))))))

This is an example of how our advanced Excel course in Singapore is conducted. If you go to a normal Excel course, you will learn about IF and LOOKUP. But you are unlikely to find out what to do if you need to put more than seven conditions into one cell.