Excel Lesson: More Advanced Table Lookup Functions

Topics Covered:
MATCH()
INDEX()
2-Way lookups (find data in a table based on entry in one column and one row)
2-Column lookups (find data based on results of lookups done in two columns.

Real World Example Application:

While Excel provides some very strong built-in functions for doing lookups of data within tables,
those are mostly built around the lookup seeking a match in a single column/row of information
and then returning related information about that match from within the table. For more on those
features read the ExcelLookupsLesson.pdf1 Lesson.

But sometimes those functions just don’t do the job. Sometimes you may need to find a value
based upon a match in the values in the top row of a table combined with a match of the values
in the first column of the table. Or you may need to find a value based on the values in pairs of
column entries. In these cases, VLOOKUP(), HLOOKUP() and LOOKUP() just won’t do the
job.

Consider a sales chart – across the top are the months of the year, and down the left side are
various products sold. At the intersection of these column/row combinations are quantities sold
or total dollars for the sales in that month for that item. To find out how many widgets were sold
in February, you need to be able to do a 2-Way lookup.

Or maybe you have a list of merchandise with a few options available that cause changes in the
cost of the merchandise (as models of vehicles or clothing items with optional accessories). In
this case you may need to use a 2-column lookup.

To implement a 2-way or 2-column lookup, you need to use two built in functions from Excel:

MATCH() - Returns the relative position of an item in an array that matches a specified value in
a specified order. Use MATCH instead of one of the LOOKUP functions when you need the
position of an item in a range instead of the item itself.

INDEX() - Returns a value or the reference to a value from within a table or range.

Specific information on how both the MATCH() and INDEX() functions work, their syntax, and
basic use can be found in ExcelMatchAndIndexLesson.pdf2.

And before continuing, if you are not familiar with setting up equations in Excel, addressing and
Named Range definitions and use, please read ExcelBasicsLesson.pdf3 before continuing.

1
http://www.jlathamsite.com/teach/ExcelLookupsLesson.pdf
2
http://www.jlathamsite.com/teach/ExcelMatchAndIndexLesson.pdf
3
http://www.jlathamsite.com/teach/ExcelBasicsLesson.pdf

Copyright © 2004, Jerry L. Latham, All Rights Reserved. Page 1 of 5

Some portions Copyright © by The Microsoft Corporation.
The two-way lookup analyzed. For this lesson you’ll need to look at the
ExcelMoreLookupsLesson.xls4 workbook. The TwoWayLookups worksheet looks like this:

I’ve laid this out all in final fashion with a few added items to help explain things. For instance,
the list of Named Ranges and the two Indexes displays aren’t actually needed for use, they are
there for explanation.

A couple of things to notice: Because we have defined our SalesTable range to extend from A1
to M7, we also have to include cell A1 in both our MonthList and ProductList so that the
MATCH() functions used with them return the proper index values into the table.

If you click in either cell C10 (TheMonth) or C11 (TheProduct) you will see a pull-down arrow
appear at the right edge of the cell. Clicking on it gives you a list of valid entries. If you have
not seen/done this before, it was done by clicking in each cell and then choosing Data /
Validation and working from there. If you wish to see the setup for those cells, click in them and
choose Data / Validation to see their setup.

A couple of quick notes about Data / Validation using a list as was done here:
Advantage: gives your users a complete list of properly spelled and formatted items to pick
from. This increases accuracy and reduces the possibility of error.
Disadvantage: the list used for selection must be on the same sheet with the cell using the list.
This can cause you to have to create worksheets with extra areas on them to hold the lists and
have to set the sheets up for printing with this thought in mind (think of restricting the Print Area
to exclude the source of the lists).
Maintenance: by using a defined range with absolute addressing like $B$1:$M$1 you have to
watch out for additions/deletions from the list and/or movement of the list around on the
worksheet. Better to use Named Ranges for this kind of thing.

4
http://www.jlathamsite.com/teach/ExceMoreLookupsLesson.xls

Copyright © 2004, Jerry L. Latham, All Rights Reserved. Page 2 of 5

Some portions Copyright © by The Microsoft Corporation.
The real workhorse cell on the sheet is at C12. The formula for that cell looks like this:
=INDEX(SalesTable,MATCH(TheProduct,ProductList,0),MATCH(TheMonth,MonthList,0))

It looks a bit complicated, but really isn’t. Remember that the INDEX() function needs three
parameters to do it’s job, and those are (as they relate to this example)
Parameter #1: The address reference to the table where the looked-up information is to come
from.
Parameter #2: The row offset value into the table specified by Parameter #1.
Parameter #3: The column offset value into the table specified by Parameter #1.

Since the MATCH() function gives us values we can apply as index values, we just use that
function to determine those in the proper order “on the fly” so to speak. If you look down where
I’ve placed the Indexes information, you can see the actual values they return for any given
choice of either product or month. Since those values are there, we could have written our 2-way
lookup formula like this:
=INDEX(A1:M7,C15,C14)
Or using the data table’s name,
=INDEX(SalesTable,C15,C14)

But by including the MATCH() functions as part of the formula in cell C12, we don’t have to
even have anything like cells C14 and C15 on our worksheet – saving worksheet space, system
resources, and keeping our sheet neater.

Copyright © 2004, Jerry L. Latham, All Rights Reserved. Page 3 of 5

Some portions Copyright © by The Microsoft Corporation.
Two-Column Lookup

We will begin by taking a quick look at the worksheet that’s been set up to help with this:

When would you need such a lookup? You might need a 2-column lookup in cases where none
of the VLOOKUP(), HLOOKUP(), LOOKUP() or even 2-way lookups works. With all of those
the intersection of rows and columns forms results in a single value. If you look at the table of
information above you see that each Model and # of bedroom combinations has 2 more things
associated with them: model number and number of baths. You could stretch that on out to
include pricing, square footage and many other items that simply wouldn’t fit into a single cell
reasonably.

I’ve set up a couple of selection cells there at B4 and B5 so you can choose items from the two
columns we’ll be using to finish out our lookup. Cell B6 shows the results (note that it will
display #N/A if no lookup match is found – as with choosing any Model other than the ‘Palace’
with 5 bedrooms). The stuff out in columns L and M is mostly informational, although the lists
used for the pull-down validation in cells B4 and B5 are out there also.

The heart of this whole thing is an odd looking formula in cell B6:
{=INDEX(ModelCode,MATCH(B4&B5,Models&Bedrooms,0))}

The first thing you probably notice is that it doesn’t start with an = symbol, but with a left curly
brace {. How’d that get there?

It got there because after typing in the formula in the normal fashion (including starting it with
an = symbol) we ended it not with just the [Enter] key, but with a key combination:
[Shift]+[Ctrl]+[Enter]. That has to be the terminator for entry/editing of what are known as
array formulas in Excel.

What/how array formulas are is a difficult concept to explain in a few words. Try to look at
them as being formulas that don’t just look at one cell or range of cells, but rather they look at
ranges of cells and evaluate all cells within that range at once as individuals. Told you it wasn’t
easy to explain. I’ll try again: in this case Models refers to the range E5:E16 and the range

Copyright © 2004, Jerry L. Latham, All Rights Reserved. Page 4 of 5

Some portions Copyright © by The Microsoft Corporation.
Bedrooms refers to F5:F16, and the way we’ve used the ampersand (&) symbol tells Excel to
pair up the contents of each pair of cells in those ranges one at a time so that internally it sees
“Colonial2”, “Colonial3” … “Palace5” as it works through the list of items in those ranges. Just
before doing that it had pulled the contents of cells B4 and B5 together in similar fashion for the
comparison. Excel is smart enough to figure out which are individual cell references and which
ones it needs to use to perform the added evaluations. Array formulas are extremely powerful,
allowing you to do things that cannot be done otherwise outside of a Visual Basic for
Applications (VBA) code module – and generally the array function works faster than the VBA
code to do the same thing.

Could we have done this any other way, without resorting to an array formula? The answer is an
unqualified YES.

You could have set up a column left of the model numbers column and set it up as is shown
down in the lower area of our sheet:

We added one formula in Cell C25 there next to the cell where we choose # of bedrooms:
=B24&B25
That gives us our first parameter for the VLOOKUP() formula we set up in the results cell there
next to the Model # phrase:
=VLOOKUP(C25,G24:I35,2,FALSE)

Cells G24:I35 include all the data under the columns labeled “Added”, “Model#” and “Baths” in
our data table. Notice in the “Added” column, we’ve used a formula like
= En & Fn
Where “n” is the row number. This does the concatenation (pulling together) of the two pieces
of information we need to match to our value used in the VLOOKUP() and let it replace the
fancy-schmancy Array Formula. Obviously the array formula method is a bit cleaner with
regards to worksheet layout, at the expense of perhaps losing some of the understandability, and
adding a little confusion during editing operations if they are required later (remembering to
terminate the edit with that 3-key combination to keep it defined as an array formula).

ENJOY!

Copyright © 2004, Jerry L. Latham, All Rights Reserved. Page 5 of 5

Some portions Copyright © by The Microsoft Corporation.