Index Match data to provide multiple results in columns

L

lomond44

New Member
Joined
May 6, 2014
Messages
7
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 :)
 
Sort by date Sort by votes
Where is the link to the formula - I don't see it.
thanks

lomond44 said:
Using the example I linked to in my post, the data and results should look like this:

[TABLE="class: grid, width: 389"]
<tbody>[TR]
[TD]Part No
[/TD]
[TD="colspan: 2"]Assembly
[/TD]
[TD]Lookup
[/TD]
[TD]Match 1
[/TD]
[TD]Match 2
[/TD]
[/TR]
[TR]
[TD]Duck
[/TD]
[TD]Daffy
[/TD]
[TD][/TD]
[TD]Duck
[/TD]
[TD]Daffy
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mouse
[/TD]
[TD]Mickey
[/TD]
[TD][/TD]
[TD]Mouse
[/TD]
[TD]Mickey
[/TD]
[TD]Minnie
[/TD]
[/TR]
[TR]
[TD]Mouse
[/TD]
[TD]Minnie
[/TD]
[TD][/TD]
[TD]Bunny
[/TD]
[TD]Bugs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bunny
[/TD]
[TD]Bugs
[/TD]
[TD][/TD]
[TD]Pig
[/TD]
[TD]Porky
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pig
[/TD]
[TD]Porky
[/TD]
[TD][/TD]
[TD]Dog
[/TD]
[TD]Goofy
[/TD]
[TD]Pluto
[/TD]
[/TR]
[TR]
[TD]Dog
[/TD]
[TD]Goofy
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog
[/TD]
[TD]Pluto
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
@ poer24

=IF(ISERROR(INDEX($A$2:$T$2500,SMALL(IF($C$2:$C$2500=$AA$1,ROW($C$2:$C$2500)),ROW(4:4)),5)),"",INDEX($A$2:$T$2500,SMALL( IF($C$2:$C$2500=$AA$1,ROW($C$2:$C$2500)),ROW(4:4)),5))

is not a formula that I would recommend or post... (There are enough people who start a site on Excel and propagate this kind of nonsense, alas.)

Try rather... In AB1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($E$2:$E$2500,SMALL(IF($C$2:$C$2500=$AA$1,ROW($C$2:$C$2500)-ROW($C$2)+1),ROWS($AB$1:$AB1))),"")​
 
Upvote 0
Hi,

I have a similar issue with these posts and I can't seem to figure it out. I have two tables on two separate sheets. Column A on both sheets is the one which matches the other. I want to find all the matches for Sheet 2:A from Sheet 1:A and put Sheet 1:B in Sheet 2:C-X.

For example;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sheet 1: A[/TD]
[TD]Sheet 1: B[/TD]
[/TR]
[TR]
[TD]13244[/TD]
[TD]BS4W[/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]BCPLSAFCT5L[/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]WMB3FW[/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]EB3FX[/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]MFC10STHF[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Sheet 2: A[/TD]
[TD]Sheet 2: B[/TD]
[TD]Sheet 2: Match 1[/TD]
[TD]Sheet 2: Match 2[/TD]
[TD]Sheet 2: Match 3[/TD]
[TD]Sheet 2: Match 4[/TD]
[/TR]
[TR]
[TD]13244[/TD]
[TD]bbm1[/TD]
[TD]bs4w[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]hw10[/TD]
[TD]bcplsafct5l[/TD]
[TD]wmb3fx[/TD]
[TD]eb3fx[/TD]
[TD]mfc10sthf[/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]hwa20[/TD]
[TD]bcplsafct5l[/TD]
[TD]wmb3fx[/TD]
[TD]eb3fx[/TD]
[TD]mfc10sthf[/TD]
[/TR]
[TR]
[TD]8952[/TD]
[TD]rlx4[/TD]
[TD]bcplsafct5l[/TD]
[TD]wmb3fx[/TD]
[TD]eb3fx[/TD]
[TD]mfc10sthf[/TD]
[/TR]
</tbody>[/TABLE]

I attempted to use the formula you've used in reply #4 but I can't seem to get it to work (I did press Ctrl + Shift + Enter)
 
Upvote 0
@ Nugget_Leodis

Sheet1 (data)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][/tr][tr][td]
1​
[/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td]
13244​
[/td][td]BS4W[/td][/tr]
[tr][td]
3​
[/td][td]
8952​
[/td][td]BCPLSAFCT5L[/td][/tr]
[tr][td]
4​
[/td][td]
8952​
[/td][td]WMB3FW[/td][/tr]
[tr][td]
5​
[/td][td]
8952​
[/td][td]EB3FX[/td][/tr]
[tr][td]
6​
[/td][td]
8952​
[/td][td]MFC10STHF[/td][/tr]
[tr][td]
7​
[/td][td][/td][td][/td][/tr]
[/table]

Sheet2 (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr][tr][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td]
13244​
[/td][td]BS4W[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
3​
[/td][td]
8952​
[/td][td]BCPLSAFCT5L[/td][td]WMB3FW[/td][td]EB3FX[/td][td]MFC10STHF[/td][/tr]
[tr][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In B2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$B$2:$B$6,SMALL(IF(Sheet1!$A$2:$A$6=$A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COLUMNS($B$2:B2))),"")
 
Upvote 0
Hi Aladin,

Could you elaborate the below functions for me...? As I am not getting couple of ranges you referred to MATCH fuction, also before MATCH function threre is IF($B$2:$B$14<>"" statement which I am not getting as well. thanks in advance.

=IFERROR(INDEX($B$2:$B$14,SMALL(IF(FREQUENCY(IF($A$2:$A$14=$D2,
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1),
COLUMNS($E2:E2))),"")
 
Upvote 0
@ proficient

This refers to the formula which is forwarded in post #26 .

The bit:

FREQUENCY(IF($A$2:$A$14=$D2,
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1)

​
of the formula creates maps the occurrences of the non-blank cells in B2:B14 to the bin-array ROW(B2:B14)-ROW(B2)+1, which correspond to the occurrences of D2 in A2:A14.

A. $B$2:$B$14<>"" means: skip blanks/empty cells.
​

B. MATCH($B$2:$B$14,$B$2:$B$14,0) matches the range interest against itself, leading to numbers showing matches.

C. FREQUENCY works with numbers (data_array) whose occurrences it classifies into bins_array.

_____________________________
Intermezzo:

[TABLE="class: grid, width: 861"]
<tbody>[TR]
[TD]item[/TD]
[TD]match item set against itself (data_array)[/TD]
[TD]bins_array[/TD]
[TD]count of B[/TD]
[TD]results of count B[/TD]
[/TR]
[TR]
[TD]JAD[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]from 0 to >= 1[/TD]
[TD]2 = 2 times 1[/TD]
[/TR]
[TR]
[TD]VAD[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]from 2 to >= 2[/TD]
[TD]1 = 1 times 2[/TD]
[/TR]
[TR]
[TD]JAD[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]from 3 to >= 3[/TD]
[TD]0 = 0 times 3[/TD]
[/TR]
[TR]
[TD]KAD[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD]from 4 to >= 4[/TD]
[TD]1 = 1 times 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rest[/TD]
[TD]0 = 0 times anything beyond 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]How many results are above 0?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]This count is precisely the number of unique items JAD, VAD, and KAD.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

_____________________________​

D. The IF bit, i.e.

IF(FREQUENCY(IF($A$2:$A$14=$D2,
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1)

>>

IF(non-zero results of frequency, ROW($B$2:$B$14)-ROW($B$2)+1)
​


​
​
of the formula returns the rows of non-zero results.

E. Feeding the rows from [D] to INDEX looking at $B$2:$B$14 one by one by means of SMALL, we get the list of unique items from $B$2:$B$14.

See for more:
https://www.mrexcel.com/forum/excel...ing-sum-if-frequency-match-2.html#post3156949

​
Hope this helps.
 
Last edited:
Upvote 0
Aladin Akyurek said:
@ proficient

This refers to the formula which is forwarded in post #26 .

The bit:

FREQUENCY(IF($A$2:$A$14=$D2,
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1)

​
of the formula creates maps the occurrences of the non-blank cells in B2:B14 to the bin-array ROW(B2:B14)-ROW(B2)+1, which correspond to the occurrences of D2 in A2:A14.

A. $B$2:$B$14<>"" means: skip blanks/empty cells.
​

B. MATCH($B$2:$B$14,$B$2:$B$14,0) matches the range interest against itself, leading to numbers showing matches.

C. FREQUENCY works with numbers (data_array) whose occurrences it classifies into bins_array.

_____________________________
Intermezzo:

[TABLE="class: grid, width: 861"]
<tbody>[TR]
[TD]item[/TD]
[TD]match item set against itself (data_array)[/TD]
[TD]bins_array[/TD]
[TD]count of B[/TD]
[TD]results of count B[/TD]
[/TR]
[TR]
[TD]JAD[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]from 0 to >= 1[/TD]
[TD]2 = 2 times 1[/TD]
[/TR]
[TR]
[TD]VAD[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]from 2 to >= 2[/TD]
[TD]1 = 1 times 2[/TD]
[/TR]
[TR]
[TD]JAD[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD]from 3 to >= 3[/TD]
[TD]0 = 0 times 3[/TD]
[/TR]
[TR]
[TD]KAD[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD]from 4 to >= 4[/TD]
[TD]1 = 1 times 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rest[/TD]
[TD]0 = 0 times anything beyond 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]How many results are above 0?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]This count is precisely the number of unique items JAD, VAD, and KAD.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

_____________________________​

D. The IF bit, i.e.

IF(FREQUENCY(IF($A$2:$A$14=$D2,
IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1)

>>

IF(non-zero results of frequency, ROW($B$2:$B$14)-ROW($B$2)+1)
​


​
​
of the formula returns the rows of non-zero results.

E. Feeding the rows from [D] to INDEX looking at $B$2:$B$14 one by one by means of SMALL, we get the list of unique items from $B$2:$B$14.

See for more:
https://www.mrexcel.com/forum/excel...ing-sum-if-frequency-match-2.html#post3156949

​
Hope this helps.
Click to expand...

The way you explain the formula step by step is awesome, thank you so much.
 
Upvote 0
Hey guys, I was using this formula to handle imported date for my job. Everything about this formula works amazingly.

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

However, I'm running to a little issue. I need for this formula to look up values/data from the first two columns as opposed of just A1:A8. Can anyone help me out with this?


So we have this: (Thanks for this example btw)
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Animal[/TD]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="width: 64"][/TD]
[TD="class: xl64, width: 64"]Lookup:[/TD]
[TD="class: xl63, width: 64"]Mouse[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]Daffy[/TD]
[TD][/TD]
[TD="class: xl64"]Matches:[/TD]
[TD="class: xl66"]Mickey[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Mickey[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"]Minnie[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Minnie[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]Bunny[/TD]
[TD]Bugs[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]Pig[/TD]
[TD]Porky[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Goofy[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Pluto[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]


I trying to get the "Lookup: Mouse" To lookup not just values from Column A but also Column B. Like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Animal 1[/TD]
[TD]Animal 2[/TD]
[TD]Items[/TD]
[TD][/TD]
[TD]Lookup[/TD]
[TD]Mouse[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]Mockingjay[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Matches[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Cat[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Elephant[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lion[/TD]
[TD]Mouse[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In the last row, "Mouse" appears on Column B. Is there a way to make the formula read column B as well as column A? Also, I do not intent to have repeated values in a row. In case that makes things easier?

Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Dynamically Build Datasheet
Replies
3
Views
114
mongoose00318
M
C
  • Question Question
How Can I Reference multiple sheets
Replies
3
Views
324
jolivanes
jolivanes
S
  • Solved
Looking to find most repeated text in a column on excel depending on criteria - 2 problems
2
Replies
17
Views
265
Cubist
Cubist
D
  • Solved
VBA for Text To Columns to correct how imported data is stored
Replies
4
Views
270
Alex Blakenburg
A
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
815
henrik2h
H

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top