Finding and listing out the multiple Matches of an item in a reference list.

[mahhdy]

HI,
I have a master list, includes product, work order and item info. I have another list which sorts out the items and their available stock. I want to know each item is called in which work #, and which products #. I know that with catenating the Work# and Product #, I can deal with P#/W# combination. But I don't know how to populate the list of them in front of each item.
Vlookup Just can bring one result, Actually the first result only.
Any Idea?

I am also open to accepting VBA solutions too.
Here is a microscopic image of the long list.

 

[Aladin Akyurek]

An image is not Excel readable, so not very useful.

Let A3:D9 house the source list, headers included.

Let G3:M9 house the desired table, headers included.

In H4 control+shift+enter, not just enter, copy across (to J4), and down:

=IFERROR(INDEX($B$4:$B$9,SMALL(IF($C$4:$C$9=$G4,ROW($C$4:$C$9)-ROW($C$4)+1),COLUMNS($H4:H4))),"")

In K4 control+shift+enter, not just enter, copy across (to M4), and down:

=IFERROR(INDEX($A$4:$A$9,SMALL(IF($C$4:$C$9=$G4,ROW($C$4:$C$9)-ROW($C$4)+1),COLUMNS($K4:K4))),"")

 

[mahhdy]

Hi,
Thanks, Actually that was just as a sample. I somehow got your idea, but not able to successfully implement that.
It is what I understood from your function:
That's correct that I need just a simple index function for the first occurrence. It is same as V lookup result.
By using Small function, at first it checks the match of cell values ("I think there may be something wrong here"), if true, it returns the relative position of the matched row in the table. then the small have to provide the (first, second, third,..) result according to the respective columns for them.
But generally, i didn't get it.
Here is my sample file, If you can help me more.

My excel Sample file:
https://1drv.ms/x/s!ArGi1KRQ5iItgYtPI9u7ErshfqY-uQ

Yours,
M

An image is not Excel readable, so not very useful.

Let A3:D9 house the source list, headers included.

Let G3:M9 house the desired table, headers included.

In H4 control+shift+enter, not just enter, copy across (to J4), and down:

=IFERROR(INDEX($B$4:$B$9,SMALL(IF($C$4:$C$9=$G4,ROW($C$4:$C$9)-ROW($C$4)+1),COLUMNS($H4:H4))),"")

In K4 control+shift+enter, not just enter, copy across (to M4), and down:

=IFERROR(INDEX($A$4:$A$9,SMALL(IF($C$4:$C$9=$G4,ROW($C$4:$C$9)-ROW($C$4)+1),COLUMNS($K4:K4))),"")

 

[Aladin Akyurek]

You should have done this yourself...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	A/C	Work Book	Material	QTY		MATERIAL	W/B-1	W/B-2	W/B-3	W/B-4
2​	70003​	GX72533	20334-3	1​		AS1633A20H0165	GX72617	GX72617	GX72624
3​	70003​	GX72533	70692A010001	1​		G02194102-001	GX72533
4​	70003​	GX72533	B0202032C5K	1​		G02194111-001	GX72533
5​	70003​	GX72533	B0202033C0432K	4​		G02194401-001	GX72607
6​	70003​	GX72533	B0202033C0532K	3​		G02194402-001	GX72607	GX72617	GX72621
7​	70003​	GX72533	B0309017-04-025	4​		G02194403-001	GX72607	GX72617	GX72621
8​	70003​	GX72533	B0309017-04-032	1​		G02194404-001	GX72533
9​	70003​	GX72533	B0309017-04-033	3​		G02194405-001	GX72607	GX72617	GX72620
10​	70003​	GX72533	G02191003-101	2​		G03890011-001	GX72607	GX72617	GX72620
11​	70003​	GX72533	G02191004-101	3​		G03890012-001	GX72607	GX72617	GX72620
12​	70003​	GX72533	G02194102-001	1​		G03890013-001	GX72607	GX72617	GX72620
13​	70003​	GX72533	G02194105-001	1​		NAS1922-0625-3	GX72533
14​	70003​	GX72533	G02194107-001	1​		NAS6404U14	GX72533

The above exhibit shows your tables partially, not fully...

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

=IFERROR(INDEX(Table2[Work Book],SMALL(IF(Table2[Material]=[@MATERIAL],ROW(Table2[Material])-ROW(INDEX(Table2[@Material],1,1))+1),COLUMNS($G$2:G2))),"")

 

[mahhdy]

Hi,
Thanks Got it.
I was trying using, F2 and ten Ctr, shift Enter....
LOL
Great Help

 

[Aladin Akyurek]

Hi,
Thanks Got it.
I was trying using, F2 and ten Ctr, shift Enter....
LOL
Great Help

You are welcome.