**Use Microsoft® Excel Differently to Gain a Productive Edge**

*Issue - Jun 2009*

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.

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.

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.

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.

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.