Get column index from table header in excel

0 votes

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.

Feb 23, 2023 in Others by narikkadan
• 63,640 points
749 views

1 answer to this question.

0 votes

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)
answered Feb 23, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

Get column names of Excel worksheet with OpenPyXL in readonly mode

This will print every thing from row ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,640 points
7,195 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,640 points
1,382 views
0 votes
1 answer

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,640 points
1,734 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,640 points
1,582 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,883 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,965 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
958 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,612 views
0 votes
1 answer
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
829 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP