Fórmula genérica
=XLOOKUP(max,dates,results,,-1) // latest match by date
Resumen
Para obtener la última coincidencia en un conjunto de datos por fecha, puede usar XLOOKUP en el modo de coincidencia aproximada configurando match_mode en -1. En el ejemplo que se muestra, la fórmula en G5, copiada, es:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
donde la fecha (C5: C15), el artículo (B5: B15) y el precio (D5: D15) son rangos de nombres.
Explicación
XLOOKUP ofrece varias características que lo hacen excepcionalmente bueno para búsquedas más complicadas. En este ejemplo, queremos el último precio de un artículo por fecha. Si los datos estuvieran ordenados por fecha en orden ascendente, esto sería muy sencillo. Sin embargo, en este caso, los datos no están clasificados.
De forma predeterminada, XLOOKUP devolverá la primera coincidencia en un conjunto de datos. Para obtener la última coincidencia, podemos establecer el argumento opcional search_mode en -1 para hacer que XLOOKUP busque "del último al primero". Sin embargo, no podemos utilizar este enfoque aquí porque no hay garantía de que el último precio de un artículo aparezca al final.
En su lugar, podemos establecer el argumento opcional match_mode en -1 para forzar una coincidencia aproximada de "exacta o la siguiente más pequeña", y ajustar el valor de búsqueda y la matriz de búsqueda como se explica a continuación. La fórmula en G5, copiada, es:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Trabajando con los argumentos uno por uno, lookup_value es la fecha más grande (más reciente) en los datos:
MAX(date) // get max date value
El lookup_array se deriva con una expresión lógica booleana:
(item=F5)*date
Al comparar cada elemento con el valor en F5, "Cinturón", obtenemos una matriz de valores VERDADERO / FALSO:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
donde los valores TRUE representan entradas para "Belt". Esta matriz actúa como un filtro. Cuando se multiplica por los valores en el rango de fechas nombrado , los valores VERDADERO / FALSO se evalúan a unos y ceros:
=(1;0;0;0;0;0;1;0;1;0;0)*date
El resultado es una matriz que contiene solo ceros y fechas para cinturones:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Nota: los números de serie son fechas válidas de Excel.
Esta matriz se envía directamente a XLOOKUP como argumento lookup_array.
Return_array es el precio del rango con nombre (D5: D15)
No se proporciona el argumento opcional not_found.
Match_mode se establece en -1, para una coincidencia exacta o el siguiente elemento más pequeño.
XLOOKUP busca en la matriz de búsqueda el valor de fecha máximo. Dado que la matriz ya se ha filtrado para excluir fechas no asociadas con "Belt", XLOOKUP simplemente encuentra la mejor coincidencia (ya sea la fecha exacta o la siguiente fecha más pequeña) que corresponde a la última fecha.
El resultado final es el precio asociado a la última fecha. La fórmula seguirá funcionando cuando los datos se clasifiquen en cualquier orden.
