Index Match Based on date range and name criteria

Try this

This is an array formula must be entered with Ctrl+Shift and Enter key combination.

=INDEX($B$3:$B$7,IF($C$3:$D$7=G9,MATCH(G8,$A$3:$A$7)))

Formula a gave you returns the correct result: =Nike. On your spreadsheet you have two criteria Name and Date so, the formula meets both and returns the correct result. How is it that the formula is not working?

See attached file

What is your point? You're the one who provides the requirements. If 11/13/13 date doesn't exists in your table, the formula will not retrieve the desired result. If you your criteria has two parameters then the formula should meet them not to ignore. Secondly, just because you call it "data range" it doesn't make it so.

hi darkhangelsk. try this array formula:
=INDEX(B3:B11,MIN(IF(A3:A11=G8,IF(C3:C11<=G9,IF(D3:D11>=G9,ROW(B3:B11)))))-ROW(B3)+1)

@AlKey:
your logical tests of (G9>=C3:C11)*(G9<=D3:D11) shows up correctly as this:
{1;0;0;0;0;0;0;0;0}
so by right, you ought to grab the 1st cell where it 13 Nov 2013 is within C3 & D3. by doing IF & MATCH, you are simply saying do the MATCH only when the above shows 1. since the 1st one is 1, it does an approximate match (omitting the 0 is using approximate match) of finding Carlo & it's supposed to give you the last instance & the unsorted list doesn't help. even if you use exact MATCH with 0, it will still give you problems because the names might appear more than once & you are just ignoring your logical tests & return the 1st MATCH.

Thanks benishiryo and darkhangelsk. Glad your were able to find the right solution. Sorry couldn't get back there earlier, had to attend New Year celebration party

Happy New Year to all.