On this worksheet, I have a table that contains employee data, named Table1.
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.
Fortable_array, I want the lookup table itself, Table1.
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_typeis zero or false, since I want to force an exact match.
When I enter the formula, we get “Julie”, which is correct.
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.
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_typeis zero for exact match.
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 ofnestingfunctions inside a formula.
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.
