¿Quién no ha buscado algo en un libro de Excel? Puedes buscar de muchas formas: con el cuadro de diálogo Buscar y reemplazar o mediante fórmulas más o menos complejas. Hoy te muestro un truco avanzado que utilizo cuando el valor que quiero buscar tiene varias coincidencias o resultados.
BUSCARV es la mejor opción cuando quieres buscar valores únicos o cuando quieres obtener el primer resultado de una tabla, pero… ¿y si quieres obtener la segunda o tercera coincidencia? ¿o las tres primeras?
Excel contará en su próxima versión (Excel 2022 ó 2023, no sé) con un puñado de funciones matriciales que van a facilitar enormemente este tipo de búsquedas. Mientras tanto, te muestro mi forma de proceder.
Cómo hacer búsquedas que devuelvan varios resultados
Como he comentado, no he encontrado la forma de utilizar BUSCARV así que he agrupado un puñado de funciones en una fórmula matricial:
- SI: Evalúa una condición y devuelve un resultado si se cumple dicha condición y otro valor si no se cumple.
- K.ESIMO.MENOR: Devuelve el enésimo valor más pequeño de una matriz.
- INDICE: devuelve un valor o un referencia de la celda en la intersección de una fila y columna en particular, en un rango especificado.
- FILA: Devuelve un número de fila.
- SI.ERROR: Si encuentra un error en una fórmula, devuelve un valor determinado.
Descárgate el ejemplo para practicar:
Supongamos que tienes una tabla que contiene marcas y modelos de vehículos. Las marcas se ubican en la columna A y los modelos en la B. Tu objetivo es el de obtener una lista de los modelos de cada marca
Estos son los pasos que yo sigo:
- Introduce en la celda D2 la marca de vehículo de la que deseas obtener los modelos como ves en la imagen.
- En la celda D3 introduce la siguiente fórmula sin olvidarte de pulsar Ctrl + Mayús + Intro para introducirla, porque se trata de una fórmula matricial.
=SI.ERROR(INDICE($B$3:$B$68;K.ESIMO.MENOR(SI(D$2=$A$3:$A$68;FILA($B$3:$B$68)-MIN(FILA($A$3:$A$68))+1;»»);FILA()-2));»»)
- Arrastra la fórmula hacia abajo. Si quieres buscar los modelos de otras marcas, no tienes más que introducirlas a partir de E2 hacia la derecha y arrastrar la fórmula también hacia la derecha.
¿Cómo funciona la fórmula?
Para evaluar una fórmula correctamente, debes comenzar a hacerlo desde el centro hacia afuera. Por lo menos yo lo hago así.
Antes de comenzar, debes estar familiarizado con el funcionamiento de las fórmulas matriciales.
La función SI
En el corazón de la fórmula puedes ver la función SI, que se utiliza para obtener las posiciones de todas las ocurrencias del valor buscado:
SI(D$2=$A$3:$A$68;FILA($B$3:$B$68)-MIN(FILA($A$3:$A$68))+1;»»)
SI compara el valor de búsqueda (D2) con el valor de cada celda en el rango A3:A68 y si éste coincide, devuelve la posición relativa de la fila. En caso contrario devuelve un «vacío».
Las posiciones relativas se calculan restando el número de filas que no corresponden al rango a la posición relativa en el rango del valor encontrado. En este ejemplo el rango comienza en la fila 3, entonces la posición relativa sería 3-3+1 y daría como resultado 1.
Para que veas el resultado parcial de la función SI, selecciónala con todos sus argumentos y pulsa F9.
Si te fijas, únicamente aparecen los valores de las posiciones relativas al valor que se está buscando.
La función K.ESIMO.MENOR
El siguiente paso, K.ESIMO.MENOR(matriz;k), se utiliza para determinar qué valor de las coincidencias anteriores, debe mostrarse en cada celda específica.
Como el argumento matriz ya lo conocemos, me voy a centrar en k, que es el «enésimo valor más pequeño». Conociendo esto, puedes ordenar los valores FILA()-n, donde n es el número de la fila donde se encuentra la fórmula menos 1. En este ejemplo tengo fórmulas en el rango D3:D20, así que en la celda D3, esta parte de la fórmula será FILA()-2, que es igual a 1 (3 filas – 2). En la celda D4 los cálculos de esa parte de la fórmula serán 4 – 2, que es igual a 2… y así sucesivamente hasta llegar a la fila 20.
En definitiva, la función K.ESIMO.MENOR se encarga de ir devolviendo de forma ordenada los valores más pequeños de la matriz.
Si avanzamos en el análisis de la fórmula, en este momento nos quedaría así en la celda D3:
=SI.ERROR(INDICE($B$3:$B$68;{1});»»)
Truco: para ver el resultado de una parte de la fórmula, en este tutorial se muestra cómo hacerlo.
La función INDICE
La función INDICE devuelve el valor de una matriz que se encuentra en cierta posición. En la fórmula anterior, se mostraría el valor de la primera celda del rango B3:B68.
La función SI.ERROR
Esta parte de la fórmula es la que se encarga de ocultar los POSIBLES ERRORES errores cuando se producen (no se puede prever el número de modelos de vehículo por cada marca). Si se omitiera, el rango de fórmulas con el que estamos trabajando se mostraría así:
Las comillas al final indican que cuando se produce un errors se debe mostrar una cadena vacía.
A tener en cuenta: Presta una atención especial al tipo de referencias que he utilizado en la fórmula. Todas son absolutas excepto la referencia de la condición a evaluar, ya que la fórmula será arrastrada hacia la derecha.
Resumen
Hoy te he mostrado un truco para hacer una búsqueda avanzada en la que existe un número indeterminado de resultados. La fórmula creada los muestra todos.
Si no estás familiarizado con fórmulas matriciales o te resultan difíciles de comprender, estoy preparando un artículo en el que te mostraré otras dos formas de hacer búsquedas que devuelven varios resultados.
