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:
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>
</tbody>
| Date | FirstName | Surname | Salary |
| 01/01/2018 | John | Smith | 102 |
| 01/02/2018 | Jack | Jones | 98 |
| 01/03/2018 | Jill | Smith | 52 |
| 01/04/2018 | Tom | Jones | 69 |
| 01/05/2018 | Tom | Smith | 12 |
| 01/06/2018 | John | Smith | 103 |
| 01/06/2018 | Tony | Jones | 54 |
| 01/06/2018 | Jill | Smith | 270 |
| 01/07/2018 | Jill | Jones | 103 |
| 01/07/2018 | Jack | Jones | 120 |
| 01/08/2018 | Jill | Jones | 78 |
| 01/09/2018 | Tony | Smith | 83 |
| 01/10/2018 | Tom | Jones | 47 |
| Date | 01/09/2018 | ||
| FirstName | Tom | ||
| Surname | Smith | ||
| Salary |
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>
</tbody>
