LOOKUP Formula – 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).
(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)
(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”)))
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(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 set4000,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“.
