Google Sheets ARRAYFORMULA to VLOOKUP last result before a certain date

Question

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.

Answer 1

Try this formula:

=ARRAYFORMULA(VLOOKUP(indirect(F2)+indirect(G2)/100000, sort({indirect(B2)+indirect(C2)/100000, indirect(D2)}, 1, 1), 2, true))

Note also that the INDIRECT ranges refer to fields calculated on the spreadsheet (refer image below).

Count

• =count(B6:B200) in Cell B4
• copy this to cells C4, D4, F4 & G4

Column

• =column() in Cell C3
• copy this to cells C3, D3, F3 & G3

Address

• =address(6,B3,4,true)&":"&address(B4+6-1,B3,4,true) in Cell B2
• copy this to cells C2, D2, F2 & G2

---

Sample data