Forum Discussion
Excel functionality for returning values
Hi all, I am trying to create a dashboard where if a state is selected from a drop-down and the company supports that state, it will return the company name. Basically, my data table is below. I...
There may be a more elegant way to do this, but I played around with a combination of functions and came up with a solution. The formula is this, and it's working in the attached spreadsheet.
=FILTER(A2:A6,INDEX(B2:H6,,MATCH(M1,B1:H1,0))="Yes")
Let me see if I can explain how it works, from the inside-out.
- MATCH(M1,B1:H1,0) finds the column for the selected state and delivers a column number, 1-7
- INDEX(B2:H6,,[that column #]) delivers a column with the words ("Yes" "No" etc) in it
- and the enclosing FILTER finds the Company names from column A where the value in the foregoing result is "Yes"