Lookup Tables from the Web:  ABC ~ All ‘Bout Computers  Vol. 30, Feb. 2004

 

Lookup tables are fantastically useful things in Excel.  …

 

First of all, then, what's a data table? Well, there's one shown below:

 

	A	B	C	D
1	Name	Age	Location
2	Adam	39	Los Angeles
3	Barbara	23	Caron
4	Charlie	30	Denver
5	Debbie	31	Belfast
6	Elvis	28	Indiana

 

You'll notice that some related data is set out in columns, each with a heading in bold at the top. So many other functions in Excel can use those headings intelligently, that I have always made it a habit to put them in. Data tables like this have so many uses it's difficult to know where to start: phone number lists, CD collections, customer lists, the uses are endless. But sooner or later, you're going to want to extract data from such a list, perhaps for a mail merge or to fill in an invoice automatically.

 

Probably the best way of learning about the LOOKUP functions is to ask some questions and use formulae to answer them. For instance, look again at the data table above. If I want to know Barbara's age, I can use a command called VLOOKUP. It's called VLOOKUP because it looks up the data in a table, based on finding the key in a Vertical list.

 

The formula I'd use here is:   =VLOOKUP("Barbara",A2:C6,2,FALSE)

 

Of course, this will return the number 23, which is Barbara's age. Let's look briefly at the format of the function. The first argument is the piece of data I want to look up (what I call the 'key') in the first column. (This must always be in the first column, but later on I'll show you how to find values based on a key in other columns instead.)

 

The second argument is the range which contains the table, in this case A2:C6. I'd normally have named this range, but you don't have to.

 

The third argument is the column number I want to return the value from. Looking at the table again, the first column contains names, the second ages and the third locations. Clearly, if I want to find Barbara's location, I'd put a 3 in this argument, but since I want to know her age, I've used 2.

 

The fourth argument, the FALSE, is supposed to be an optional argument, but my advice is ALWAYS to use it. What it means is, "Don't rely on the list of items in the first column of the table being in alphanumeric order - check every one of them until you get an exact match."

Leaving it out is like saying, "The first column of the lookup table is definitely in alphanumeric order - if you get past the search phrase in the list and it's not there, don't keep looking, just use the nearest match". This would speed up your sheet if there were a lot of huge data tables in it, but here it's not even worth thinking about. It's good practice always to include "FALSE" just in case it trips you up one day.

 

Well, of course there's an HLOOKUP to match the VLOOKUP, too. You'd use this when your table is oriented left to right, rather than top to bottom. Here is an example of what I mean:

 

	E	F	G	H	I	J
1
2	Name	Adam	Barbara	Charlie	Debbie	Elvis
3	Status	Married	Divorced	Widowed	Separated	Single
4	Birthday	December	March	July	April	February
5

 

I can use the HLOOKUP function to find what date Debbie's birthday falls in like this:

 

=HLOOKUP ("Debbie",F2:J4,3,FALSE)

 

This formula returns April. The arguments work the same way as for VLOOKUP, except of course that the third argument refers to the row number rather than the column number. On

 

=MATCH(“Belfast”, C2:C6,0)

 

This will tell us that Belfast is number 4 in the list so we can put the number 4 into an INDEX formula:  =INDEX(A2:A6,4)

 

Of course, this formula will return the name 'Debbie', which answers the original question. But in the same way that original question is made up of two sub questions, so we can turn our two formulas into a single one, like this:  =INDEX(A2:A6,MA TCH(“Belfast" ,C2:C6,O))

 

Again, this gives us the answer 'Debbie'.    Of course, we've looked so far at simple tables, and it's been far quicker just to look at our table and answer the questions than to sit down and write formulae! However, there will be times when the data tables are huge, or when you want Excel to work things out for itself and get on with things. At times like those, you'll find that the LOOKUP functions of Excel are an invaluable part of your toolkit.