Exceljet

Quick, clean, and to the point

This video is part of our online video training.

How to use VLOOKUP with an Excel Table

Tags 
Summary 
This video shows how to setup VLOOKUP with a table, and how to use the MATCH function inside VLOOKUP to generate a dynamic column index. This makes it easy to refer to columns by name inside VLOOKUP, instead of hardcoding the column number. As a bonus, you can reorder the columns and the formula will keep working correctly.
Video Transcript 

In this video, we'll look at how to use VLOOKUP to lookup values in an Excel Table.

On this worksheet, I have a table that contains employee data, named Table1.

To illustrate how to work with VLOOKUP when the source data is in a table, I'll set up formulas to the right to extract data from the table, matching on an employee ID.

First, I'll select the table header and use paste special with transpose to get the field values.

Then I'll add some formatting, and an ID value so I have something to match against.

Now I'll write the first VLOOKUP formula.

For the lookup, I want the value from K4, locked so it doesn't change when I copy the formula down.

For table array, I want the lookup table itself, Table1.

Now, because VLOOKUP only looks to the right, it's important that the lookup is to the left of values we we want to retrieve.

In this table, the ID is the leftmost column, so we can get any value.

For column ID, I need 2, since the first name is the second column in the table.

Match type is zero or false, since I want to force an exact match.

=VLOOKUP($K$4,Table1,2,FALSE)

When I enter the formula, we get "Julie", which is correct.

If I copy the formula down to the next row, I only need to adjust the column number to get last name.

And, I can do the same for all the other fields.

You might wonder if there's an easy way to avoid hardcoding the column number in the formula?

The answer is yes. Since the names in column J match values in the table header, I can use the MATCH function get an index for each field.

To demonstrate, I'll use MATCH in column L by itself.

The lookup value comes from column J.

The array is the table header.

Match type is zero, for exact match.

=MATCH(J5,Table1[#Headers],0)

When I copy the formula down, I get a numeric index for each field.

Now I just need to copy MATCH formula into VLOOKUP to replace the hardcoded column index.

This is an example of nesting functions inside a formula.

=VLOOKUP($K$4,Table1,MATCH(J5,Table1[#Headers],0),FALSE)

When I copy the formula down, I get a result for each field. 

I'll go ahead and remove the helper column.

When I change the ID value, everything works.

And, because I'm using a table, I can easily add more data, and the same formulas will continue to work without edits.

Finally, using match this way provides a really nice benefit: I can easily reorder fields in the output formulas, or, in the table itself, and the VLOOKUP formulas keep working.

Author 
Dave Bruns

Related shortcuts