Index Match Formula with date and code as criteria

[bubuweas]

Hi there,

i am quite new to excel and would like to search a way to find the dates from the table list as per the attachment.
I try use the index match with multiple criteria but can't actually get it. The result shows #VALUE!.
Perhaps that i do not understand how the formula works and require some advice for this.

Formula use: =INDEX(C8:C15,MATCH(1,(B4:B9=B13)*(D4:D9=C13),0))

Appreciate the advice.

[AliGW]

Welcome to the forum!

What results are you expecting and why?

This will not work, but you should know that your ranges need to match, whatever you are doing:

=INDEX(C4:C9,MATCH(1,(B4:B9=B13)*(D4:D9=C13),0))

[bubuweas]

Hi there,

I guess the index match formula can't get what i wish for. The expected result that i wish to get from here will be the dates from Column C - End Date.

Requirement:
B13 - random dates
C13 - Codes given

Expected result: formula to find the dates in Column C - End Date and show 6/2/2018 as the result.

[samba_ravi]

=lookup(2,1/(($b$4:$b$9<=$b13)*($c$4:$c$9>=$b13)*($d$4:$d$9=$c13)),$c$4:$c$9)

[Pepe Le Mokko]

Like this?

=SUMPRODUCT(--($D$4:$D$9=C13),$B$4:$B$9<=B13,$C$4:$C$9)

[bubuweas]

Hi all,

The formula below works. I try with different date and it gives the correct result:

=lookup(2,1/(($b$4:$b$9<=$b13)*($c$4:$c$9>=$b13)*($d$4:$d$9=$c13)),$c$4:$c$9)

Thanks all for your time on this.

[kmahesh]

simple look up function will also give u the desired result.
in c13 =LOOKUP(B14,$B$4:$B$9,$D$4:$D$9)

drag down to c14