Index, Filter and Sort across two tables

[barney_t]

Hi,
I am hoping for some assistance as I admitting defeat again!

I am trying to get a formula that will extract a data/ criteria from two tables.

I have been working with Index Sortby Filter with limited success.

On the below I need the result to be the product number with the highest stock which had unit sales of zero or less.

I also need it to be grouped by type and material, therefore referencing cells K2 & K3.

The order of the products will be different in the two tables and some products may not be on the first table and the assumption would be that their sales were zero.

Lastly, without the use of a helper column.

Thank you in advance for taking the time to look at this.

Kind regards

Barney

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	type	material	product	units sold		type	material	product	stock
2	nut	metal	product1	2		nut	metal	product1	5		nut
3	nut	metal	product2	0		nut	metal	product2	6		metal
4	nut	metal	product3	-1		nut	metal	product3	7
5	nut	metal	product6	4		nut	metal	product4	5
6	nut	plastic	product7	4		bolt	plastic	product5	9
7	nut	plastic	product8	0		nut	metal	product6	5			result
8	bolt	metal	product9	8		nut	plastic	product7	5		1st hightest stock product	product3
9	bolt	metal	product10	0		nut	plastic	product8	5		2nd highest stock product	product2
10	bolt	metal	product11	10		bolt	metal	product9	5		3rd highest stock product	product4
11	bolt	plastic	product12	0		bolt	metal	product10	5
12						bolt	metal	product11	5
13						bolt	plastic	product12	5
Sheet1 (3)

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Marcelo Branco]

You're welcome and thanks for the feedback.

By the way, since Fluff's formulas used the new dynamic arrays functions, they should be faster.

M.