How to pick-up data & populate across column?

[xlmaniac]

Dear All,
I do have the following sample data set spread across A1:D6
I would like to pick-up the Supplier Code against the latest PO date & populate the same across column E against one Article Code.
Pls help with the formula in column E which can deliver the said result.

Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
1​	a	03-Jan​	10​	30​
1​	b	09-Jan​	20​	30​
1​	s	29-Jan​	30​	30​
1​	d	24-Jan​	30​	30​
1​	e	15-Jan​	30​	30​
2​	x	18-Jan​	40​	50​
2​	y	21-Jan​	50​	50​
2​	z	10-Jan​	50​	50​

​		​	​	​

 

[DRSteele]

working

 

[xlmaniac]

Thanks for the reply DRSteele!!
Pls let me know if you need any further clarification from my side in this.

 

[Peter_SSs]

Is this what you mean?

A_MrExcel.xlsm
	A	B	C	D	E
1	Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
2	1	a	3-Jan	10	30
3	1	b	9-Jan	20	30
4	1	s	29-Jan	30	30
5	1	d	24-Jan	30	30
6	1	e	15-Jan	30	30
7	2	x	18-Jan	40	50
8	2	y	21-Jan	50	50
9	2	z	10-Jan	50	50
Last PO

Cell Formulas
Range	Formula
E2:E9	E2	=INDEX(D:D,AGGREGATE(15,6,ROW(A$2:A$9)/((A$2:A$9=A2)*(C$2:C$9=MAXIFS(C$2:C$9,A$2:A$9,A2))),1))

Edit: If you don't have the MAXIFS function (please update your Account details so we know what Excel version(s) you have) then try

=INDEX(D:D,AGGREGATE(15,6,ROW(A$2:A$9)/((A$2:A$9=A2)*(C$2:C$9=AGGREGATE(14,6,C$2:C$9/(A$2:A$9=A2),1))),1))

 

[DanteAmor]

Here another formula for you to consider:

Note: is an array formula. Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Book1
	A	B	C	D	E
1	Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
2	1	a	03/01/2020	10	30
3	1	b	09/01/2020	20	30
4	1	s	29/01/2020	30	30
5	1	d	24/01/2020	30	30
6	1	e	15/01/2020	30	30
7	2	x	02/01/2020	40	50
8	2	y	21/01/2020	50	50
9	2	z	10/01/2020	50	50
sheet

Cell Formulas
Range	Formula
E2:E9	E2	{=MAX(($A$2:$A$9=A2)*($C$2:$C$9=LARGE(IF($A$2:$A$9=A2,$C$2:$C$9),1))*($D$2:$D$9))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DRSteele]

Oh heavens!

I pasted something and it was all wrong so I edited the posting with something neutral. Then I just plumb forgot to come back and finish the job. So sorry.

PeterSS will surely sort your project.

 

[Peter_SSs]

Actually, my previous suggestion doesn't need the INDEX if the Supplier code is always numerical

A_MrExcel.xlsm
	A	B	C	D	E
1	Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
2	1	a	3-Jan	10	30
3	1	b	9-Jan	20	30
4	1	s	29-Jan	30	30
5	1	d	24-Jan	30	30
6	1	e	15-Jan	30	30
7	2	x	18-Jan	40	50
8	2	y	21-Jan	50	50
9	2	z	10-Jan	50	50
Last PO (2)

Cell Formulas
Range	Formula
E2:E9	E2	=AGGREGATE(15,6,D$2:D$9/((A$2:A$9=A2)*(C$2:C$9=MAXIFS(C$2:C$9,A$2:A$9,A2))),1)

.. or if no MAXIFS:
=AGGREGATE(15,6,D$2:D$9/((A$2:A$9=A2)*(C$2:C$9=AGGREGATE(14,6,C$2:C$9/(A$2:A$9=A2),1))),1)

 

[DanteAmor]

Here another "regular" formula that does not require ctrl+shift+enter to accept it, for you to consider:

Book1
	A	B	C	D	E
1	Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
2	1	a	03/01/2020	10	30
3	1	b	09/01/2020	20	30
4	1	s	29/01/2020	30	30
5	1	d	24/01/2020	30	30
6	1	e	15/01/2020	30	30
7	2	x	18/01/2020	40	50
8	2	y	21/01/2020	50	50
9	2	z	10/01/2020	50	50
Sheet7

Cell Formulas
Range	Formula
E2:E9	E2	=LOOKUP(2,1/(($A$2:$A$9=A2)*($C$2:$C$9=MAX(($A$2:$A$9=A2)*($C$2:$C$9)))),($D$2:$D$9))

 

[xlmaniac]

Is this what you mean?

A_MrExcel.xlsm
	A	B	C	D	E
1	Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
2	1	a	3-Jan	10	30
3	1	b	9-Jan	20	30
4	1	s	29-Jan	30	30
5	1	d	24-Jan	30	30
6	1	e	15-Jan	30	30
7	2	x	18-Jan	40	50
8	2	y	21-Jan	50	50
9	2	z	10-Jan	50	50
Last PO

Cell Formulas
Range	Formula
E2:E9	E2	=INDEX(D:D,AGGREGATE(15,6,ROW(A$2:A$9)/((A$2:A$9=A2)*(C$2:C$9=MAXIFS(C$2:C$9,A$2:A$9,A2))),1))

Edit: If you don't have the MAXIFS function (please update your Account details so we know what Excel version(s) you have) then try

=INDEX(D:D,AGGREGATE(15,6,ROW(A$2:A$9)/((A$2:A$9=A2)*(C$2:C$9=AGGREGATE(14,6,C$2:C$9/(A$2:A$9=A2),1))),1))

Dear Sir,
I am on Excel 2010 and used your 2nd solution.
It is truly mesmerizing.
Thanks a lot for helping me again.
Regards

 

[xlmaniac]

Here another "regular" formula that does not require ctrl+shift+enter to accept it, for you to consider:

Book1
	A	B	C	D	E
1	Article Code	PO No	PO Date	Supplier Code	Supplier Code Against Last PO Date
2	1	a	03/01/2020	10	30
3	1	b	09/01/2020	20	30
4	1	s	29/01/2020	30	30
5	1	d	24/01/2020	30	30
6	1	e	15/01/2020	30	30
7	2	x	18/01/2020	40	50
8	2	y	21/01/2020	50	50
9	2	z	10/01/2020	50	50
Sheet7

Cell Formulas
Range	Formula
E2:E9	E2	=LOOKUP(2,1/(($A$2:$A$9=A2)*($C$2:$C$9=MAX(($A$2:$A$9=A2)*($C$2:$C$9)))),($D$2:$D$9))

Dear DanteAmor,
Thanks a lot for your soultions.
It is yielding the desired result.
Thanks again!!