0

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.

2
  • 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.
    – Erik Tyler
    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?
    – Tedinoz
    Jun 8, 2021 at 5:49

1 Answer 1

0

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

Snapshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.