April 29, 2004


There are different kinds of lookup functions available. VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE and many more. But LOOKUP by itself is probably the most simple and easiest to use.

How does it work?
First we need a reference cell and at least 2 lists to make a comparison (cross-reference). In the regular LOOKUP function the 2 lists should be vertical.

Say I wanted a quick reference to the days in a month.

As you can see I used reference numbers from 1-12 fro the months of the year in cells A2:A13 and the days in the month in cells C2:C13. (Cell B2:B13 show the month's names just to make it easier to understand. Although I can use text for reference in LOOKUP, I prefer to use numbers because it is so much faster)

For February, I've entered the month's reference number shown in cell A3 into cell A16. Then in cell B16, I've entered the following formula =LOOKUP(A16,A2:A13,C2:C13).
This means that the number I've entered in cell A16 will be searched for in list A2:A13, and then cross-referenced in list C2:C13 to give me a value of 28.

But what about leap years? Okay, I can use another LOOKUP function for this too, by adding a new list in cells D2:D13 and entered this formula into cell C16, =LOOKUP(A16,A2:A13,D2:D13).

Yesterday I used logic formulas to choose alternatives between different customers to calculate their shipping time. With just 2 customers, it was starting to get quite difficult to avoid potential errors in calculation. Now let's apply LOOKUP to the task.

Say my customers have now increased. I have a total of 10 customers and I use 2 different modes of shipping for each of them. (It doesn't matter whether it is by courier, truck, air or sea. The important thing is there are 2 modes available for each customer)

I've entered the customer's reference numbers in cells A2:A11, customer's names in cells B2:B11, and days required for either shipment mode 1 or 2 in cells C2:C11 and D2:D11 respectively.

I enter the customer's reference number in cell A14 and the shipment mode in cell B14.
In cell C14, I have entered this formula, =IF(B14=1,LOOKUP(A14,A2:A11,C2:C11),LOOKUP(A14,A2:A11,D2:D11)).

It means to check the shipment mode in cell B14 and then cross-reference the list A2:A11 with list C2:C11 or list D2:D11 depending on the shipment mode. Then in cell D14 I enter the shipment date, and enter =C14+D14 in cell E14 to give me the arrival date.

Somewhat complex, but trying to do all of this with only logic formulas by themselves would involve a lot more work. LOOKUP does it all for me.

This page is powered by Blogger. Isn't yours?