Formula to return a value based on criteria

[helpme20]

I am looking for a formula to produce a value based on the last date.

Column A is the Product Name
Column B is the Date of Purchase
Column C is the Product Cost by Unit of Measure


Can someone help me with the formula in Column D to return the Last Product Cost (Column C) for the most recent purchase (Column B) of the Product name (Column A) ?


Thank you very much.

Brian

 

[helpme20]

Wasn't sure if there was an Aggregate formula I should use?

 

[Aladin Akyurek]

If dates are in chronological order, in D2 entrer and copy down:

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$400=$A2),$C$2:$C$400)

which might suffice.

 

[helpme20]

I appreciate the response, but that is not returning the right values. The information is sorted by column A (this has multiple product names in this column) and then column B, newest to oldest.

 

[Aladin Akyurek]

I appreciate the response, but that is not returning the right values. The information is sorted by column A (this has multiple product names in this column) and then column B, newest to oldest.

Does this work?

=INDEX($C$2:$C$400,MATCH($A2,INDEX($A$2:$A$400,0),0))

 

[markmzz]

I am looking for a formula to produce a value based on the last date.

Column A is the Product Name
Column B is the Date of Purchase
Column C is the Product Cost by Unit of Measure

Can someone help me with the formula in Column D to return the Last Product Cost (Column C) for the most recent purchase (Column B) of the Product name (Column A) ?

Thank you very much.

Brian

Hi!

Maybe the the Array Formula (use Ctrl+Shift+Enter to enter the formula) below can helps.

In D2 and copy down

=IF(COUNTIF(A$2:A2,A2)>1,"",
LARGE(IF(B$2:B$17=LARGE(IF(A$2:A$17=A2,B$2:B$17),1),C$2:C$17),1))

	A	B	C	D	E
1	Product	Data of Purchase	Cost by Unit of Measure	Last Cost
2	Product14	01/01/2018	153	192
3	Product13	02/01/2018	279	279
4	Product10	03/01/2018	224	224
5	Product12	04/01/2018	271	271
6	Product14	05/01/2018	198
7	Product11	06/01/2018	287	182
8	Product14	07/01/2018	132
9	Product12	08/01/2018	136
10	Product15	09/01/2018	227	291
11	Product11	10/01/2018	260
12	Product11	11/01/2018	182
13	Product12	12/01/2018	117
14	Product14	13/01/2018	279
15	Product15	14/01/2018	291
16	Product12	15/01/2018	271
17	Product14	16/01/2018	192
18
***	*********	***********	************	*********	**

<tbody>
</tbody>

Markmzz

 

[markmzz]

You can try the formula below too:

=IF(COUNTIF(A$2:A2,A2)>1,"",INDEX(C$2:C$17,MATCH(1,
INDEX(1*((A$2:A$17=A2)*(B$2:B$17)=LARGE((A$2:A$17=A2)*B$2:B$17,1)),),0)))

Markmzz

 

[helpme20]

You can try the formula below too:

=IF(COUNTIF(A$2:A2,A2)>1,"",INDEX(C$2:C$17,MATCH(1,
INDEX(1*((A$2:A$17=A2)*(B$2:B$17)=LARGE((A$2:A$17=A2)*B$2:B$17,1)),),0)))

Markmzz

This one worked. Thank you. I could not get the formula in the previous post to work.

 

[helpme20]

Does this work?

=INDEX($C$2:$C$400,MATCH($A2,INDEX($A$2:$A$400,0),0))

Yes, that did work as well. Thank you for checking this one.

 

[markmzz]

Hi!

Maybe the the Array Formula (use Ctrl+Shift+Enter to enter the formula) below can helps.

In D2 and copy down

=IF(COUNTIF(A$2:A2,A2)>1,"",
LARGE(IF(B$2:B$17=LARGE(IF(A$2:A$17=A2,B$2:B$17),1),C$2:C$17),1))

Markmzz

This one worked. Thank you. I could not get the formula in the previous post to work.

You are welcome and thanks for the feedback.

By the way, for the first formula (array formula) to work, you must to press Ctrl+Shift+Enter to enter the formula.

Markmzz ​