New Contributor

# 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

# Re: Excel functionality for returning values

@Saljets If you have Excel 365 this will do the trick.

``=FILTER(A1:A6,CHOOSECOLS(B1:H6,MATCH(M1,B1:H1,0))="yes")``

# Re: Excel functionality for returning values

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"

# Re: Excel functionality for returning values

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.

# Re: Excel functionality for returning values

Thank you! this worked perfectly