Match Formula to pull all matched items

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
907
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down rows as needed.

Excel Workbook
A
1Company
2COMPANY 2
3COMPANY 10
4COMPANY11
5COMPANY14
6
7
8
9
Sheet2
Excel Workbook
ABCD
1ColorComp.Choose
2REDCOMPANY 1BLUE
3BLUECOMPANY 2
4YELLOWCOMPANY 3
5YELLOWCOMPANY4
6REDCOMPANY 7
7BLUECOMPANY 10
8BLUECOMPANY11
9BLUECOMPANY14
10REDCOMPANY 20
11REDCOMPANY 19
12PURPLECOMPANY 55
13PURPLECOMPANY53
Sheet1
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top