Find price of an item based on the most recent date

[brownt21390]

I'm working on constantly changing retail price for a list of items, and I want to pull the most recent price based on date.

For a simple example, if column A is Item # (A1), column B is Price (B1), & column C is Date (C1), let's say one unique item is Apple, and there are 3 entries for Apple (A2,A3,A4), the first for $1.00 (B2) dated 1/1/18 (C2), the second for $2.00 (B3) at 2/1/18 (C3), and the third for $3.00 (B4) at 3/1/18 (C4).

Let's say my results' table is F1:G2, where F1 is Item #, G1 is Price, F2 the item I'm looking for a result (Apple), and G2 is my result formula:

Right now, I've come up with {=MAX(IF(F2=$A$2:$A$4,$C$2:$C$4))} (which gives a result of 43160 or 3/1/18 in date form) to give me a match to the max possible date which Apple's price has changed, but now I want my result to show price relative to that date ($3.00).

How do I now make reference to this?
Thanks for any and all help!

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter:

=INDEX($B$2:$B$4,MATCH(MAX(IF($A$2:$A$4=F2,$C$2:$C$4)),$C$2:$C$4,0))

 

[brownt21390]

This looks to work perfectly! Thank you!!!

 

[Aladin Akyurek]

This looks to work perfectly! Thank you!!!

You are welcome. I want to add though that there can be more than one price on the max date. That requires a different formula...

 

[brownt21390]

Actually, I'm still trying to figure out exactly why this happens, but it's giving me the same result for ever item number.

I have multiple (thousands) of items that are going to have the same max date - is that why I'm getting the same value for all my results?

 

[Aladin Akyurek]

Actually, I'm still trying to figure out exactly why this happens, but it's giving me the same result for ever item number.

I have multiple (thousands) of items that are going to have the same max date - is that why I'm getting the same value for all my results?

Book1
	A	B	C	D	E	F	G	H	I	J
1	item	price	date			ITEM	MOST RECENT DATE
2	apple	$1.00	1/1/2018			apple	3/1/2018	PRICE:	$3.00	$4.00
3	apple	$2.00	2/1/2018
4	apple	$3.00	3/1/2018
5	pear	$6.00	3/1/2018
6	apple	$4.00	3/1/2018
Sheet1

If we run

=INDEX($B$2:$B$4,MATCH(MAX(IF($A$2:$A$4=$F2,$C$2:$C$4)),$C$2:$C$4,0))

on this data, confirmed with control+shift+enter, we will get the first price that occurs on the most recent date associated with apple. The result is obviously $3.00.


As I noted, there can be more price records on the most recent date associated with the item we are interested in. The above sample contains indeed two price records on the most recent date associated with apple. In the above sample these prices are in order of occurrence $3.00 and $4.00. The following formula set up captures this fact.

In I2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$F2,IF($C$2:$C$10=MAX(IF($A$2:$A$10=$F2,$C$2:$C$10)),ROW($B$2:$B$10)-ROW($B$2)+1)),COLUMNS($I$2:I2))),"")

As claimed, this formula lists the observed results.