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]

You need to change the formula back to what I suggested, otherwise it won't work.

 

[Half Measures]

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

And this is second worksheet called "Search"

Copy of Customer Test Forms - Copy (1).xlsx
	A	B	C	D	E	F	G	H
1
2		Food		Music		Activity		Color
3		pizza		metal		talking		yellow
4
5
6		Customer		Customer		Customer		Customer
7		Alice's kitchen		#N/A		#N/A		#N/A
8		Alice's kitchen
9		Alice's kitchen
10		Alice's kitchen
11		Alice's kitchen
12		Alice's kitchen
13		Alice's kitchen
14		Alice's kitchen
15		Alice's kitchen
16		Alice's kitchen
17		Alice's kitchen
Search

Cell Formulas
Range	Formula
D7	D7	=VLOOKUP(D3,List!A1:E8,4,FALSE)
F7	F7	=VLOOKUP(F3,List!A1:E8,4,FALSE)
H7	H7	=VLOOKUP(H3,List!A1:E8,4,FALSE)
B7:B17	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))),"")

Cells with Data Validation
Cell	Allow	Criteria
D3	List	country, metal, classical, rap, pop
F3	List	sleeping, talking, walking
H3	List	red, yellow, blue, green, orange
B3	List	=List!$A$2:$A$8

 

[Fluff]

See post#21

 

[Half Measures]

See post#21

Ha Ha! I think it's working now! Thank you so much!!

Copy of Customer Test Forms - Copy (1).xlsx
	A	B	C	D	E	F	G	H
1
2		Food		Music		Activity		Color
3		sandwiches		metal		talking		yellow
4
5
6		Customer		Customer		Customer		Customer
7		Donovan's backyard		#N/A		#N/A		#N/A
8		Alice's kitchen
9
10
11
12
13
14
15
16
17
Search

Cell Formulas
Range	Formula
D7	D7	=VLOOKUP(D3,List!A1:E8,4,FALSE)
F7	F7	=VLOOKUP(F3,List!A1:E8,4,FALSE)
H7	H7	=VLOOKUP(H3,List!A1:E8,4,FALSE)
B7:B17	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=B3),ROWS(B$7:B7))),"")

Cells with Data Validation
Cell	Allow	Criteria
D3	List	country, metal, classical, rap, pop
F3	List	sleeping, talking, walking
H3	List	red, yellow, blue, green, orange
B3	List	=List!$A$2:$A$8

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Half Measures]

You're welcome & thanks for the feedback.

I really appreciate you putting up with me, and walking me through everything. I thought I knew a lot about Excel. But, really? I'm just a newbie. lol

 

[Fluff]

My pleasure.

 

[Half Measures]

My pleasure.

Eh...still running into some problems. Thought I had copied/pasted and edited the formulas right. But, they aren't giving great results in the other cells.

One customer is showing up twice in one. And the full list of customers isn't showing up in the other.

Copy of Customer Test Forms - Copy (1).xlsx
	A	B	C	D	E	F	G	H
1
2		Food		Music		Activity		Color
3		sandwiches		pop		sleeping		yellow
4
5
6		Customer		Customer		Customer		Customer
7		Donovan's backyard		Donovan's backyard		Donovan's backyard		Alice's kitchen
8		Alice's kitchen				Alice's kitchen
9						Alice's kitchen
10
11
12
13
14
15
16
17
Search

Cell Formulas
Range	Formula
B7:B17	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=B3),ROWS(B$7:B7))),"")
D7:D17	D7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$B$2:$B$8=D3),ROWS(D$7:D7))),"")
F7:F17	F7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$C$2:$C$8=F3),ROWS(F$7:F7))),"")
H7:H17	H7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$E$2:$E$8=H3),ROWS(H$7:H7))),"")

Cells with Data Validation
Cell	Allow	Criteria
D3	List	country, metal, classical, rap, pop
F3	List	sleeping, talking, walking
H3	List	red, yellow, blue, green, orange
B3	List	=List!$A$2:$A$8

 

[Half Measures]

Eh...still running into some problems. Thought I had copied/pasted and edited the formulas right. But, they aren't giving great results in the other cells.

One customer is showing up twice in one. And the full list of customers isn't showing up in the other.

View attachment 28105

Copy of Customer Test Forms - Copy (1).xlsx
	A	B	C	D	E	F	G	H
1
2		Food		Music		Activity		Color
3		sandwiches		pop		sleeping		yellow
4
5
6		Customer		Customer		Customer		Customer
7		Donovan's backyard		Donovan's backyard		Donovan's backyard		Alice's kitchen
8		Alice's kitchen				Alice's kitchen
9						Alice's kitchen
10
11
12
13
14
15
16
17
Search

Cell Formulas
Range	Formula
B7:B17	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=B3),ROWS(B$7:B7))),"")
D7:D17	D7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$B$2:$B$8=D3),ROWS(D$7:D7))),"")
F7:F17	F7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$C$2:$C$8=F3),ROWS(F$7:F7))),"")
H7:H17	H7	=IFERROR(INDEX(List!$D$2:$D$8,AGGREGATE(15,6,(ROW(List!$D$2:$D$8)-ROW(List!$D$2)+1)/(List!$E$2:$E$8=H3),ROWS(H$7:H7))),"")

Cells with Data Validation
Cell	Allow	Criteria
D3	List	country, metal, classical, rap, pop
F3	List	sleeping, talking, walking
H3	List	red, yellow, blue, green, orange
B3	List	=List!$A$2:$A$8

This cell isn't giving all the customers either. Not sure what I did wrong.

 

[Fluff]

The formula does not exclude duplicate values, because you never said it should.
If you are missing values then it's probably because on the list sheet the values are not exactly the same as D2. Check for typos & leading/trailing spaces.