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

 

[teeltea]

Where is the link to the formula - I don't see it.
thanks

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>

 

[Aladin Akyurek]

Where is the link to the formula - I don't see it.
thanks

Post #4 shows the formula you can use...

 

[Aladin Akyurek]

@ 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))),"")​

 

[Nugget_Leodis]

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;

Sheet 1: A	Sheet 1: B
13244	BS4W
8952	BCPLSAFCT5L
8952	WMB3FW
8952	EB3FX
8952	MFC10STHF

<tbody>
</tbody>

Sheet 2: A	Sheet 2: B	Sheet 2: Match 1	Sheet 2: Match 2	Sheet 2: Match 3	Sheet 2: Match 4
13244	bbm1	bs4w
8952	hw10	bcplsafct5l	wmb3fx	eb3fx	mfc10sthf
8952	hwa20	bcplsafct5l	wmb3fx	eb3fx	mfc10sthf
8952	rlx4	bcplsafct5l	wmb3fx	eb3fx	mfc10sthf

<tbody>
</tbody>

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)

 

[Aladin Akyurek]

@ Nugget_Leodis

Sheet1 (data)

Row\Col	A​	B​
1​
2​	13244​	BS4W
3​	8952​	BCPLSAFCT5L
4​	8952​	WMB3FW
5​	8952​	EB3FX
6​	8952​	MFC10STHF
7​

Sheet2 (processing)

Row\Col	A​	B​	C​	D​	E​
1​
2​	13244​	BS4W
3​	8952​	BCPLSAFCT5L	WMB3FW	EB3FX	MFC10STHF
4​

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))),"")

 

[proficient]

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))),"")

 

[Aladin Akyurek]

@ 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:

item	match item set against itself (data_array)	bins_array	count of B	results of count B
JAD	1	1	from 0 to >= 1	2 = 2 times 1
VAD	2	2	from 2 to >= 2	1 = 1 times 2
JAD	1	3	from 3 to >= 3	0 = 0 times 3
KAD	4	4	from 4 to >= 4	1 = 1 times 4
			rest	0 = 0 times anything beyond 4
				How many results are above 0?
				3
				This count is precisely the number of unique items JAD, VAD, and KAD.

<tbody>
</tbody>

_____________________________​

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.

 

[proficient]

@ 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:

item	match item set against itself (data_array)	bins_array	count of B	results of count B
JAD	1	1	from 0 to >= 1	2 = 2 times 1
VAD	2	2	from 2 to >= 2	1 = 1 times 2
JAD	1	3	from 3 to >= 3	0 = 0 times 3
KAD	4	4	from 4 to >= 4	1 = 1 times 4
			rest	0 = 0 times anything beyond 4
				How many results are above 0?
				3
				This count is precisely the number of unique items JAD, VAD, and KAD.

<tbody>
</tbody>

_____________________________​

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.

The way you explain the formula step by step is awesome, thank you so much.

 

[Aladin Akyurek]

The way you explain the formula step by step is awesome, thank you so much.

Glad to hear that you like it.

 

[Delaik]

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)

Animal	Name		Lookup:	Mouse
Duck	Daffy		Matches:	Mickey
Mouse	Mickey			Minnie
Mouse	Minnie
Bunny	Bugs
Pig	Porky
Dog	Goofy
Dog	Pluto

<tbody>
</tbody>

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

Animal 1	Animal 2	Items		Lookup	Mouse
Duck	Mockingjay	3		Matches	12
Mouse	Cat	12			14
Cat	Dog	4			9
Mouse	Elephant	14
Lion	Mouse	9

<tbody>
</tbody>

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!