¿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:
-
-
Título: Búsquedas varios resultados (693 clics)
Tamaño: 14 KB
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.
13 comentarios en “Cómo hacer que una búsqueda devuelva varios resultados”
Estimado Sergio;
el dia menos mensado me inscirbire en uno de tus cursos.
muy buenos tus detalles que envias y me sorprende ver todo lo que sabes y compartes.
mil saludos
Hola buen día,
Disculpa si mi tabla es dinámica también los detecta?? Gracias por el apoyo.
Hola Emmanuel!
En principio sí debería funcionar pero debes asegurarte de que al señalar los rangos de la tabla dinámica, se introduzca únicamente el rango (no la función IMPORTARDATOSDINAMICOS, como está configurado en Excel de forma predeterminada).
Espero que te ayude!
Sergio, buenos días, estoy intentando saber de una tabla que maneja fechas y montos, saber si tengo pagos devueltos el mismo día o el día siguiente, utilice la función SI(Y, y me funciona pero solo comparando entre dos valores, realmente no se trabajar con matrices.
Esta el la formula que coloco =SI(Y($A$2=A4;$D$2=-D4);”DEVUELTO HOY”;SI(Y($A$2A4;$D$2=-D4);”DEVUELTO AL OTRO DÍA”;””)), porque me interesa saber si con la misma fecha se presenta en la otra columna el monto igual pero negativo, agradeciendo de antemano tu colaboración para esta neofita
Fulvia, para poder darte una respuesta necesitaría ver el archivo. Te sugiero que te registres en el foro de Ayuda Excel y plantées allí tu consulta. Enseguida te responderemos.
Tus explicaciones son muy claras y concisas. Muchas gracias.
Muchas gracias!!
Hola, buenas noches Sergio, tengo una pregunta: como puedo usar una formula para sacar datos de una base de datos, por ejemplo: tengo dos hojas (una que se llama libro diario y otra que se llama cuentas Ts). En el libro diario se manejan los asientos contables, se ponen las cuentas en una columna y en otras se dos columnas mas se pone las cantidades que representa tanto el debe como el haber, en la otra hoja hay pequeñas tablas que representan las cuentas: una celda combinada con el nombre de la cuenta y dos columnas una del debe y otra del haber. Aquí viene mi pregunta: como puedo pasar los datos de cada cuenta a su respectiva cuenta T con sus datos en cantidades? Espero pronta ayuda, saludos,
Hola tocayo!
Si utilizas Excel 365, puedes buscar información sobre la función FILTRAR. Ésta se encarga de extraer y mostrar el rango de datos cuando uno de los campos coincide con un criterio dado. En tu caso el criterio podría ser “Debe” o “Haber” o cuando existan números negativos o positivos.
Buen día. Estoy haciendo la busqueda pero tengo 2 inquietudes: ¿Se puede hacer de la misma manera en Google Sheets? ¿PUede hacerse de tal manera que presente los datos de manera horizontal y no vertical como aparece en tu ejemplo? Gracias.
Hola Jorge,
Muchas de las tareas de Excel se pueden realizar con Google Sheets, y las búsquedas es una de ellas.
Para hacer que devuelva los datos de forma horizontal, utiliza la función TRANSPONER.
Eres un Master, solo una cosa que yo en lo personal desconocia, las llaves en la formula, modifique para aumentar el rango de la formula y perdi esas llaves y la formula no funcionaba correctamente. comentario por si a alguien le pasaba lo mismo lol.
Hola Homar!
Esas llaves indican que se trata de una matriz.
Si la matriz se encuentra en el interior de una fórmula, puedes escribir las llaves a mano, pero si la matriz cubre toda la fórmula debes introducirla con Ctrl + Alt + Intro. Las llaves aparecen solas.
Los comentarios están cerrados.