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.
If someone selects Arizona, I would like it to return a list of all companies that support Arizona (yes).
I am sure there is a way with vlookup or index, but I am not having any luck. Any help is appreciated - Thank you!
| |||||||
Company | AL | AK | AZ | AR | CA | MD | MA |
Company A | Yes | With Subs | Yes | Yes | Yes | Yes | Yes |
Company B | With Subs | With Subs | With Subs | With Subs | With Subs | With Subs | With Subs |
Company C | Yes | No | Yes | No | Yes | Yes | Yes |
Company D | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
Company E | No | No | No | No | Yes | No | No |
4 Replies
Here's a refinement. Assuming you might want to add options under each state, this (after you've added the acceptable answers to the list in Data Validation) will give you a list that meets an individually chosen criterion for the state. The FILTER function can be extended to allow for other kinds of combinations. Here's a video explaining it.
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"
Saljets If you have Excel 365 this will do the trick.
=FILTER(A1:A6,CHOOSECOLS(B1:H6,MATCH(M1,B1:H1,0))="yes")
