# use INDEX formula in Excel for gathering multiple search items

#### Half Measures

##### New Member
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.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=FILTER(List!D1:D8,List!A1:A8=B3)``

#### Half Measures

##### New Member
Hi & welcome to MrExcel.
Excel Formula:
``=FILTER(List!D1:D8,List!A1:A8=B3)``
Eh...It's telling me that formula isn't valid.

#### Half Measures

##### New Member
Eh...It's telling me that formula isn't valid.
I tried FILETERXML instead, and it is only giving me a #VALUE! result.

#### Fluff

##### MrExcel MVP, Moderator

Eh...It's telling me that formula isn't valid.
Does it say the formula or function is not valid?

#### Fluff

##### MrExcel MVP, Moderator

So you get a message like

#### Fluff

##### MrExcel MVP, Moderator
So it doesn't say formula then
How about in B7 dragged down
Excel Formula:
``=IFERROR(INDEX(List!\$D\$1:\$D\$8,AGGREGATE(15,6,(ROW(List!\$D\$1:\$D\$8)-ROW(List!\$D\$1)+1)/(List!\$A\$1:\$A\$8=B3),ROWS(B\$7:B7))),"")``

#### Half Measures

##### New Member
So it doesn't say formula then
How about in B7 dragged down
Excel Formula:
``=IFERROR(INDEX(List!\$D\$1:\$D\$8,AGGREGATE(15,6,(ROW(List!\$D\$1:\$D\$8)-ROW(List!\$D\$1)+1)/(List!\$A\$1:\$A\$8=B3),ROWS(B\$7:B7))),"")``
Eh...it's still only showing one result, even if more than one Customer uses the Food item.

Replies
17
Views
271
Replies
9
Views
524
Replies
5
Views
29
Replies
2
Views
311
Replies
9
Views
177

1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

### 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.

### Which adblocker are you using?

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

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