You can use the MATCH function to get the column number and the INDEX function to get the column name.
Get column index using the MATCH function
In the following example, we have two tables Users and Results.
What we want to do here, is to retrieve the column index from the Users table and insert it into the Results table, based on the Column name.
There are two ways you can do it.
In the first method, you make a standard reference. The function will look for value E2 inside $A$1:$C$1. 0 as the third parameter means that it has to be an exact match.
1 |
=MATCH(E2,$A$1:$C$1,0) |
The second method is better. If you have your table on another sheet, you can refer to it by using the table name and reference to headers.
1 |
=MATCH(E2,Users[#Headers],0) |
If you convert the table Users into range, Example 2 will change from these formulas (Ctrl + ~).
Into this.
Now, Example 2 is the same as Example 1. The only difference is that in Example 2 there is also a reference to the current sheet.
Get column index using the INDEX function
The INDEX function is similar to the MATCH function.
The difference is that in the MATCH function, you give a name and the function returns an index.
In the INDEX function, you give an index and Excel returns a name.
Let’s modify our example by replacing the functions.
Press Ctrl + ~ to display formulas instead of values.
The INDEX function takes a range of cells (A1:C1 in our example) and returns the value of the specified index:
1: First Name
2: Last Name
3: Age
The last example returns the reference error (#REF!) because there is no fourth column in our example.