I have a Google Sheets workbook which logs changes to casks, and I need it to be able to look up a value at a given date - i.e. the last change before the lookup date.
The data sheet is laid out similarly to this:
A B C
Cask No Op. Date Volume
1 1 Jan 21 80
1 2 Jan 21 140
1 12 Feb 21 180
2 2 Jan 21 120
2 1 Jan 21 90
2 2 Feb 21 200
Likewise, the lookup would be similar
A B C
Cask No Lookup Date Volume
1 1 Feb 21
2 3 Mar 21
And what I want is for column C in the second sheet to show 140 for cask 1 and 200 for cask 2, as those were the last recorded entries before that date (and thus correct for the date that they were looked up.
My partial solution is to use:
=ARRAYFORMULA(VLOOKUP(A2:A+B2:B/100000, sort({datasheet!A2:A+datasheet!B2:B/100000, datasheet!C2:C}, 1, 1), 2, 1))
Which means that the VLOOKUP is searching an array that looks like:
A B
Cask No Volume
1.44041 80
1.44042 140
1.44072 180
2.44042 120
2.44072 90
But that's returning the first result in the table, rather than the most recent.
I'm fairly sure that I'm close, but with so many logical turns I'm clearly missing something.