Retrieve the latest Price

[stormspike]

It is hard for me to describe what exactly I am looking for. Perhaps it is best that I try to illustrate it with my example.

A1: Date
A2: 10/1/2014
A3: 10/2/2014
A4: 10/9/2014
A5: 10/10/2014

B1: SKU
B2: T001
B3: T002
B4: T001
B5: T002

C1: PRICE
C2: 12
C3: 5
C4: 13
C5: 6


Suppose, A8 is my date criteria=10/1/2014, B8 is my SKU criteria=T001, C8 should return 12.
Suppose, A9 is my date criteria=10/4/2014, B9 is my SKU criteria=T001, C9 should return 12.
Suppose, A10 is my date criteria=10/9/2014, B10 is my SKU criteria=T001, C10 should return 13.

How do I write the formula on the C column so that Excel will always return the latest price based on the date that I have input on A column?

I tried to use the following formula
=INDEX($A$2:$C$5,MATCH($A$8&$B$8,$A$2:$A$5&$B$2:$B$5,1),3)

But the results get mixed up because MATCH is trying to find the closest match based on DATE but will also try to find the closest match based on SKU as well. I need MATCH to find the latest date but must find the exact SKU.

How do I go about from here? Hope some gurus can shed some lights.

 

[Aladin Akyurek]

C8, control+shift+enter, not just enter, and copy down:

=LOOKUP($A8,IF($B$2:$B$5=$B8,$A$2:$A$5),$C$2:$C$5)

 

[stormspike]

Many thanks. Works like a charm.

 

[Aladin Akyurek]

Many thanks. Works like a charm.

You are welcome. Thanks for providing feedback.