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

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff
Thank you once again for coming to the rescue.
It works perfectly.
Many thanks
Barney
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,429
Messages
5,642,063
Members
417,255
Latest member
Martin Dyulgyarov

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top