Adding to a Index Sorted filtered formula, match an additional criteria from a separate table

[barney_t]

Hi
I am having difficulty trying to complete a formula which involves extracting data from a separate table to return the product number.

I have the following formula which would work if column "H" was in the same table or ran in the same order as the left table. (columns A to E).

=INDEX(SORTBY(FILTER(C2:C11,(H2:H11>=E2:E11)*(A2:A11=H8)*(B2:B11=H9)),FILTER(E2:E11-D2:D11,(H2:H11>=E2:E11)*(A2:A11=H8)*(B2:B11=H9)),-1),1)

I am after a solution that would replace or adapt the H2:H11 in the above formula so that the data can be checked on a second table (columns G to H on the below).

Therefore without the use of helper columns, return the product name of the greatest increase week on week when filtered by type and material where the stock is greater or equal to week two's units .

If you require any further information just let me know.

Kind Regards
Barney

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	type	material	product	units wk1	units wk2		product	stock
2	nut	metal	product1	2	2		product5	4
3	nut	metal	product2	2	5		product6	6
4	bolt	metal	product3	4	5		product7	6
5	nut	plastic	product4	5	5		product4	4
6	bolt	metal	product5	8	5		product2	6
7	bolt	plastic	product6	9	5		product1	6	nut in metal top gain providing stock >= to wk2 units?
8	bolt	metal	product7	10	5		product10	4	nut
9	bolt	plastic	product8	0	5		product9	3	metal
10	nut	metal	product9	1	5				Top Gain	=INDEX(SORTBY(FILTER(C2:C11,(H2:H11>=E2:E11)*(A2:A11=H8)*(B2:B11=H9)),FILTER(E2:E11-D2:D11,(H2:H11>=E2:E11)*(A2:A11=H8)*(B2:B11=H9)),-1),1)
11	nut	plastic	product10	6	5
12
13
14
15
16
Sheet1 (2)

 

[barney_t]

Hi, Apologies, I just noticed that the formula quoted was mis aligned, below is how it should have read.
=INDEX(SORTBY(FILTER(C2:C11,(H2:H11>=E2:E11)*(A2:A11=I8)*(B2:B11=I9)),FILTER(E2:E11-D2:D11,(H2:H11>=E2:E11)*(A2:A11=I8)*(B2:B11=I9)),-1),1)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	type	material	product	units wk1	units wk2		product	stock
2	nut	metal	product1	2	2		product5	4
3	nut	metal	product2	2	5		product6	6
4	bolt	metal	product3	4	5		product7	6
5	nut	plastic	product4	5	5		product4	4
6	bolt	metal	product5	8	5		product2	6
7	bolt	plastic	product6	9	5		product1	6	nut in metal top gain providing stock >= to wk2 units?
8	bolt	metal	product7	10	5		product10	4	nut
9	bolt	plastic	product8	0	5		product9	3	metal
10	nut	metal	product9	1	5				Top Gain	=INDEX(SORTBY(FILTER(C2:C11,(H2:H11>=E2:E11)*(A2:A11=I8)*(B2:B11=I9)),FILTER(E2:E11-D2:D11,(H2:H11>=E2:E11)*(A2:A11=I8)*(B2:B11=I9)),-1),1)
11	nut	plastic	product10	6	5
12
13
14
15
16
Sheet1 (2)

 

[Fluff]

Two options in case you don't have the LET function

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	type	material	product	units wk1	units wk2		product	stock
2	nut	metal	product1	2	2		product5	4
3	nut	metal	product2	2	5		product6	6
4	bolt	metal	product3	4	5		product7	6
5	nut	plastic	product4	5	5		product4	4
6	bolt	metal	product5	8	5		product2	6
7	bolt	plastic	product6	9	5		product1	6
8	bolt	metal	product7	10	5		product10	4	nut
9	bolt	plastic	product8	0	5		product9	3	metal
10	nut	metal	product9	1	5				Top Gain	product2
11	nut	plastic	product10	6	5					product2
12
Data

Cell Formulas
Range	Formula
J10	J10	=INDEX(SORTBY(FILTER(C2:C11,(E2:E11<XLOOKUP(C2:C11,G2:G9,H2:H9,0))*(A2:A11=I8)*(B2:B11=I9),""),FILTER(E2:E11-D2:D11,(E2:E11<XLOOKUP(C2:C11,G2:G9,H2:H9,0))*(A2:A11=I8)*(B2:B11=I9),""),-1),1)
J11	J11	=LET(Ary,(E2:E11<XLOOKUP(C2:C11,G2:G9,H2:H9,0))*(A2:A11=I8)*(B2:B11=I9),INDEX(SORTBY(FILTER(C2:C11,Ary,""),FILTER(E2:E11-D2:D11,Ary,""),-1),1))

 

[barney_t]

Hi Fluff
Thank you once again for coming to the rescue.
It works perfectly.
Many thanks
Barney

 

[Fluff]

You're welcome & thanks for the feedback.