I am having difficulty trying to complete a formula which involves extracting data from a two separate tables to return a product number.

The objective is to return the products with the greatest variance in units to stock. (I am looking to have the top 10 products where the units are greater than the stock)

I have the following formula which gives the required results but does not put them in correct order. I believe the issue may be with the sortby .... FILTER(J2:J11-D2:D11, ?

=INDEX(SORTBY(FILTER(C2:C11,(ED:D11<=XLOOKUP(C2:C11,I2:I9,J2:J9,0))*(A2:A11=L8)*(B2:B11=L9)*(D2:D11>0),""),FILTER(J2:J11-D2:D11,(D2:D11>=XLOOKUP(C2:C11,I2:I11,J2:J11,0))*(A2:A11=L8)*(B2:B11=L9)*(D2:D11>0),""),-1),1)

The result will need to be filtered by type and material and when the units are greater than zero without the user of a helper column .

If you require any further information just let me know.

Kind Regards

Barney

Book1.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|

B | C | D | E | F | G | H | I | J | K | L | |||

1 | material | product | units | type | material | product | stock | ||||||

2 | metal | product1 | 2 | nut | metal | product4 | 4 | ||||||

3 | metal | product2 | 2 | nut | metal | product5 | 6 | ||||||

4 | metal | product3 | 4 | bolt | metal | product6 | 0 | ||||||

5 | plastic | product4 | 5 | nut | plastic | product7 | 4 | ||||||

6 | metal | product5 | 8 | bolt | metal | product8 | 6 | ||||||

7 | plastic | product6 | 9 | bolt | plastic | product9 | 6 | ||||||

8 | metal | product7 | 10 | bolt | metal | product1 | 4 | nut | |||||

9 | plastic | product8 | 0 | bolt | plastic | product2 | 3 | metal | |||||

10 | metal | product9 | 1 | nut | metal | product3 | 0 | ||||||

11 | plastic | product10 | 6 | nut | plastic | product11 | 6 | ||||||

12 | Top Variance 1 | ||||||||||||

13 | Top Variance 2 | ||||||||||||

Sheet5 (3) |