Excel functionality for returning values

New Contributor

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

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

 

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

 

DexterG_III_0-1664926489775.png

 

@Saljets 

 

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 

 

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.

@mathetes 

 

Thank you! this worked perfectly