Index Match data to provide multiple results in columns

[lomond44]

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

 

[RebelRebel]

Are Mouse, Disney in a single cell?

No. Those are the 2 criteria that I want to search records on. The result should be Mickey and Minnie.

 

[Aladin Akyurek]

No. Those are the 2 criteria that I want to search records on. The result should be Mickey and Minnie.

[TABLE="width: 493"]
<COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6172" width=174><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4152" width=117><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1024" width=29><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5660" width=159><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4096" width=115><TBODY>[TR]
[TD="class: xl67, width: 174, bgcolor: white"]Origin[/TD]
[TD="class: xl67, width: 117, bgcolor: white"]Animal[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]Name[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl67, width: 159, bgcolor: white"]Lookup: Origin[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Disney[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Warner[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Duck[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Daffy[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl67, width: 159, bgcolor: white"]Lookup: Animal[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Mouse[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 174, bgcolor: transparent"]Disney[/TD]
[TD="class: xl66, width: 117, bgcolor: transparent"]Mouse[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Mickey[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl67, width: 159, bgcolor: white"]Matches:[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Mickey[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 174, bgcolor: transparent"]Disney[/TD]
[TD="class: xl66, width: 117, bgcolor: transparent"]Mouse[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Minnie[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Minnie[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Warner[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Bunny[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Bugs[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Warner[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Pig[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Porky[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Disney[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Dog[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Goofy[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 115, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Disney[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Dog[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Pluto[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl63, width: 115, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]20th Cent[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Mouse[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Mighty[/TD]
[TD="class: xl63, width: 29, bgcolor: white"] [/TD]
[TD="class: xl63, width: 159, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[/TR]
</TBODY>[/TABLE]

F3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=$F$1,
  IF($B$2:$B$9=$F$2,ROW($C$2:$C$9)-ROW($C$2)+1)),
  ROWS($F$3:F3))),"")

Or just enter and copy down:

=IFERROR(INDEX($C$2:$C$9,AGGREGATE(14,6,
  (ROW($C$2:$C$9)-ROW($C$2)+1)/(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2)),
  ROWS($F$3:F3))),"")

 

[RebelRebel]

[TABLE="width: 493"]
<tbody>[TR]
[TD="class: xl67, width: 174, bgcolor: white"]Origin[/TD]
[TD="class: xl67, width: 117, bgcolor: white"]Animal[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]Name[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 159, bgcolor: white"]Lookup: Origin[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Disney[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Warner[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Duck[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Daffy[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 159, bgcolor: white"]Lookup: Animal[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Mouse[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 174, bgcolor: transparent"]Disney[/TD]
[TD="class: xl66, width: 117, bgcolor: transparent"]Mouse[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Mickey[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 159, bgcolor: white"]Matches:[/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Mickey[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 174, bgcolor: transparent"]Disney[/TD]
[TD="class: xl66, width: 117, bgcolor: transparent"]Mouse[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Minnie[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl63, width: 159, bgcolor: white"][/TD]
[TD="class: xl63, width: 115, bgcolor: white"]Minnie[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Warner[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Bunny[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Bugs[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl63, width: 159, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Warner[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Pig[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Porky[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl63, width: 159, bgcolor: white"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Disney[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Dog[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Goofy[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl63, width: 159, bgcolor: white"][/TD]
[TD="class: xl63, width: 115, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]Disney[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Dog[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Pluto[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl63, width: 159, bgcolor: white"][/TD]
[TD="class: xl63, width: 115, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 174, bgcolor: white"]20th Cent[/TD]
[TD="class: xl63, width: 117, bgcolor: white"]Mouse[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Mighty[/TD]
[TD="class: xl63, width: 29, bgcolor: white"][/TD]
[TD="class: xl63, width: 159, bgcolor: white"][/TD]
[TD="class: xl64, bgcolor: white"][/TD]
[/TR]
</tbody>[/TABLE]


F3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=$F$1,
  IF($B$2:$B$9=$F$2,ROW($C$2:$C$9)-ROW($C$2)+1)),
  ROWS($F$3:F3))),"")

Or just enter and copy down:

=IFERROR(INDEX($C$2:$C$9,AGGREGATE(14,6,
  (ROW($C$2:$C$9)-ROW($C$2)+1)/(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2)),
  ROWS($F$3:F3))),"")

This is perfect! Thanks! Can you explain this part of it though: ROW($C$2:$C$9)-ROW($C$2)+1)

 

[Aladin Akyurek]

This is perfect! Thanks!

You are welcome.

Can you explain this part of it though: ROW($C$2:$C$9)-ROW($C$2)+1)

This creates an integer vector, running from 1 to the size of C2:C8, i.e., {1;2;3;4;5;6;7,8}. In the first formula, the integers which survive the IF filters are fed to INDEX by the SMALL function. In the second formula, it's the division which occasions the filtering of the integer. AGGREGATE ignores error values that division causes. Surviving integers are used by INDEX, function number 14 (which corresponds to SMALL) feeds to it.

Hope this helps.

 

[wiz329]

You are welcome.



This creates an integer vector, running from 1 to the size of C2:C8, i.e., {1;2;3;4;5;6;7,8}. In the first formula, the integers which survive the IF filters are fed to INDEX by the SMALL function. In the second formula, it's the division which occasions the filtering of the integer. AGGREGATE ignores error values that division causes. Surviving integers are used by INDEX, function number 14 (which corresponds to SMALL) feeds to it.

Hope this helps.

Thanks very much for your solution, I was having this same problem, and this helped a lot!

 

[Aladin Akyurek]

Thanks very much for your solution, I was having this same problem, and this helped a lot!

Great to hear that. Thanks for providing feedback.

 

[Preeti Chikara]

HI
please explain why we are using ctrl+Shift+enter. and from where we can take the deep knowledge of these combination.
please suggest.

 

[Preeti Chikara]

thanks for your post, this is very useful for me.
but can you explain us use of cntrl+Shift+enter in array.

 

[Aladin Akyurek]

HI
please explain why we are using ctrl+Shift+enter. and from where we can take the deep knowledge of these combination.
please suggest.

thanks for your post, this is very useful for me.
but can you explain us use of cntrl+Shift+enter in array.

=IFERROR(INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=$F$1,
  IF($B$2:$B$9=$F$2,ROW($C$2:$C$9)-ROW($C$2)+1)),
  ROWS($F$3:F3))),"")

IF-conditionals which occur within the SMALL function (are set up to) process arrays when they test the A-range and B-range for equality to F1 and F2, respectively, and generate an integer array, i.e., ROW($C$2:$C$9)-ROW($C$2)+1. When a formula is set up to process and generate aarays, Excel, as required by design, must be informed about this by applying control+shift+enter.

 

[jdclover]

Hello,

Had a more elaborate request about the index matching. Here's a sample of my data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Test
[/TD]
[TD]Section
[/TD]
[TD]Components
[/TD]
[/TR]
[TR]
[TD]BMP
[/TD]
[TD]Section 1
[/TD]
[TD]Glucose
[/TD]
[/TR]
[TR]
[TD]BMP
[/TD]
[TD][/TD]
[TD]Creatinine
[/TD]
[/TR]
[TR]
[TD]BMP
[/TD]
[TD][/TD]
[TD]Sodium
[/TD]
[/TR]
[TR]
[TD]Electrolyte
[/TD]
[TD]Section 2
[/TD]
[TD]Sodium
[/TD]
[/TR]
[TR]
[TD]Electrolyte
[/TD]
[TD]Section 3
[/TD]
[TD]Potassium
[/TD]
[/TR]
[TR]
[TD]Electrolyte
[/TD]
[TD][/TD]
[TD]Chloride
[/TD]
[/TR]
</tbody>[/TABLE]

This is the lookup table, looking up the components in the above table to return tests and sections

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Component
[/TD]
[TD]Test
[/TD]
[TD]Section
[/TD]
[/TR]
[TR]
[TD]Glucose
[/TD]
[TD]BMP
[/TD]
[TD]Section 1
[/TD]
[/TR]
[TR]
[TD]Creatinine
[/TD]
[TD]BMP
[/TD]
[TD]Section 1
[/TD]
[/TR]
[TR]
[TD]Sodium
[/TD]
[TD]BMP, Electrolyte
[/TD]
[TD]Section 1, Section 2, Section 3
[/TD]
[/TR]
[TR]
[TD]Potassium
[/TD]
[TD]Electrolyte
[/TD]
[TD]Section 2, Section 3
[/TD]
[/TR]
[TR]
[TD]Chloride
[/TD]
[TD]Electrolyte
[/TD]
[TD]Section 2, Section 3
[/TD]
[/TR]
</tbody>[/TABLE]

So more or less I want a way to lookup a component and return all tests it might be in as well as all sections that are connected to the test. Components are in one sheet by themselves, with tests in another sheet indicating what components belong to the test and what sections the test belongs to.

Please let me know if you need clarification or more information.