use INDEX formula in Excel for gathering multiple search items

[Half Measures]

On the "List" worksheet, I have an inventory of Food, Music, Activity and Color that a Customer uses listed into columns. On a "Search" worksheet I have created PickLists cataloguing the Food, Music, Activity and Color available for Customers.

My goal with this is to create an INDEX formula for finding which Customers are utilizing a chosen item from the PickLists? These would be done by aligning the Row of a Customer and Item listed that they are using, from the "List" worksheet. A VLOOKUP formula only shows one result, and only shows the first one in the search. I’m looking for a way to result all the Customers using the chosen item.

I wanted to attach the Excel doc here that I'm talking about, for a better example. But, I couldn't find a way to do that. I'm new here. So, here are some screen-shot's visualizing what I'm talking about.

 

[Fluff]

Did you drag the formula down?

 

[Half Measures]

Did you drag the formula down?

Not sure what you mean by "dragged down". Can you explain?

 

[Fluff]

Put the formula in B7 then select B7:B10 (or however far you want) & hit Ctrl D

 

[Half Measures]

Put the formula in B7 then select B7:B10 (or however far you want) & hit Ctrl D

Now it's not giving any results. Weird. Not sure what I'm doing wrong.

 

[Half Measures]

Now it's not giving any results. Weird. Not sure what I'm doing wrong.

Wait. I get it now. It's just giving me duplicates of one result, not grouping the multiple results needed.

 

[Fluff]

In that case please use the XL2BB add-in to post some sample data of both sheets, along with expected results.

 

[Half Measures]

In that case please use the XL2BB add-in to post some sample data of both sheets, along with expected results.

I followed directions listed there. I do not have that add-in. Not sure how to get that and install it. I clicked on the links provided, and read articles. Couldn't find explanation on how to install the add-in if I do not have it.

 

[Fluff]

You can download it here

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Half Measures]

You can download it here

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Copy of Customer Test Forms - Copy (1).xlsx
	B
7	Alice's kitchen
Search

Cell Formulas
Range	Formula
B7	B7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$A$2:$A$8=Search!$B$3),ROWS(Search!$B$7:$B$7))),"")

 

[Half Measures]

Copy of Customer Test Forms - Copy (1).xlsx
	B
7	Alice's kitchen
Search

Cell Formulas
Range	Formula
B7	B7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$A$2:$A$8=Search!$B$3),ROWS(Search!$B$7:$B$7))),"")

Here is my first worksheet called "List"

Copy of Customer Test Forms - Copy (1).xlsx
	A	B	C	D	E
1	Food	Music	Activity	Customer	Color
2	pizza	country	walking	Alice's kitchen	red
3	hamburger	metal	talking	Alice's kitchen	blue
4	spaghetti	classical		Alice's kitchen	green
5		rap		Alice's kitchen	yellow
6				Alice's kitchen	orange
7	spaghetti	pop	talking	Donovan's backyard	red
8	sandwiches	country	sleeping	Donovan's backyard	yellow
List