Multiple Criteria Lookup with dates

[NikShah]

Hello,

My query is that I would like to use Index and Match functions that relate to the Date and Code criteria as per query below. It can be assumed that the data box below starts from cell A1 to C8 and the criteria box from E1 to G8. The formula I have been using is the following: {=INDEX(C:C,MATCH(1,IF(A:A=E3,IF(B:B=F3,1)),0))*-1}, but for months 201401 and 201312 is retrieving an error. Ideally if there is no such date as 201312 or 201401 I would like to have a lookup that references the previous date available - in this case 201311 - which should retrieve the price of 160.

I have tried to amend the formula above to {=INDEX(C:C,MATCH(1,IF(A:A<=E3,IF(B:B=F3,1)),0))*-1}, but then it retrieves the price for 201310 which is 164.

Can anyone please help?

Regards,
Nik.

Data		Criteria	Result
Date	Code	Price		Date	Code	Price
201310	D&D T1-24PM17X	164		201402	D&D T1-24PM17X	-154
201311	D&D T1-24PM17X	160		201403	D&D T1-24PM17X	-150
201402	D&D T1-24PM17X	154		201401	D&D T1-24PM17X	#N/A
201403	D&D T1-24PM17X	150		201405	D&D T1-24PM17X	-145
201405	D&D T1-24PM17X	145		201406	D&D T1-24PM17X	-140
201406	D&D T1-24PM17X	140		201312	D&D T1-24PM17X	#N/A

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[Aladin Akyurek]

Would you post the desired results instead of the results of a non-working formula?

 

[NikShah]

Would you post the desired results instead of the results of a non-working formula?

Yes sure. The desired results are as such:

Data		Criteria	Result
Date	Code	Price		Date	Code	Price
201310	D&D T1-24PM17X	164		201402	D&D T1-24PM17X	-154
201311	D&D T1-24PM17X	160		201403	D&D T1-24PM17X	-150
201402	D&D T1-24PM17X	154		201401	D&D T1-24PM17X	-160
201403	D&D T1-24PM17X	150		201405	D&D T1-24PM17X	-145
201405	D&D T1-24PM17X	145		201406	D&D T1-24PM17X	-140
201406	D&D T1-24PM17X	140		201312	D&D T1-24PM17X	-160

<tbody>
</tbody>

 

[Aladin Akyurek]

Yes sure. The desired results are as such:

Data		Criteria	Result
Date	Code	Price		Date	Code	Price
201310	D&D T1-24PM17X	164		201402	D&D T1-24PM17X	-154
201311	D&D T1-24PM17X	160		201403	D&D T1-24PM17X	-150
201402	D&D T1-24PM17X	154		201401	D&D T1-24PM17X	-160
201403	D&D T1-24PM17X	150		201405	D&D T1-24PM17X	-145
201405	D&D T1-24PM17X	145		201406	D&D T1-24PM17X	-140
201406	D&D T1-24PM17X	140		201312	D&D T1-24PM17X	-160

<tbody>
</tbody>

Thanks for the sample and the associated desired results.

In G3 control+shift+enter, not just enter, and copy down:

=-1*INDEX($C$3:$C$8,MAX(IF($A$3:$A$8<=E3,IF($B$3:$B$8=F3,ROW($C$3:$C$8)-ROW($C$3)+1))))

 

[NikShah]

Thanks for the sample and the associated desired results.

In G3 control+shift+enter, not just enter, and copy down:

=-1*INDEX($C$3:$C$8,MAX(IF($A$3:$A$8<=E3,IF($B$3:$B$8=F3,ROW($C$3:$C$8)-ROW($C$3)+1))))

That's brilliant! Thank you very much.

 

[Aladin Akyurek]

That's brilliant! Thank you very much.

You are welcome.