Find latest non-blank value no later than given date - and quickly

Question

I'm really struggling to find a solution to this deceptively simple problem. I've even enlisted ChatGPT's help, but none of its solutions have worked. Maybe you can prove you're smarter than a machine (for now)?

I'm searching neighbouring worksheets and external workbooks with sometimes tens of thousands of rows, so I'm finding I need something faster than LOOKUP or VLOOKUP, or things grind to a halt. However, I'm using LibreOffice 7.4, so dynamic array formulae and some newer Excel functions such as XLOOKUP, FILTER, and SORT aren't supported.

Column A contains a list of dates in either ascending or descending order. Column B contains non-sorted numbers and blank cells.

A	B
01/05/23	1234.10
02/05/23	134.25
05/05/23	5123.45
08/05/23
10/05/23	0.00

A need a non-CPU-intensive formula to find the latest non-blank value in column B (including zeroes) where the lookup value is a date no later than the one in cell $Z1. I've tried variations on LOOKUP(2,1/, INDEX MATCH, VLOOKUP, but either I get all zeroes, an error, or the first or last value in column B, ignoring the date.

Any ideas? I'm starting to wonder if there's something wrong with LibreOffice. Wouldn't be the first time I've had to reinstall it to get it to start behaving itself.

EDIT: A list of formulae I've tried so far:

{=INDEX($B$2:$B$9999,MAX(IF($A$2:$A$9999<=$Z1,ROW($A$2:$A$9999)-MIN(ROW($A$2:$A$9999))+1)))}

It returns a value of 0. I think it's finding cell $B$9998, rather than the last non-blank cell which is $B$124.

=LOOKUP(2,1/($A$2:$A$9999<=$Z1),$B$2:$B$9999)

It returns an empty string. I've used a different version of this before:

=LOOKUP(2,1/($Z1>=$A$2:$A$9999)/$B$2:$B$9999,$B$2:$B$9999)

It works, except it doesn't support cells with a value of 0.

{=MAX(IF($A$2:$A$9999<=$Z1, $B$2:$B$9999, ""))}

Somehow, this one returns the third value in column B, not the last - regardless of the date in cell $Z1.

{=OFFSET($'Savings chart'.$B$2,MAX(IF($'Savings chart'.$A$2:$A$9999<=$A38,ROW($'Savings chart'.$A$2:$A$9999)-ROW($A$2)+1,0))-1,0)}

This one looks at cell $B$9998 and returns a value of 0, rather than looking at the last non-blank cell.

{=INDIRECT("'Sheet 2'.B"&SUMPRODUCT(MAX(('Sheet 2'.A:A<=$Z1)*ROW('Sheet 2'.A:A))))}

This one returns a value of 0.

{=INDEX('Sheet 2'.B:B, MAX(IF('Sheet 2'.A:A<=$Z1, ROW('Sheet 2'.A:A))))}

This one returns a value of 0.

{=INDEX($B$2:$B$9999,MAX(IF($A2:$A$9999<=$Z1,ROW($B$2:$B$9999)-MIN(ROW($B$2:$B$9999))+1)))}

This one returns a value of 0.

{=OFFSET('Savings chart'.$B$1, MAX(IF('Savings chart'.$B$2:$B$9999<>"", ROW('Savings chart'.$B$2:$B$9999)-ROW('Savings chart'.$B$2)+1, 0))-1, 0)}

Somewhat surprisingly, this one returns the last-but-one value in column B.

{=INDEX($B$2:$B$9999, MAX(IF($B$2:$B$9999<>"", ROW($B$2:$B$9999)-ROW($B$2)+1, 0)))}

This returns the value of the last non-blank cell in column B, but I couldn't then figure out how to get it to take account of the date in $Z1. (As you can probably tell, I was very tired while doing this.)

=INDEX($B$2:$B$9999, MATCH(2, 1/($A$2:$A$9999<=$Z1), 1))

This one looks at cell $B$9998 and returns a value of 0.

=IF($A5>=$'Savings chart'.$A$2:$A$9999,LOOKUP(9.999999E+306,$'Savings chart'.$B$2:$B$9999),0)

This one always returns the last non-blank cell in column B, regardless of the date in cell $Z1.

=XLOOKUP($'Savings chart'.$A$2:$B$9999,($A2>=$'Savings chart'.$A$2:$A$9999)*($'Savings chart'.$B$2:$B$9999<>""),$'Savings chart'.$B$2:$B$9999)

After installing the Lox365 extension by goosepirate, I tried this formula using XLOOKUP. It produced an error code Err:504. At this point, I gave up and went to bed.

Here is a sample of data I've been using:

Date    Data
1 Jan 23    490.44
1 Jan 23    
1 Jan 23    
2 Jan 23    
3 Jan 23    
4 Jan 23    
4 Jan 23    
5 Jan 23    
6 Jan 23    
7 Jan 23    
8 Jan 23    
9 Jan 23    
11 Jan 23   
11 Jan 23   
11 Jan 23   
11 Jan 23   
11 Jan 23   
12 Jan 23   
12 Jan 23   
13 Jan 23   
14 Jan 23   
16 Jan 23   
16 Jan 23   
17 Jan 23   
18 Jan 23   
18 Jan 23   
19 Jan 23   
20 Jan 23   493.44
21 Jan 23   
21 Jan 23   
22 Jan 23   
22 Jan 23   
23 Jan 23   499.44
24 Jan 23   
24 Jan 23   
24 Jan 23   
24 Jan 23   
25 Jan 23   484.81
25 Jan 23   
25 Jan 23   
25 Jan 23   
25 Jan 23   
25 Jan 23   
26 Jan 23   
26 Jan 23   
26 Jan 23   
27 Jan 23   
27 Jan 23   
27 Jan 23   
28 Jan 23   
29 Jan 23   
31 Jan 23   
31 Jan 23   
1 Feb 23    
1 Feb 23    
1 Feb 23    
3 Feb 23    
3 Feb 23    
4 Feb 23    
5 Feb 23    
5 Feb 23    
5 Feb 23    
6 Feb 23    
6 Feb 23    
6 Feb 23    
6 Feb 23    
6 Feb 23    
7 Feb 23    
7 Feb 23    
7 Feb 23    
7 Feb 23    
8 Feb 23    
8 Feb 23    
9 Feb 23    
9 Feb 23    
9 Feb 23    
10 Feb 23   
10 Feb 23   
10 Feb 23   
10 Feb 23   
10 Feb 23   
10 Feb 23   
10 Feb 23   
10 Feb 23   
11 Feb 23   
11 Feb 23   
11 Feb 23   
11 Feb 23   
11 Feb 23   
12 Feb 23   
12 Feb 23   
12 Feb 23   
13 Feb 23   
13 Feb 23   
13 Feb 23   
13 Feb 23   
14 Feb 23   
15 Feb 23   
15 Feb 23   
16 Feb 23   
16 Feb 23   
17 Feb 23   
17 Feb 23   
17 Feb 23   
17 Feb 23   
17 Feb 23   
17 Feb 23   
17 Feb 23   
18 Feb 23   
18 Feb 23   
19 Feb 23   
20 Feb 23   487.81
20 Feb 23   
20 Feb 23   
20 Feb 23   
21 Feb 23   
21 Feb 23   
22 Feb 23   
22 Feb 23   
22 Feb 23   
22 Feb 23   
23 Feb 23   493.81
24 Feb 23   477.81

Answer 1

The formula I started with was:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/$'Sheet 1'.B$2:B$10000,$'Sheet 1'.B$2:B$10000)

I was able to get the result I needed by adding a "not blank" filter like so:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/($'Sheet 1'.B$2:B$10000<>""),$'Sheet 1'.B$2:B$10000)