Data lookup based on multiple conditions without using CSE

[JHE1969]

I have a data table of dates, first names, surnames and salary. I need to be able to lookup the salary on or before a given date. I need to do this multiple times so do not want to use the Control Shift Enter array construct. Any suggestions for a single line formula that can deal with the data conditionality. An example table is below:

[TABLE="width: 348"]
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]Date[/TD]
[TD="class: xl65, width: 87"]FirstName[/TD]
[TD="class: xl65, width: 87"]Surname[/TD]
[TD="class: xl65, width: 87"]Salary[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/01/2018[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]102[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/02/2018[/TD]
[TD="class: xl65"]Jack[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]98[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/03/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]52[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/04/2018[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]69[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/05/2018[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/06/2018[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/06/2018[/TD]
[TD="class: xl65"]Tony[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]54[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/06/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]270[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/07/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/07/2018[/TD]
[TD="class: xl65"]Jack[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]120[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/08/2018[/TD]
[TD="class: xl65"]Jill[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]78[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/09/2018[/TD]
[TD="class: xl65"]Tony[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65, align: right"]83[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]01/10/2018[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Date[/TD]
[TD="class: xl66, align: right"]01/09/2018[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]FirstName[/TD]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Surname[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl67"]Salary[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]

 

[JHE1969]

Massively helpful, I've been cracking my head all day on that. Thanks so much.

 

[shg]

You're welcome.