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.
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.
Preface Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create...
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX...