Encuentre el último valor que no esté en blanco a más tardar en la fecha indicada, y rápidamente

Encuentre el último valor que no esté en blanco a más tardar en la fecha indicada, y rápidamente

Realmente estoy luchando por encontrar una solución a este problema aparentemente simple. Incluso pedí la ayuda de ChatGPT, pero ninguna de sus soluciones funcionó. ¿Quizás puedas demostrar que eres más inteligente que una máquina (por ahora)?

Estoy buscando hojas de trabajo vecinas y libros de trabajo externos que a veces tienen decenas de miles de filas, por lo que descubro que necesito algo más rápido que LOOKUPo VLOOKUP, o las cosas se detienen. Sin embargo, estoy usando LibreOffice 7.4, por lo que las fórmulas de matriz dinámica y algunas funciones más nuevas de Excel como XLOOKUP, FILTERy SORTno son compatibles.

La columna A contiene una lista de fechas en orden ascendente o descendente. La columna B contiene números no ordenados y celdas en blanco.

A B
05/01/23 1234.10
05/02/23 134,25
05/05/23 5123.45
05/08/23
05/10/23 0.00

Se necesita una fórmula que no requiera un uso intensivo de CPU para encontrar el último valor que no esté en blanco en la columna B (incluidos los ceros) donde el valor de búsqueda es una fecha no posterior a la de la celda $Z1. Probé variaciones de LOOKUP(2,1/, INDEX MATCH, VLOOKUPpero obtengo todo ceros, un error o el primer o último valor en la columna B, ignorando la fecha.

¿Algunas ideas? Estoy empezando a preguntarme si hay algún problema con LibreOffice. No sería la primera vez que tengo que reinstalarlo para que empiece a comportarse solo.

EDITAR: Una lista de fórmulas que he probado hasta ahora:

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

Devuelve un valor de 0. Creo que está buscando la celda $B$9998, en lugar de la última celda que no está en blanco, que es $B$124.

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

Devuelve una cadena vacía. He usado una versión diferente de esto antes:

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

Funciona, excepto que no admite celdas con un valor de 0.

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

De alguna manera, éste devuelve el tercer valor en la columna B, no el último, independientemente de la fecha en la celda $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)}

Este mira la celda $B$9998 y devuelve un valor de 0, en lugar de mirar la última celda que no está en blanco.

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

Este devuelve un valor de 0.

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

Este devuelve un valor de 0.

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

Este devuelve un valor de 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)}

Sorprendentemente, este devuelve el penúltimo valor de la columna B.

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

Estedevuelve el valor de la última celda que no está en blancoen la columna B, pero no pude encontrar la manera de hacer que tenga en cuenta la fecha en $Z1. (Como probablemente podrás ver, estaba muy cansado mientras hacía esto).

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

Éste mira la celda $B$9998 y devuelve un valor de 0.

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

Este siempre devuelve la última celda que no está en blanco en la columna B, independientemente de la fecha en la celda $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)

Después de instalar elExtensión Lox365 de goosepirate, Probé esta fórmula usando XLOOKUP. Produjo uncódigo de error Err:504. En ese momento me di por vencido y me fui a la cama.

Aquí hay una muestra de datos que he estado usando:

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

Respuesta1

La fórmula con la que comencé fue:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/$'Sheet 1'.B$2:B$10000,$'Sheet 1'.B$2:B$10000)

Pude obtener el resultado que necesitaba agregando un filtro "no en blanco" como este:
=LOOKUP(2,1/($A2>='Sheet 1'.$A$2:$A$10000)/($'Sheet 1'.B$2:B$10000<>""),$'Sheet 1'.B$2:B$10000)

información relacionada