everydayExcel Newsletter Header

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.

=IF(D2<50,30,100)


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.

=IF(D2<50,30,if(D2<90,100, 450))


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.

lookup formula

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.

lookup exact find

This is similar to writing the following Nested IF formula

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

Note: I have reduced the number of conditions to 3 for the IF formula.

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 formula not exact

=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.

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.


Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.


Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.


Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"

 

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.