Index Match help

[davidmor]

Hi

i posted a messy question yesterday and have got to a point where certain things make sense but still cant get the index match to work as i want.

on the sheet below i need cells AC, AD and AE to report the information from cells A, I and U.

at present the example is blank but on the report i run this has unrequired information in it.

i have tried using an index match formula which Fluff helped with for another similar sheet, and for some reason it wont pull the date, customer or destination across.

is there a way that i can get the sheet to compare AA to F and then report the information from cell D into AC, Cell a into AD and Cell U into AE.

Thanks for any assistance

Declarations sheet chep.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Customer			Despatch Date		Shipping Req			No of pallets												Destination						shipping ref	Qty	Date	Customer	Destination
2	Park Cakes Ltd Oldham			12/08/2022		355875			4												Oldham						355875	4		#N/A	#N/A
3	Park Cakes Ltd Oldham			12/08/2022		343858			1												Oldham						343858	2		#N/A	#N/A
4	Park Cakes Ltd Oldham			12/08/2022		343858			1												Oldham						347978	26		#N/A	#N/A
5	Lindt & Sprungli UK Ltd			12/08/2022		347978			26												Coventry						355777	5		#N/A	#N/A
6	Park Cakes Ltd Oldham			12/08/2022		355777			2												Oldham						356553	15		#N/A	#N/A
7	Park Cakes Ltd Oldham			12/08/2022		355777			2												Oldham						356590	1		#N/A	#N/A
8	Park Cakes Ltd Oldham			12/08/2022		355777			1												Oldham						356907	3		#N/A	#N/A
9	Mars Wrigley Confectionery UK Ltd			12/08/2022		356553			15												NELSON						356908	19		#N/A	#N/A
10	Park Cakes Ltd Bolton			12/08/2022		356590			1												Bolton						356918	2		#N/A	#N/A
11	TATA CONSUMER PRODUCTS GB LIMITED			12/08/2022		356907			3												Stockton-on-Tees						357011	6		#N/A	#N/A
12	Finlay Beverages			12/08/2022		356908			1												Pontefract						357043	3		#N/A	#N/A
13	Finlay Beverages			12/08/2022		356908			3												Pontefract						357113	1		#N/A	#N/A
14	Finlay Beverages			12/08/2022		356908			1												Pontefract						357167	11		#N/A	#N/A
15	Finlay Beverages			12/08/2022		356908			1												Pontefract						357271	2		#N/A	#N/A
16	Finlay Beverages			12/08/2022		356908			1												Pontefract						357291	24		#N/A	#N/A
17	Finlay Beverages			12/08/2022		356908			1												Pontefract						357309	14		#N/A	#N/A
18	Finlay Beverages			12/08/2022		356908			1												Pontefract						357314	23		#N/A	#N/A
19	Finlay Beverages			12/08/2022		356908			1												Pontefract						357315	20		#N/A	#N/A
20	Finlay Beverages			12/08/2022		356908			1												Pontefract						357316	20		#N/A	#N/A
21	Finlay Beverages			12/08/2022		356908			1												Pontefract						357320	8		#N/A	#N/A
22	Finlay Beverages			12/08/2022		356908			1												Pontefract						357324	21		#N/A	#N/A
23	Finlay Beverages			12/08/2022		356908			2												Pontefract						357346	5		#N/A	#N/A
24	Finlay Beverages			12/08/2022		356908			1												Pontefract						357313	2		#N/A	#N/A
25	Finlay Beverages			12/08/2022		356908			2												Pontefract						357336	2		#N/A	#N/A
26	Finlay Beverages			12/08/2022		356908			1												Pontefract						357340	1		#N/A	#N/A
27	Fox's Biscuits Limited			12/08/2022		356918			2												West Yorkshire						357343	1		#N/A	#N/A
28	Fox's Biscuits Limited			12/08/2022		357011			2												West Yorkshire						347925	17		#N/A	#N/A
29	Fox's Biscuits Limited			12/08/2022		357011			2												West Yorkshire						347979	26		#N/A	#N/A
30	Fox's Biscuits Limited			12/08/2022		357011			2												West Yorkshire						349161	3		#N/A	#N/A
31	Magna Specialist Confectioners Ltd			12/08/2022		357043			3												Telford						357337	3		#N/A	#N/A
32	Fox's Biscuits Limited			12/08/2022		357113			1												West Yorkshire						357338	1		#N/A	#N/A
MASTER

Cell Formulas
Range	Formula
AA2:AA453	AA2	=UNIQUE(FILTER(LEFT(F2:F10000,8),F2:F10000<>""))
AE2:AE453	AE2	=INDEX(U2:U10000,MATCH(AA2#&"*",F2:F10000,0))
AB2:AB32	AB2	=SUMIFS(I:I,F:F,AA2)
AD2	AD2	=INDEX(A$2:A$10000,MATCH(AA2&"*",F$2:F$10000))
AD3:AD32	AD3	=INDEX(A$2:A$10000,MATCH(AA3&"*",F$2:F$10000,0))
Dynamic array formulas.

 

[jasonb75]

Why are you using LEFT(F2:F10000,8) in the first formula? I can see no reason for it and it will be the reason (or at least part of) why the other formulas are failing.

See if this does what you need.

(post edited with revised formula)

Book1
	AA	AB	AC	AD	AE
1	shipping ref	Qty	Date	Customer	Destination
2	355875	4	12/08/2022	Park Cakes Ltd Oldham	Oldham
3	343858	2	12/08/2022	Park Cakes Ltd Oldham	Oldham
4	347978	26	12/08/2022	Lindt & Sprungli UK Ltd	Coventry
5	355777	5	12/08/2022	Park Cakes Ltd Oldham	Oldham
6	356553	15	12/08/2022	Mars Wrigley Confectionery UK Ltd	NELSON
7	356590	1	12/08/2022	Park Cakes Ltd Bolton	Bolton
8	356907	3	12/08/2022	TATA CONSUMER PRODUCTS GB LIMITED	Stockton-on-Tees
9	356908	19	12/08/2022	Finlay Beverages	Pontefract
10	356918	2	12/08/2022	Fox's Biscuits Limited	West Yorkshire
11	357011	6	12/08/2022	Fox's Biscuits Limited	West Yorkshire
12	357043	3	12/08/2022	Magna Specialist Confectioners Ltd	Telford
13	357113	1	12/08/2022	Fox's Biscuits Limited	West Yorkshire
Sheet2

Cell Formulas
Range	Formula
AA2:AA13	AA2	=UNIQUE(F2:INDEX(F:F,MATCH(1E+100,F:F)))
AB2:AB13	AB2	=SUMIFS(I2:I10000,F2:F10000,AA2#)
AC2:AE13	AC2	=INDEX(A2:U10000,MATCH(AA$2#,F2:F10000,0),{4,1,21})
Dynamic array formulas.

 

[davidmor]

Hi Jason

thanks for the response, i was using the Left as its what was in a previous sheet but the Ref nos were slightly different.

what you have given works great.

would there be a way that i could exclude certain customers from this or is it easier just to filter them out ?

Thanks Again

 

[jasonb75]

You could possibly use the filter function within the formulas to exclude some customers. Do you have a list of the customers to exclude in your sheet somewhere?