CODEWITHSUNDEEP
excelexcel-formulaexcel-tables
Ryan T
Ryan T

Reputation: 1

How to return specific column values based on cell value

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!!

Current Table Example

Upvotes: 0

Views: 75

Answers (1)

Vlado Bošnjaković
Vlado Bošnjaković

Reputation: 21

Similar to @Spectral's answer:

=FILTER(Table1[[Employee Name]:[Employee ID]]; INDEX(Table1; 0; MATCH(A1; Table1[#Headers]; 0)) = "Y")
  1. 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.
  1. 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.
  1. FILTER(..., INDEX(...) = "Y"):
  • Filters the Employee Name and Employee ID columns (Table1[[Employee Name]:[Employee ID]]) where the selected skill column equals "Y".
  1. Dynamic Input in A1:
  • By changing the skill name in A1, the formula automatically adjusts to filter based on the corresponding column.

Upvotes: 1

Related Questions