Return value from table using match and in-between criteria

[keelaboosa]

Employee	Rate	Effective Date
Brandon	$15.00	1/1/17
Anthony	$12.50	1/1/17
James	$17.00	1/1/17
Josh	$16.50	1/1/17
Brandon	$16.50	3/15/17
Brandon	$17.50	5/1/17
James	$16.50	6/10/17

<tbody>
</tbody>

I've built the table above to record pay rates and effective dates for a pool of employees. I'm looking for a formula to return the correct pay rate when I enter an employee name and date. For instance, if I enter Brandon and 2/1/17, I should get $15.00. If I enter Brandon and 3/16/17, I should get $16.50.

This one's got my head spinning!

 

[gaz_chops]

Try

<b15,1))))[ code]
=INDEX($B$2:$B$8,MATCH(1,IF($A$2:$A$8=A15,IF($C$2:$C$8 < B15,1))))

A15 & B15 are the Name & Date to search for

Enter formula as an Array, Ctrl Shift & Enter

[TABLE="width: 322"]
<tbody>[TR]
[TD="class: xl66, width: 87"]Employee[/TD]
[TD="class: xl66, width: 87"]Rate[/TD]
[TD="class: xl66, width: 148"]Effective Date[/TD]
[/TR]
[TR]
[TD="class: xl63"]Brandon[/TD]
[TD="class: xl63"]$15.00[/TD]
[TD="class: xl64, align: right"]01/01/2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]Anthony[/TD]
[TD="class: xl63"]$12.50[/TD]
[TD="class: xl64, align: right"]01/01/2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]James[/TD]
[TD="class: xl63"]$17.00[/TD]
[TD="class: xl64, align: right"]01/01/2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]Josh[/TD]
[TD="class: xl63"]$16.50[/TD]
[TD="class: xl64, align: right"]01/01/2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]Brandon[/TD]
[TD="class: xl63"]$16.50[/TD]
[TD="class: xl64, align: right"]15/03/2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]Brandon[/TD]
[TD="class: xl63"]$17.50[/TD]
[TD="class: xl64, align: right"]01/05/2017[/TD]
[/TR]
[TR]
[TD="class: xl63"]James[/TD]
[TD="class: xl63"]$16.50[/TD]
[TD="class: xl64, align: right"]10/06/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brandon[/TD]
[TD="class: xl65, align: right"]16/03/2017[/TD]
[TD="class: xl67, align: right"]$16.50[/TD]
[/TR]
[TR]
[TD]Brandon[/TD]
[TD="class: xl65, align: right"]01/02/2017[/TD]
[TD="class: xl67, align: right"]$15.00[/TD]
[/TR]
</tbody>[/TABLE]

</b15,1))))[>

 

[keelaboosa]

Beauty! That did it, thanks so much.