Get column index from table header in excel

Question

Can the column index be obtained from the table header? I have a table that I am filtering, and I want to use the column headings to create up a number of parameters using the filtered data.

In this instance, the formula I'm testing is:

=LET(setPrjFiltered, FILTER(StaffDetailsTbl,StaffDetailsTbl[Discipline]= "Programming"),
namesPrj, INDEX(setPrjFiltered,, StaffDetailsTbl[Employee]),
namesPrj)

This causes an error, however, it works properly if I use the index in this format: INDEX(setPrjFiltered,, 1). I'd like this formula to utilise the table column to reference the index of the column because it will have a lot of parameters set up in the same way and the same function will be used frequently in the sheet. This way, I won't have to update any indexes in the functions if the source data changes.

Answer 1

Try it. The StaffDetailsTbl table's headers are where the MATCH will search for the word "Employee" and return its position.

The column index, not the column itself, is the third input to INDEX. I believe this is the cause of your initial formula's erroneous result.

=LET(setPrjFiltered, FILTER(StaffDetailsTbl,StaffDetailsTbl[Discipline]= "Programming"),
namesPrj, INDEX(setPrjFiltered,, MATCH("Employee", StaffDetailsTbl[#Headers], 0)),
namesPrj)