VLOOKUP on one set of criteria and return multiple matches in a row?

[Kelvinator1]

I have two reports, one that has aged inventory data and the other that has sales data with inventory items on it. What I am trying to do is look up the item number from the aged inventory report and then go to the sales report, find that same item number and return all the customer names that have purchased that item and put the results in a row (across a bunch of columns). The aged inventory report is small (59 rows) compared to the sales report (2229 rows). I tried using index, small, row, column based on an example I found online, but it didn't work because my data are in two different files and are two different sizes. Any ideas?

 

[Peter_SSs]

At the moment we have to guess just how your data is laid out and what it is actually like. We also have to manually type any thing to test with. You will generally get much faster/better responses if you provide some sample data and expected results in a form that helpers can easily copy from.

For the future, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Assuming below is doing the sort of thing you are after, see if you can adapt it to your own circumstances.

24 08 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Sales				Aged
2	Item	Result			Item
3	Item 1	V			Item 1	V	I	G
4	Item 7	D			Item 2
5	Item 3	B			Item 3	B	D	W	Z	K
6	Item 1	I			Item 4	B
7	Item 7	O			Item 5	D	J	P
8	Item 3	D			Item 6	N	V
9	Item 3	W			Item 7	D	O	A
10	Item 3	Z			Item 8
11	Item 4	B			Item 9	J
12	Item 7	A
13	Item 1	G
14	Item 9	J
15	Item 5	D
16	Item 5	J
17	Item 6	N
18	Item 5	P
19	Item 6	V
20	Item 3	K
Kelvinator1

Cell Formulas
Range	Formula
F3:H3,F9:H9,F8:G8,F7:H7,F5:J5,F4,F6,F10:F11	F3	=TRANSPOSE(FILTER(B$3:B$20,A$3:A$20=E3,""))
Dynamic array formulas.

 

[kvsrinivasamurthy]

This may help.

In E3

=LET(a,A3:A9,au,UNIQUE(a),b,B3:B9,c,BYROW(au,LAMBDA(ro,TEXTJOIN("_",TRUE,FILTER(b,a=ro)))),d,TEXTSPLIT(TEXTJOIN(",",FALSE,c),"_",",",FALSE,0,""),HSTACK(au,IFERROR(VALUE(d),"")))

 

[Kelvinator1]

At the moment we have to guess just how your data is laid out and what it is actually like. We also have to manually type any thing to test with. You will generally get much faster/better responses if you provide some sample data and expected results in a form that helpers can easily copy from.

For the future, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Assuming below is doing the sort of thing you are after, see if you can adapt it to your own circumstances.

24 08 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Sales				Aged
2	Item	Result			Item
3	Item 1	V			Item 1	V	I	G
4	Item 7	D			Item 2
5	Item 3	B			Item 3	B	D	W	Z	K
6	Item 1	I			Item 4	B
7	Item 7	O			Item 5	D	J	P
8	Item 3	D			Item 6	N	V
9	Item 3	W			Item 7	D	O	A
10	Item 3	Z			Item 8
11	Item 4	B			Item 9	J
12	Item 7	A
13	Item 1	G
14	Item 9	J
15	Item 5	D
16	Item 5	J
17	Item 6	N
18	Item 5	P
19	Item 6	V
20	Item 3	K
Kelvinator1

Cell Formulas
Range	Formula
F3:H3,F9:H9,F8:G8,F7:H7,F5:J5,F4,F6,F10:F11	F3	=TRANSPOSE(FILTER(B$3:B$20,A$3:A$20=E3,""))
Dynamic array formulas.

Hi Peter:

Sorry for not including examples of the data, I now have downloaded Xl2bb and am attaching the mini sheets. I think I forgot to mention that the Aged Inventory sheet is a table that is pulling data out of SAP. For this purpose I have copied and pasted the data, but it still acts like a table. So, the first mini sheet is the Aged Inventory Report with all the extra columns hidden except for the item number, name, and then the columns for the customers that I want to fill automatically. I have also hidden everything after row 9 to make the data smaller. The second mini sheet is the Sales Report that has the sales data by customer and item number. The third mini sheet is where I manually found the customers for each of the items and copied them into columns, which is what the expected result would be when I put in a formula to do this automatically. The fourth mini sheet is the result when I used your formula. As you can see, I get #SPILL! errors for any items that have more than one customer, and then it pulls in the customer name if the item has only been sold to one customer. So how can we get the result in Mini Sheet #3 automatically? Thank you for your reply and any future help you could give me.

CFarms Aged Inventory 8-21-24 for testing with customers.xlsx
	C	D																		V	W	X	Y	Z
1	Mini Sheet #1
2	Item Code	Item Name																		Customers	Customers2	Customers3	Customers4	Customers5
3	FG-PPAJ-040-000-C44	Aji Amarillo, Ground , Untreated, 44.09 lb Carton
4	FG-PPAJ-040-000-C44	Aji Amarillo, Ground , Untreated, 44.09 lb Carton
5	FG-PPAN-014-002-C25	Chile, Ancho, Flake, Irradiated, 25 lb Carton
6	FG-PPAN-014-002-C30	Chile, Ancho, Flake, Irradiated, 30 lb Carton
7	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton
8	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton
9	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton
10	FG-PPAN-052-002-P40	Chile, Ancho Chile, Frozen Puree, Irradiated, 40 lb Pail
61
Data with blank customers

The Sales Report that shows the customers and item numbers they purchased:

CFarms Aged Inventory 8-21-24 for testing with customers.xlsx
	A	B	C	D	E
1	Mini Sheet #2
2	DocNumber	CustomerName	Warehouse	ItemCode	ItemName
3	57429	JUAN JOSÉ ALBARRACÍN, S.A.	DIRECT WAREHOUSE	FG-PPJR-016-000-C36	Chile, Red Jalapeno, Rough Cut, Untreated, 35.273 lb Carton
4	57319	HURON PRODUCE LIMITED	VALLEY COLD STORAGE	FG-PPCP-017-000-G30	Chile, Chipotle Morita, Pods with Stems, Untreated, 30 lb Bag
5	57439	HURON PRODUCE LIMITED	VALLEY COLD STORAGE	FG-PPCP-017-000-G30	Chile, Chipotle Morita, Pods with Stems, Untreated, 30 lb Bag
6	57377	HURON PRODUCE LIMITED	VALLEY COLD STORAGE	FG-PPCP-017-000-G30	Chile, Chipotle Morita, Pods with Stems, Untreated, 30 lb Bag
7	57489	HURON PRODUCE LIMITED	VALLEY COLD STORAGE	FG-PPCP-017-000-G30	Chile, Chipotle Morita, Pods with Stems, Untreated, 30 lb Bag
8	56915	HURON PRODUCE LIMITED	DIRECT WAREHOUSE	FG-PPCP-017-000-G30	Chile, Chipotle Morita, Pods with Stems, Untreated, 30 lb Bag
9	55954	MANIKER F AND G CO., LTD	CFARMS MAIN WAREHOUSE	FG-BLHM-040-000-C44	Blend, Habanero Blend, Ground, Untreated, 44.09 lb Carton
10	56216	MANIKER F AND G CO., LTD	CFARMS MAIN WAREHOUSE	FG-BLHM-040-000-C44	Blend, Habanero Blend, Ground, Untreated, 44.09 lb Carton
11	57290	G.L. MEZZETTA, INC.	CFARMS MAIN WAREHOUSE	FG-TMC7-003-000-T40	Tomato, California/Mexican Blend, Halves, 400 lb Tote
12	56364	JUAN JOSÉ ALBARRACÍN, S.A.	DIRECT WAREHOUSE	FG-PPJR-016-000-C36	Chile, Red Jalapeno, Rough Cut, Untreated, 35.273 lb Carton
13	57320	G.L. MEZZETTA, INC.	CFARMS MAIN WAREHOUSE	FG-TMC7-003-000-T40	Tomato, California/Mexican Blend, Halves, 400 lb Tote
14	56865	G.L. MEZZETTA, INC.	CFARMS MAIN WAREHOUSE	FG-TMC7-003-000-T40	Tomato, California/Mexican Blend, Halves, 400 lb Tote
15	57348	MCCORMICK & COMP-MOJAVE	VALLEY COLD STORAGE	FG-PPPO-017-100-C25	Chile, Pasilla Ancho, Pods with Stems, Untreated, 25 lb Carton
16	56596	G.L. MEZZETTA, INC.	CFARMS MAIN WAREHOUSE	FG-TMC7-003-000-T40	Tomato, California/Mexican Blend, Halves, 400 lb Tote
17	57321	MANIKER F AND G CO., LTD	CFARMS MAIN WAREHOUSE	FG-BLHM-040-000-C56	Blend, Habanero Blend, Ground, Untreated, 55.1 lb Carton
18	55627	MANIKER F AND G CO., LTD	CFARMS MAIN WAREHOUSE	FG-BLHM-040-000-C44	Blend, Habanero Blend, Ground, Untreated, 44.09 lb Carton
19	57205	JUAN JOSÉ ALBARRACÍN, S.A.	DIRECT WAREHOUSE	FG-PPJR-016-000-C36	Chile, Red Jalapeno, Rough Cut, Untreated, 35.273 lb Carton
2023-24 Sales thru August

The results when I manually looked up the item numbers in the Sales Report and copied and pasted them into the Aged Inventory Report:

CFarms Aged Inventory 8-21-24 for testing with customers.xlsx
	C	D																		V	W	X	Y	Z	AA
1	Mini Sheet #3
2	Item Code	Item Name																		Customers	Customers2	Customers3	Customers4	Customers5	Customers6
3	FG-PPAJ-040-000-C44	Aji Amarillo, Ground , Untreated, 44.09 lb Carton																		DEHYDRATES, INC.	SKIDMORE SALES & DIST. CO. INC
4	FG-PPAJ-040-000-C44	Aji Amarillo, Ground , Untreated, 44.09 lb Carton																		DEHYDRATES, INC.	SKIDMORE SALES & DIST. CO. INC
5	FG-PPAN-014-002-C25	Chile, Ancho, Flake, Irradiated, 25 lb Carton																		SAFEWAY, INC./SUPPLY	GREEK ISLAND SPICE	GOURMET FOODS INTERNATIONAL	EARTH & VINE PROVISIONS
6	FG-PPAN-014-002-C30	Chile, Ancho, Flake, Irradiated, 30 lb Carton																		SAFEWAY, INC./SUPPLY	EARTH & VINE PROVISIONS
7	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton																		BCFOODS, INC.	VICTORIAN EPICURE INC.	STIR FOODS LLC	ROBERTET CANADA	ATX SPECIALTY FOODS	QUALIA SPECIALTY PROVISIONS
8	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton																		BCFOODS, INC.	VICTORIAN EPICURE INC.	STIR FOODS LLC	ROBERTET CANADA	ATX SPECIALTY FOODS	QUALIA SPECIALTY PROVISIONS
9	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton																		BCFOODS, INC.	VICTORIAN EPICURE INC.	STIR FOODS LLC	ROBERTET CANADA	ATX SPECIALTY FOODS	QUALIA SPECIALTY PROVISIONS
10	FG-PPAN-052-002-P40	Chile, Ancho Chile, Frozen Puree, Irradiated, 40 lb Pail																		DEEN MEAT & COOKED FOODS
61
Copy & Pasting Customers

The results when I used your TRANSPOSE(FILTER formula:

CFarms Aged Inventory 8-21-24 for testing with customers.xlsx
	C	D																		V	W
1	Mini Sheet #4
2	Item Code	Item Name																		Customers	Customers2
3	FG-PPAJ-040-000-C44	Aji Amarillo, Ground , Untreated, 44.09 lb Carton																		#SPILL!
4	FG-PPAJ-040-000-C44	Aji Amarillo, Ground , Untreated, 44.09 lb Carton																		#SPILL!
5	FG-PPAN-014-002-C25	Chile, Ancho, Flake, Irradiated, 25 lb Carton																		#SPILL!
6	Formula from Peter_SSs	Chile, Ancho, Flake, Irradiated, 30 lb Carton
7	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton																		#SPILL!
8	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton																		#SPILL!
9	FG-PPAN-014-003-C25	Chile, Ancho, Flake, Steam Treated, 25 lb Carton																		#SPILL!
10	FG-PPAN-052-002-P40	Chile, Ancho Chile, Frozen Puree, Irradiated, 40 lb Pail																		DEEN MEAT & COOKED FOODS
Formula from Peter_SSs

Cell Formulas
Range	Formula
V3:V10	V3	=TRANSPOSE(FILTER('2023-24 Sales thru August'!B:B,'2023-24 Sales thru August'!D:D=[@[Item Code]],""))

Named Ranges
Name	Refers To	Cells
'2023-24 Sales thru August'!_FilterDatabase	='2023-24 Sales thru August'!$D$2:$D$2230	V3:V10

 

[Kelvinator1]

This may help.
View attachment 116046
In E3

=LET(a,A3:A9,au,UNIQUE(a),b,B3:B9,c,BYROW(au,LAMBDA(ro,TEXTJOIN("_",TRUE,FILTER(b,a=ro)))),d,TEXTSPLIT(TEXTJOIN(",",FALSE,c),"_",",",FALSE,0,""),HSTACK(au,IFERROR(VALUE(d),"")))

Hi, I couldn't get this to work at all. First of all, I have no idea what it is doing, you don't seem to be referencing individual cells (what do the a, b ,c, d, au, etc. in your formula represent?), and even when I set up your example and copied and pasted your formula into it, it did nothing, just posted a text version of the formula.

 

[Peter_SSs]

@Kelvinator1
Sorry, I have been away for a few weeks so was unable to respond. Do you still need help with this?
If so, can you give new (small) sample data where we can see where your manual results come from? For example, in your sample above, you have results like 'DEHYDRATES, INC.' and 'SKIDMORE SALES & DIST. CO. INC' which we cannot see in the original data.

 

[Kelvinator1]

@Kelvinator1
Sorry, I have been away for a few weeks so was unable to respond. Do you still need help with this?
If so, can you give new (small) sample data where we can see where your manual results come from? For example, in your sample above, you have results like 'DEHYDRATES, INC.' and 'SKIDMORE SALES & DIST. CO. INC' which we cannot see in the original data.

Hi Peter, no I ended up doing something else since I couldn't get any of the suggestions here to work. I did a pivot table on the customer-item data and then did a VLOOKUP on the item back to the aged inventory report and pulled in the quantities and values to the right of the pivot table. Thanks anyway for your help!

 

[Peter_SSs]

OK, thanks for the further information.