extract values based on next two columns

[noubaba]

Dear Experts,
Need your help to develop a formula,
I have some data in "available Data" table and in other excel sheet I have some material codes in column E, which I want to lookup but the condition is for example if this code 100010 having zero in column C then it must be skipped and return valve must be 45003 and in F3 and 1 in G3, and if any code like 100013 do not have 1 in column C then return value must be 45009 in F4 and 0 in G4. Moreover For code 100015 there are two PO numbers 45005 and 450020 and both having zero in column C, in this case latest value with 450020 must be captured in F6. attached picture can be referred as well. Thank you

question Book1.xlsx
	A	B	C	D	E	F	G
1	available Data				This is how I want to extract based on available data
2	material code	PO numbers	Quantity		material code	PO numbers	Quantity
3	100010	45001	0		100010	45003	1
4	100010	45002	0		100013	45009	0
5	100010	45003	1		100014	450011	1
6	100013	45009	0		100015	450020	0
7	100014	45007	0
8	100014	450011	1
9	100014	45022	0
10	100015	45005	0
11	100015	450020	0
Sheet1

 

[Glenn Kennedy]

You have not told us what your Excel product is... If it is O365, try this:

It assumes that your first row of DATA is in cell A3, and that the area where you expect results to appear is BLANK.

=LET(A,A3:A11,B,B3:C11,C,UNIQUE(A),VSTACK(A2:C2,HSTACK(C,DROP(REDUCE("",C,LAMBDA(x,y,(VSTACK(x,IFERROR(FILTER(B,(A=y)*(INDEX(B,,2)<>0)),TAKE(FILTER(B,A=y),-1)))))),1))))

 

[Glenn Kennedy]

Book1.xlsx
	A	B	C	D	E	F	G
1
2	material code	PO numbers	Quantity		material code	PO numbers	Quantity
3	100010	45001	0		100010	45003	1
4	100010	45002	0		100013	45009	0
5	100010	45003	1		100014	450011	1
6	100013	45009	0		100015	450020	0
7	100014	45007	0
8	100014	450011	1
9	100014	45022	0
10	100015	45005	0
11	100015	450020	0
12
Sheet1

Cell Formulas
Range	Formula
E2:G6	E2	=LET(A,A3:A11,B,B3:C11,C,UNIQUE(A),VSTACK(A2:C2,HSTACK(C,DROP(REDUCE("",C,LAMBDA(x,y,(VSTACK(x,IFERROR(FILTER(B,(A=y)*(INDEX(B,,2)<>0)),TAKE(FILTER(B,A=y),-1)))))),1))))
Dynamic array formulas.

 

[noubaba]

I have office pro plus 2016,
Thank You Glenn for your help but this is not what I want, please allow me to explain again.
I have table 1 in one sheet and on the other sheet I have table 2. In table 2 I have only material codes in column E which I want to look up in table 1 range. But I want to capture specific PO numbers in column F and Quantity in G. for example as I mentioned in table 3. I need Vlookup or Xlookup or any other formula which I can apply in F3 and G3 and then drag below.

question Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	table 1				Table 2				Table 3 - Result after lookup material codes in table 1. Want to capture specific PO numbers and Quantity
2	material code	PO numbers	Quantity		material code	PO numbers	Quantity		material code	PO numbers	Quantity
3	100010	45001	0		100010				100010	45003	1
4	100010	45002	0		100013				100013	45009	0
5	100010	45003	1		100014				100014	450011	1
6	100013	45009	0		100015				100015	450020	0
7	100014	45007	0
8	100014	450011	1
9	100014	45022	0
10	100015	45005	0
11	100015	450020	0
Sheet1 (2)

 

[Glenn Kennedy]

Your explanation is FAR from clear. Obviously my first attempt won't work with your older Excel product. So try this (again a guess, as your requirement was not at all clear):

Book4.xlsx
	A	B	C	D	E	F	G	H	I
1
2	2	material code	PO numbers	Quantity		material code	PO numbers	Quantity
3	3	100010	45001	0		100010	45003	1
4	4	100010	45002	0		100013	45009	0
5	5	100010	45003	1		100014	450011	1
6	6	100013	45009	0		100015	450020	0
7	7	100014	45007	0
8	8	100014	450011	1
9	9	100014	45022	0
10	10	100015	45005	0
11	11	100015	450020	0
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
G3:H10	G3	=IF($F3="","",INDEX(C:C,AGGREGATE(14,6,ROW($B$3:$B$19)/(($B$3:$B$19=$F3)*(IF(SUMIF($B:$B,$F3,$D:$D)=0,1,($D$3:$D$19<>0)))),1)))

 

[Glenn Kennedy]

In the above... the formula returns the row corresponding to the last non-zero value in columns C for each code. If all values in column C, for an individual code, are zero, it returns the last one.

 

[Scott Huish]

In your sample data, it appears that if there is a 1, it is your highest # PO. If that is actually the case then I think this should work.

Book2
	A	B	C	D	E	F	G
1	material code	PO numbers	Quantity		material code	PO numbers	Quantity
2	100010	45001	0		100010	45003	1
3	100010	45002	0		100013	45009	0
4	100010	45003	1		100014	450011	1
5	100013	45009	0		100015	450020	0
6	100014	45007	0
7	100014	450011	1
8	100014	45022	0
9	100015	45005	0
10	100015	450020	0
Sheet1

Cell Formulas
Range	Formula
F2:G5	F2	=MAXIFS(B:B,$A:$A,$E2)

 

[Fluff]

I have office pro plus 2016,

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)