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

#### Fluff

##### MrExcel MVP, Moderator
Did you drag the formula down?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Half Measures

##### New Member
Did you drag the formula down?
Not sure what you mean by "dragged down". Can you explain?

#### Fluff

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

#### Half Measures

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

##### New Member

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

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

#### Half Measures

##### New Member

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.

#### Half Measures

##### New Member
Copy of Customer Test Forms - Copy (1).xlsx
B
7Alice's kitchen
Search
Cell Formulas
RangeFormula
B7B7=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

##### New Member
Copy of Customer Test Forms - Copy (1).xlsx
B
7Alice's kitchen
Search
Cell Formulas
RangeFormula
B7B7=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
ABCDE
1FoodMusicActivityCustomerColor
2pizzacountrywalkingAlice's kitchenred
3hamburgermetaltalkingAlice's kitchenblue
4spaghetticlassicalAlice's kitchengreen
5rapAlice's kitchenyellow
6Alice's kitchenorange
7spaghettipoptalkingDonovan's backyardred
8sandwichescountrysleepingDonovan's backyardyellow
List

Replies
17
Views
272
Replies
9
Views
533
Replies
5
Views
36
Replies
2
Views
313
Replies
9
Views
179

1,127,759
Messages
5,626,695
Members
416,200
Latest member
Pulsar3000

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