# Index Match data to provide multiple results in columns

#### lomond44

##### New Member
I need some help with a spreadsheet that contains 2 worksheets.

The first sheet contains part numbers and the assemblies that they are used in. Each part number can be used in many assemblies.

On the 2nd sheet is a list of just the part numbers without duplicates. The aim is to create columns to the right of each part number that identifies all the assemblies that the part is used in.

I have looked at this link How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group but I do not want the results to be down a column but to go across the row.

Any help appreciated

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

##### MrExcel MVP
This is how non-robust formula set ups propagate.

Care to post a small (5 row) sample from the source sheet?

#### lomond44

##### New Member
Using the example I linked to in my post, the data and results should look like this:

 Part No Assembly Lookup Match 1 Match 2 Duck Daffy Duck Daffy Mouse Mickey Mouse Mickey Minnie Mouse Minnie Bunny Bugs Bunny Bugs Pig Porky Pig Porky Dog Goofy Pluto Dog Goofy Dog Pluto

<tbody>
</tbody>

Last edited:

##### MrExcel MVP
E2, control+shift+enter, not just enter, copy acrossas far as needed, and down:
Rich (BB code):
``````=IFERROR(INDEX(\$B\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=\$D2,ROW(\$B\$2:\$B\$8)-ROW(\$D\$2)+1),
COLUMNS(\$E2:E2))),"")
``````

#### lomond44

##### New Member

Perfect! Thank you very much!

#### RebelRebel

##### New Member

How do you enter multiple criteria, like mouse and disney?

##### MrExcel MVP
How do you enter multiple criteria, like mouse and disney?

Try to provide a small sample and the expected result(s)...

#### RebelRebel

##### New Member
Try to provide a small sample and the expected result(s)...

 Origin Animal Name Lookup: Mouse, Disney Warner Duck Daffy Disney Mouse Mickey Matches: Mickey Disney Mouse Minnie Minnie Warner Bunny Bugs Warner Pig Porky Disney Dog Goofy Disney Dog Pluto 20th Cent Mouse Mighty

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

##### MrExcel MVP
Are Mouse, Disney in a single cell?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,302
Messages
5,836,516
Members
430,437
Latest member
Emilycr

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