Match Formula to pull all matched items

[PCRIDE]

Hi, I have a look up table that looks something like this. 2 columns, lets say in this example a color and company.


RED COMPANY 1
BLUE COMPANY 2
YELLOW COMPANY 3
YELLOW COMPANY4
RED COMPANY 7
BLUE COMPANY 10
BLUE COMPANY11
BLUE COMPANY14
RED COMPANY 20
RED COMPANY 19
PURPLE COMPANY 55
PURPLE COMPANY53

I have a drop down in a dashboard with the colors

RED
BLUE
YELLOW
PURPLE


When a user chooses a color, ALL of the correlating companys need to populate in another tab, and when the color is changed, the same cell would change to a different company. I have about 10-15 companys per color.


If the user chooses RED, then all the REd companies are displayed.

COMPANY 1
COMPANY 7
COMPANY 20
COMPANY 19



I can't do a vlookup, because it will just show the first one in the lookup.

I think I need like a match Product formula?

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down rows as needed.

Excel Workbook
	A
1	Company
2	COMPANY 2
3	COMPANY 10
4	COMPANY11
5	COMPANY14
6
7
8
9
Sheet2

Excel Workbook
	A	B	C	D
1	Color	Comp.		Choose
2	RED	COMPANY 1		BLUE
3	BLUE	COMPANY 2
4	YELLOW	COMPANY 3
5	YELLOW	COMPANY4
6	RED	COMPANY 7
7	BLUE	COMPANY 10
8	BLUE	COMPANY11
9	BLUE	COMPANY14
10	RED	COMPANY 20
11	RED	COMPANY 19
12	PURPLE	COMPANY 55
13	PURPLE	COMPANY53
Sheet1

 

[PCRIDE]

BOO YA!! Thank you sooo much. This is a life saver formula!! It makes my dashboard work so quickly! I did have a pivot table, then had a complex macro that changed the pivot table (3 pivot table) filters to display the Company name!!

Thanks again @AhoyNC!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.