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.

[TABLE="width: 600"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Data[/TD]
[TD][/TD]
[TD="colspan: 2, align: center"]Criteria[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]201310[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]164[/TD]
[TD][/TD]
[TD]201402[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-154[/TD]
[/TR]
[TR]
[TD]201311[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]160[/TD]
[TD][/TD]
[TD]201403[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-150[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]154[/TD]
[TD][/TD]
[TD]201401[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]201405[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-145[/TD]
[/TR]
[TR]
[TD]201405[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]145[/TD]
[TD][/TD]
[TD]201406[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-140[/TD]
[/TR]
[TR]
[TD]201406[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]140[/TD]
[TD][/TD]
[TD]201312[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[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:

[TABLE="class: cms_table, width: 600"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Data[/TD]
[TD][/TD]
[TD="colspan: 2, align: center"]Criteria[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]201310[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]164[/TD]
[TD][/TD]
[TD]201402[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-154[/TD]
[/TR]
[TR]
[TD]201311[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]160[/TD]
[TD][/TD]
[TD]201403[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-150[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]154[/TD]
[TD][/TD]
[TD]201401[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-160[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]201405[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-145[/TD]
[/TR]
[TR]
[TD]201405[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]145[/TD]
[TD][/TD]
[TD]201406[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-140[/TD]
[/TR]
[TR]
[TD]201406[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]140[/TD]
[TD][/TD]
[TD]201312[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-160[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Yes sure. The desired results are as such:

[TABLE="class: cms_table, width: 600"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Data[/TD]
[TD][/TD]
[TD="colspan: 2, align: center"]Criteria[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]201310[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]164[/TD]
[TD][/TD]
[TD]201402[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-154[/TD]
[/TR]
[TR]
[TD]201311[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]160[/TD]
[TD][/TD]
[TD]201403[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-150[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]154[/TD]
[TD][/TD]
[TD]201401[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-160[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]150[/TD]
[TD][/TD]
[TD]201405[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-145[/TD]
[/TR]
[TR]
[TD]201405[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]145[/TD]
[TD][/TD]
[TD]201406[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-140[/TD]
[/TR]
[TR]
[TD]201406[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]140[/TD]
[TD][/TD]
[TD]201312[/TD]
[TD]D&D T1-24PM17X[/TD]
[TD]-160[/TD]
[/TR]
</tbody>[/TABLE]

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.