Explanation
This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match.
The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH function:
Table1[#Headers]
The nice thing about this reference is that it will automatically adjust to any changes in the table. Even when columns are added or removed, the reference will continue to return the correct range.
Related formulas
Two-way lookup VLOOKUP in a Table
At a high level, we use VLOOKUP to extract employee information in 4 columns with ID as the lookup value: =VLOOKUP($I$4,Table1,MATCH(H5,Table1[#Headers],0),0) The ID value comes from cell I4, and is locked so that it won't change as the formula is copied down the column. The table array is Table1,...
Related functions
MATCH Function
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, MATCH is combined with the...
Related videos
How to use VLOOKUP with a table
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...
