# Google Sheets ARRAYFORMULA to VLOOKUP last result before a certain date

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
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.

• Matt, do you think that there is a flaw in your lookup processing. Whatever date you enter, you are expecting the formula to return the value relating to the closest previous date. You enter a lookup date of "Cask#1-1 Feb 2021" expecting to find the last entry for January - but what if there was actually a transaction on 1 Feb? Jun 8, 2021 at 5:49

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(6,B3,4,true)&":"&address(B4+6-1,B3,4,true)` in Cell B2