EXCEL: BUSCARX más reciente por fecha

Para obtener la última coincidencia en un conjunto de datos por fecha, puede usar BUSCARX en modo de coincidencia aproximada estableciendo match_mode en -1. En el ejemplo que se muestra, la fórmula en G5, copiada, es:

=BUSCARX(MAX(date),(item=F5)*date,price,,-1)

donde la fecha (C5: C15), el artículo (B5: B15) y el precio (D5: D15) se denominan rangos.

Cómo funciona esta fórmula

BUSCARX 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 se ordenaran por fecha en orden ascendente, esto sería muy sencillo. Sin embargo, en este caso, los datos no están ordenados.

Por defecto, BUSCARX devolverá la primera coincidencia en un conjunto de datos. Para obtener la última coincidencia, podemos establecer el argumento opcional search_mode, en -1 para que BUSCARX busque "last to first". Sin embargo, no podemos usar este enfoque aquí porque no hay garantía de que el último precio para un artículo aparezca en último lugar.

En su lugar, podemos establecer el argumento opcional match_mode en -1 para forzar una coincidencia aproximada de "exacto o el siguiente más pequeño", 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:

=BUSCARX(MAX(date),(item=F5)*date,price,,-1)

Trabajando a través de argumentos uno por uno, el valor de búsqueda es la fecha más grande (más reciente) en los datos:

MAX(date) // get max date value

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:

{VERDADERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO;FALSO}

donde los valores VERDADERO representan entradas para "Belt". Esta matriz actúa como un filtro. Cuando se multiplica por los valores en la fecha del rango con nombre, los valores VERDADERO / FALSE se evalúan en 1 y 0:

={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 entrega directamente a BUSCARX como el argumento lookup_array.

Return_array es el precio de rango con nombre (D5: D15)

No se proporciona el argumento opcional not_found.

Match_mode se establece en -1, para la coincidencia exacta o el siguiente elemento más pequeño.

BUSCARX busca en la matriz de búsqueda el valor máximo de la fecha. Dado que la matriz ya se ha filtrado para excluir fechas no asociadas con "Belt", BUSCARX 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 con la última fecha. La fórmula continuará funcionando cuando los datos se ordenen en cualquier orden.