BUSCARV para el ultimo valor

En este artículo vemos como modificar el BUSCARV para que nos encuentre el último valor, el primero por orden cronológico, el mayor o el menor.

Limitaciones del BUSCARV

Aunque tengo un artículo completo acerca de las limitaciones del BUSCARV, la limitación que me interesa el día de hoy es cuando hay varias instancias del valor buscado.

Por ejemplo estamos buscando a un cliente, y ese cliente aparece varias veces en nuestra base de búsqueda. ¿En este caso que me trae BUSCARV

Por defecto BUSCARV me trae el primer valor que encuentre, y aunque muchas veces va a ser suficiente, sobre todo si estamos seguros de que el cliente solo aparece una vez en la base, hay veces que queremos modificar un poco nuestra fórmula.

Entre otras cosas que podríamos querer están estas:

• Que me traiga el último valor
• Que me traiga el segundo o el tercer valor
  • Que me traiga el penúltimo valor
• Que me traiga el mayor valor
• Que me traiga el menor valor
• Que me traiga el último valor en orden cronológico (asumiendo que hay una columna de fecha)
• Que me traiga el primer valor en orden cronológico (asumiendo que hay una columna de fecha)

Vamos a ver cada uno de estos casos, siempre usando BUSCARV. También se puede con QUERY, con FILTER, con INDICE y otros métodos, pero hoy me interesa probar los límites del BUSCARV

Jugando con la base de búsqueda

Para la mayoría de casos que vamos a ver hoy, la clave está en “jugar” con la base de búsqueda, es decir el segundo argumento del BUSCARV

Debemos tener en cuenta que aquí no solo puedo poner un rango, sino arreglos, fórmulas y mucho más. Entonces yo puedo ordenar, filtrar y mucho más los rangos que quiera, dentro de este argumento 2, antes de realizar la búsqueda

Para entenderlo mejor vayamos viendo los ejemplos, no en orden de utilidad, sino en orden de complejidad de menor a mayor

Trayendo el primer valor cronológico

Aquí debemos asumir que hay una columna de fecha, para poder determinar cuál instancia se dio primero en el tiempo. Aquí esta mi ejemplo

Quiero entonces traer la primera venta del cliente que yo escoja

Si hacemos el BUSCARV común y corriente quedaría así

=BUSCARV(F2;B:C;2;0)

Lo primero que se me ocurre que podemos hacer para no “enredarnos” con la fórmula, es ordenar nuestra base. Si la ordenamos por fecha de menor a mayor, BUSCARV buscará el primer valor que encuentre que será en este caso el primer valor cronológico.

Pero no siempre vamos a poder hacer esto, y aquí es donde podemos ordenar dentro de nuestra misma fórmula

Vamos a tomar nuestra base y la ordenamos con la función SORT

SORT recibe varios argumentos dependiendo de como la usemos. Es este caso vamos a usar 3 argumentos: 1) la tabla que vamos a ordenar, 2) la columna por la que vamos a ordenar, y 3) el método de ordenación (1 para ascendente, 0 para descendente)

Veamos como quedaría nuestra base ordenada por fecha

=SORT(A2:C9;2;1)

Ahora haríamos nuestro BUSCARV sobre esta nueva base

Pero como no queremos tener múltiples bases “auxiliares”, entonces la idea es incluir esta ordenación dentro de nuestro BUSCARV

Quedaría así nuestro BUSCARV

=BUSCARV(F2;SORT(A2:C9;2;1);3;0)

Para dejar nuestra fórmula más robusta podemos, primero fijar las columnas de nuestra base así

=BUSCARV(F2;SORT($A$2:$C$9;2;1);3;0)

Por último podemos dejar abierta la última fila del arreglo para que funcione sin importar que agreguemos más las a nuestra base. Quedaría así

=BUSCARV(F2;SORT($A$2:$C;2;1);3;0)

Trayendo el último valor cronológico

Esta es muy fácil, ya que partiendo de la fórmula anterior, simplemente cambiamos el tercer argumento del SORT que determina si el orden es ascendente (1) o descendente (0).

Así quedaría:

=BUSCARV(F2;SORT($A$2:$C;2;0);3;0)

Trayendo el valor máximo

Hay varias maneras de hacer esto

Podríamos por ejemplo replicar la última fórmula de ordenar, pero ya no ordenaríamos por fecha (Columna B o 2), sino por Valor (Columna C o 3) de mayor a menor, con eso nos aseguramos que el primer valor encontrado siempre será el más grande. Quedaría así:

=BUSCARV(F2;SORT($A$2:$C;3;0);3;0)

Trayendo el valor mínimo

Sería lo mismo que la anterior, pero de nuevo cambiamos el tipo de ordenación, así:

=BUSCARV(F2;SORT($A$2:$C;3;1);3;0)

Trayendo el último valor

Esta es un poco más difícil, porque no tenemos como ordenarlo.

Una opción es crear una nueva columna de números consecutivos, y ordenar por esta

Así se vería:

Después recurriríamos a la misma fórmula que hemos usado, pero ampliando nuestra tabla para incluir la última columna, y ordenando por esta (la 4)

=BUSCARV(G2;SORT($A$2:$D;4;0);3;0)

Pero como la idea durante todo el ejercicio ha sido no tener que modificar nuestra base original, ni añadiendo columnas, ni filtrando, ni ordenando, entonces veamos de que manera podríamos incluir esta columna consecutiva dentro de nuestra fórmula

Para esto vamos a usar la poco usada pero extremadamente útil función SEQUENCE

Esta lo podemos modificar para que nos traiga la secuencia de números que queramos

Como en este caso solo queremos una lista simple consecutiva, entonces solo debemos decirle cuantos números queremos

En nuestro caso tenemos 8 datos, entonces la función quedaría así

=SEQUENCE(8)

De que otra manera podemos llegar a este 8 que no sea de manera fija. Que tal si contamos nuestros datos desde la fila 2, así:

=SEQUENCE(CONTARA(A2:A))

Lo bueno de dejar la fórmula así, es que si agregamos nuevos datos, se va a actualizar

Acuérdense que queremos ordenar, entonces la pregunta es, como agregamos esta columna “sin agregarla”, es decir dentro de nuestra fórmula SORT como hemos venido haciéndolo

Aquí viene el tema de los arreglos o la notación ={}

Con estos “brackets” podemos hacer maravillas: reordenar filas y columnas, y agregar nuevas filas o columnas.

Por ahora, importante saber que para agregar filas usamos “;” y para columnas usamos \ (en Estados Unidos, Inglaterra o México se usa “,” en vez de “\” para las columnas)

Entonces “creemos” nuestra tabla virtual, pero hagamoslo en una celda para que lo vean en acción

Primero voy a crear una copia exacta de mi tabla con los arreglos

Ahora voy a sumarle la columna de secuencia

={A2:C11\SEQUENCE(CONTARA(A2:A11))}

Ahora vamos a incluir esta tabla creada en nuestro ordenar, y vamos a ordenar de mayor a menor, por la fila 4

=sort({A2:C11\SEQUENCE(CONTARA(A2:A11))};4;0)

Y por último, incluimos esta tabla dentro de nuestro BUSCARV

=BUSCARV(F2;sort({A2:C11\SEQUENCE(CONTARA(A2:A11))};4;0);3;0)

Lo interesante es que podemos borrar todas las tablas que hemos creado menos la original, y dejar todo “escondido” dentro de nuestro BUSCARV.

Conclusión

Hoy vimos como extender las funcionalidades de BUSCARV. Particularmente nos concentramos en el segundo argumento, en la base de búsqueda.

Vimos como podemos “optimizar” o “potencializar” nuestro BUSCARV solo agregando formulas en este segundo argumento

Para esto nos apoyamos en formulas como CONTARA, SORT, SEQUENCE y los arreglos “{}”

Fíjense que estos métodos sirven para buscar los extremos: el mayor, el menor, el primero, el último, pero es un poco más difícil para buscar valores intermedios, el segundo, el tercero, el penúltimo. Ya hemos visto métodos con INDICE que se adecuan mejor a estos casos