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

#### barney_t

##### New Member
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
ABCDEFGHIJKL
1typematerialproductunits wk1units wk2productstock
2nutmetalproduct122product54
3nutmetalproduct225product66
4boltmetalproduct345product76
5nutplasticproduct455product44
6boltmetalproduct585product26
7boltplasticproduct695product16nut in metal top gain providing stock >= to wk2 units?
8boltmetalproduct7105product104nut
9boltplasticproduct805product93metal
10nutmetalproduct915Top 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)
11nutplasticproduct1065
12
13
14
15
16
Sheet1 (2)

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### barney_t

##### New Member
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
ABCDEFGHIJKL
1typematerialproductunits wk1units wk2productstock
2nutmetalproduct122product54
3nutmetalproduct225product66
4boltmetalproduct345product76
5nutplasticproduct455product44
6boltmetalproduct585product26
7boltplasticproduct695product16nut in metal top gain providing stock >= to wk2 units?
8boltmetalproduct7105product104nut
9boltplasticproduct805product93metal
10nutmetalproduct915Top 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)
11nutplasticproduct1065
12
13
14
15
16
Sheet1 (2)

#### Fluff

##### MrExcel MVP, Moderator
Two options in case you don't have the LET function
+Fluff 1.xlsm
ABCDEFGHIJ
1typematerialproductunits wk1units wk2productstock
2nutmetalproduct122product54
3nutmetalproduct225product66
4boltmetalproduct345product76
5nutplasticproduct455product44
6boltmetalproduct585product26
7boltplasticproduct695product16
8boltmetalproduct7105product104nut
9boltplasticproduct805product93metal
10nutmetalproduct915Top Gainproduct2
11nutplasticproduct1065product2
12
Data
Cell Formulas
RangeFormula
J10J10=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)
J11J11=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

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
9
Views
219
Replies
5
Views
72
Replies
16
Views
477
Replies
6
Views
81
Replies
11
Views
381

1,129,858
Messages
5,638,722
Members
417,049
Latest member
baka416

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back