¿Te has encontrado alguna vez buscando datos específicos en una montaña de hojas de cálculo de Excel? La función VLOOKUP es una herramienta poderosa, pero cuando se trata de gestionar múltiples coincidencias y criterios, puede convertirse en un verdadero rompecabezas. ¡No te preocupes! En este artículo, exploraremos las estrategias más efectivas para aprovechar al máximo VLOOKUP y obtener esas valiosas coincidencias que necesitas. Descubre cómo simplificar tu trabajo, ahorrar tiempo y optimizar tus análisis de datos con consejos prácticos y ejemplos claros. ¡Sumérgete en el fascinante mundo de las funciones avanzadas de Excel y transforma tu manera de trabajar con datos!
Si eres usuario de Excel, sabrás lo útil que puede ser la función VLOOKUP para encontrar coincidencias en un conjunto de datos. Pero, ¿qué sucede cuando necesitas buscar múltiples coincidencias con uno o más criterios? En este artículo, te enseñaremos cómo utilizar la función VLOOKUP para encontrar múltiples coincidencias en Excel y obtener así información más precisa y completa. Descubre cómo expandir el potencial de esta popular función y saca el máximo provecho de tus datos en Excel. ¡No te lo pierdas!
Debajo del nombre, seleccione una cantidad de celdas vacías que sea igual o mayor que la cantidad máxima de coincidencias posibles, ingrese una de las siguientes fórmulas de matriz en la barra de fórmulas y presione Ctrl + Mayús + Intro para completarla (en este caso, podrás editar la fórmula solo en todo el rango donde se ingresa). O puede ingresar la fórmula en la primera celda, presionar Ctrl + Mayús + Introy luego arrastre la fórmula hacia algunas celdas más (en este caso, podrá editar la fórmula en cada celda individualmente). =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,»»), ROW()-2)),»»)
o
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,»»), ROW()-2)),»»)
Como ves, el 1calle La fórmula es un poco más compacta, pero los 2Dakota del Norte uno es más universal y requiere menos modificaciones (profundizaremos un poco más en la sintaxis y la lógica).
Copie la fórmula a otras columnas. Para esto, seleccione el rango de celdas donde acaba de ingresar la fórmula y arrastre el controlador de relleno (un pequeño cuadrado en la esquina inferior derecha del rango seleccionado) hacia la derecha.El resultado será algo similar a esto:
>Cómo funciona esta fórmula
Este es un ejemplo de usos intermedios a avanzados de Excel que implica conocimientos básicos de fórmulas matriciales y funciones de Excel. Trabajando de adentro hacia afuera, esto es lo que debe hacer:
función SI En el centro de la fórmula, se utiliza la función SI para obtener las posiciones de todas las apariciones del valor de búsqueda en el rango de búsqueda: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13 )-2,»»)
IF compara el valor de búsqueda (D2) con cada valor en el rango de búsqueda (A3:A13) y, si se encuentra la coincidencia, devuelve la posición relativa de la fila; una cadena vacía («») en caso contrario.
Las posiciones relativas de las filas se calculan restando 2 de FILA($B$3:$B$13) para que la primera fila tenga la posición 1. Si su rango de retorno comienza en la fila 2, entonces reste 1, y así sucesivamente. El resultado de esta operación es la matriz {1;2;3;4;5;6;7;8;9;10;11}, que va al argumento value_if_true de la función IF.
En lugar del cálculo anterior, puede utilizar esta expresión: FILA(columna_búsqueda)- MIN(ROW(columna_búsqueda))+1, que devuelve el mismo resultado pero no requiere ningún cambio independientemente de la ubicación de la columna de retorno. En este ejemplo, sería FILA($A$3:$A$13)- MIN(FILA($A$3:$A$13))+1.
Entonces, en este punto tiene una matriz que consta de números (posiciones de coincidencias) y cadenas vacías (no coincidencias). Para la celda D3 en este ejemplo, tenemos la siguiente matriz:
> Si verifica los datos de origen, verá que «Adam» (valor de búsqueda en D2) aparece en el 3tercero8th y 10th posiciones en el rango de búsqueda (A3:A13).
función PEQUEÑA
A continuación, la función SMALL(array, k) interviene para determinar cuál de las coincidencias debe devolverse en una celda específica. Con formación ya establecido, trabajemos en el k argumento, es decir, el k-ésimo valor más pequeño que se devolverá. Para esto, crea una especie de «contador incremental» FILA()-n, donde «n» es el número de fila de la primera celda de fórmula menos 1. En este ejemplo, ingresamos la fórmula en las celdas D3:D7, por lo que FILA()-2 devuelve «1» para la celda D3 (fila 3 menos 2), «2» para la celda D4 (fila 4 menos 2), etc.
Como resultado, la función PEQUEÑA extrae el 1calle elemento más pequeño de la matriz en la celda D3, el segundo elemento más pequeño en la celda D4, y así sucesivamente. Y esto transforma la larga y compleja fórmula inicial en una muy simple, como esta:
>
Consejo. Para ver el valor calculado detrás de una determinada parte de la fórmula, seleccione esa parte en la barra de fórmulas y presione F9.
Esta parte es fácil. Utilice la función ÍNDICE para devolver el valor de un elemento de matriz en función de su número de fila.
SI ERROR función Y finalmente, envuelve la fórmula en la función SIERROR para manejar posibles errores, que son inevitables porque no puede saber cuántas coincidencias se devolverán para tal o cual valor de búsqueda y, por lo tanto, copia la fórmula en un número de celdas igual o mayor que el número de coincidencias posibles. Para no asustar a sus usuarios con un montón de errores, simplemente reemplácelos con una cadena vacía (celda en blanco).
Nota. Tenga en cuenta el uso adecuado de referencias de celda absolutas y relativas en la fórmula. Todas las referencias son fijas excepto la referencia de columna relativa en el valor de búsqueda (D$2), que debe cambiar según la posición relativa de una columna donde se copia la fórmula para devolver coincidencias para otros valores de búsqueda.
Al juntar todo esto, obtenemos las siguientes fórmulas genéricas para buscar múltiples valores en Excel:
Fórmula 1:
SI ERROR (ÍNDICE (rango_retornoPEQUEÑO(SI(valor de búsqueda = rango_de_buscadaFILA(rango_retorno )- metro ,»»), FILA() – norte )),»»)
Fórmula 2:
SI ERROR (ÍNDICE (rango_retornoPEQUEÑO(SI(valor de búsqueda = rango_de_buscada FILA(rango_de_buscada) – MIN(FILA(rango_de_buscada ))+1,»»), FILA() – norte)),»»)
Dónde:
- metro es el número de fila de la primera celda en el rango de retorno menos 1.
- norte es el número de fila de la primera celda de fórmula menos 1.
Nota. En el ejemplo anterior, ambos norte y metro son iguales a «2» porque nuestro rango de retorno y nuestro rango de fórmula comienzan en la fila 3. En sus hojas de trabajo, estos pueden ser números diferentes.
Fórmula 2. Busque varias coincidencias y devuelva resultados seguidos
En caso de que desee devolver varios valores en filas en lugar de columnas, cambie las fórmulas anteriores de esta manera:
=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,»»), COLUMN()-4)),»»)
O
=IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,»»),COLUMN()-4)), «»)
Como en el ejemplo anterior, ambas son fórmulas matriciales, así que recuerda presionar la tecla Ctrl + Mayús + Intro atajo para completarlos correctamente.
>Las fórmulas funcionan con la misma lógica que en el ejemplo anterior, excepto que se utiliza la función COLUMNA en lugar de FILA para determinar qué valor coincidente se debe devolver en una celda específica: COLUMNA()-n. Donde n es el número de columna de la primera celda donde se ingresa la fórmula menos 1. En este ejemplo, la fórmula se ingresa en las celdas E2:H2. siendo E el 5th columna, norte es igual a «4» (5-1=4).
Nota. Para que la fórmula se copie correctamente en otras filas, tenga en cuenta las referencias del valor de búsqueda, la columna absoluta y la fila relativa, como $D3.
Para concluir, aquí están las fórmulas genéricas para Vlookup con múltiples resultados devueltos en filas:
Fórmula 1:
SI ERROR (ÍNDICE (rango_retornoPEQUEÑO(SI(valor de búsqueda = rango_de_buscadaFILA(rango_retorno) – metro«»), COLUMNA() – norte)), «»)
Fórmula 2:
SI ERROR (ÍNDICE (rango_retornoPEQUEÑO(SI(valor de búsqueda = rango_de_buscadaFILA(rango_de_buscada) – MIN(FILA( rango_de_buscada))+1,»»),COLUMNA() – norte)), «»)
Dónde:
- metro es el número de fila de la primera celda en el rango de retorno menos 1.
- norte es el número de columna de la primera celda de fórmula menos 1.
Fórmula 3. Vlookup múltiples coincidencias basadas en múltiples condiciones
Ya sabe cómo buscar en V varios valores en Excel basándose en una condición. Pero, ¿qué sucede si desea obtener varias coincidencias según dos o más criterios? Llevando los ejemplos anteriores más allá, ¿qué pasa si tienes un adicional? Mes columna y desea obtener una lista de todos los productos vendidos por un vendedor determinado en un mes específico?
Si está familiarizado con las fórmulas de matrices, puede recordar que permiten el uso de asteriscos.
como operador AND. Por lo tanto, puede tomar las fórmulas analizadas en los dos ejemplos anteriores y hacer que verifiquen múltiples condiciones como se muestra a continuación.
Devolver múltiples coincidencias en una columnaSI ERROR (ÍNDICE (rango_retornoPEQUEÑO(SI(1=((–(valor_buscado1=rango_buscado1)) * ( –(valor_buscado2=rango_búsqueda2))), FILA(rango_retorno)-metro,»»), FILA()-norte
)),»»)
- Dónde: metro
- es el número de fila de la primera celda en el rango de retorno menos 1. norte
es el número de fila de la primera celda de fórmula menos 1.Asumiendo la lista de vendedores (rango_buscada1) está en A3:A30, la lista de meses (rango_búsqueda2) está en B3:B30, el vendedor de interés (valor_buscado1) está en la celda E3, y el mes de interés (valor_buscado2
=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((–($E$3=$A$3:$A$30)) * (–($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,»»), ROW()-2)),»»)
) está en la celda F3, la fórmula toma la siguiente forma:
>Vlookup con múltiples criterios que devuelven múltiples coincidencias en una columna
Devolver varios resultados seguidos
Si desea extraer varios valores en función de varios conjuntos de criterios, es posible que prefiera el diseño horizontal donde los resultados se devuelven en filas. En este caso, utilice la siguiente fórmula genérica:SI ERROR (ÍNDICE (rango_retornoPEQUEÑO(SI(1 = ((–(valor_buscado1=rango_buscado1)) * (–( valor_buscado2 =rango_búsqueda2))), FILA(rango_retorno ) –metro «»), COLUMNA() –norte
)),»»)
- Dónde: metro
- es el número de fila de la primera celda en el rango de retorno menos 1. norte
es el número de columna de la primera celda de fórmula menos 1.
=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((–($E3=$A$3:$A$30)) * (–($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,»»), COLUMN()-6)),»»)
Para nuestro conjunto de datos de muestra, la fórmula es la siguiente:
>Vlookup con múltiples criterios que devuelven múltiples coincidencias en filas
De manera similar, puedes realizar múltiples búsquedas en V con tres, cuatro o más condiciones.
Cómo funcionan estas fórmulas Básicamente, las fórmulas para buscar múltiples valores con múltiples condiciones funcionan con la lógica ya familiar, explicada en el mismoprimer ejemplo
. La única diferencia es que la función SI ahora prueba múltiples condiciones:1=((–(valor_buscado1=rango_buscada1))*(–(valor_buscado2=rango_búsqueda2
))*…)
El resultado de cada comparación lookup_value=lookup_range es una matriz de valores lógicos VERDADERO (se cumple la condición) y FALSO (no se cumple la condición). El operador unario doble (–) convierte los valores lógicos en unos y ceros. Y debido a que multiplicar por cero siempre da cero, en la matriz resultante, tienes 1 solo para aquellos elementos que cumplen todas las condiciones especificadas. Ahora, simplemente compara la matriz final con el número 1 para que la función FILA devuelva los números de filas que cumplen todas las condiciones; de lo contrario, una cadena vacía.Una palabra de precaución
. Todas las múltiples fórmulas de Vlookup analizadas en este tutorial son fórmulas de matriz. Como tal, cada fórmula recorre en iteración todos los elementos de las matrices cada vez que se cambian los datos de origen o se vuelve a calcular la hoja de cálculo. En hojas de cálculo grandes que contienen cientos o miles de filas, esto puede ralentizar significativamente su Excel.
Si necesita obtener coincidencias de varias hojas, utilice esta guía: Cómo realizar BUSCARV en varias hojas.
Cómo realizar una búsqueda en V para devolver múltiples valores en una celda
Seré sincero: no conozco una manera fácil de buscar y devolver múltiples coincidencias en una sola venta con fórmulas. Sin embargo, conozco una forma sin fórmulas (léase «sin estrés» 🙂 de hacer esto mediante el uso de dos complementos incluidos con nuestra Ultimate Suite para Excel. Los pasos detallados siguen a continuación.
Datos de origen y resultado esperado.
>Datos de origen y resultado esperado.
Extraer filas con múltiples coincidencias a la tabla principal
En su tabla principal, ingrese una lista de nombres únicos en la primera columna, meses en la segunda columna y organícelos como se muestra en la captura de pantalla a continuación. Después de eso, realice los siguientes pasos: Seleccione su tabla principal o haga clic en cualquier celda dentro de ella y luego haga clic en el Fusionar dos tablas
>Botón Combinar dos tablas en la cinta de Excel El complemento es lo suficientemente inteligente como para identificar y seleccionar la tabla completa, por lo que simplemente haga clicPróximo
>Seleccione la tabla principal. Consejo. Al utilizar la herramienta por primera vez, es lógico seleccionar la Crear una copia de seguridad de la hoja de trabajo
>
>
>
>
>
>
>
>
>
>
>
>
Perich: ¡Totalmente de acuerdo! Este artículo me salvó la vida, porque siempre me costaba un montón hacer VLOOKUP con múltiples coincidencias. Recuerdo una vez que tenía que armar un informe de gastos y estuve casi todo el día buscando datos, hasta que encontré esta guía. Desde entonces, todo es mucho más fácil, ¡gracias por compartir estos trucos!
RakyValkRakS: ¡Qué genial que te haya servido! A mí me pasó algo similar cuando intenté usar VLOOKUP para conciliar inventarios. Pasaba horas buscando, pero con tus consejos ahora puedo hacerlo en minutos. Definitivamente necesito más trucos como este, ¡gracias!
Galeas: ¡Me alegra que compartan sus experiencias! A mí también me pasó algo parecido, estaba batallando con un VLOOKUP que parecía eterno, pero seguí los pasos de este artículo y ahora puedo manejar múltiples coincidencias sin problema. ¡Es un cambio brutal! Gracias por el contenido, espero ver más consejos así.
Flegmistyyy: ¡Me encantó el artículo! Nunca había logrado hacer correctamente un VLOOKUP con múltiples coincidencias hasta que seguí tus pasos. La otra vez, estaba buscando datos de ventas y perdía tiempo buscando manualmente. Gracias a tus tips, ahora lo hago en un abrir y cerrar de ojos. ¡Sigue compartiendo este tipo de contenido!