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

• If you will share a link to a sample spreadsheet containing two sheets with the exact data shown in your post, I will add a solution to that sheet. This is the most efficient method of sharing a solution, because factors like your international locale and actual data types in your sheet will determine formula approach. Just be sure, if you do decide to share such a link, that you set the link's Share permission (when creating the link) to "Anyone with the link..." and "Editor." One array formula can handle this task. Please share the link as a comment or alert me via comment. May 13, 2021 at 4:05
• 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