I am trying to create a formula that will return the employee names and ID’s based on the Skill Name in cell A1 if the employee has a “Y” under that Skill Name. I have tried variations of xlookup, index, match, filter, and IF. I have spent hours googling trying to find something similar with no avail.
Let me know if I need to provide more detail/info
Any help is greatly appreciated!!
3
Similar to @Spectral’s answer:
=FILTER(Table1[[Employee Name]:[Employee ID]]; INDEX(Table1; 0; MATCH(A1; Table1[#Headers]; 0)) = "Y")
- MATCH(A1, Table1[#Headers], 0):
- Looks up the column header entered in A1 within the table headers (Table1[#Headers]).
- Returns the column number corresponding to the entered skill name.
- INDEX(Table1, 0, MATCH(…)):
- Retrieves the entire column (specified by the column number) from the table.
- 0 as the row argument means all rows are included.
- FILTER(…, INDEX(…) = “Y”):
- Filters the Employee Name and Employee ID columns (Table1[[Employee Name]:[Employee ID]]) where the selected skill column equals “Y”.
- Dynamic Input in A1:
- By changing the skill name in A1, the formula automatically adjusts to filter based on the corresponding column.
New contributor
Vlado Bošnjaković is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1