Devolver varios valores coincidentes según múltiples criterios en Excel (Guía completa)
Los usuarios de Excel suelen enfrentarse a escenarios en los que deben extraer varios valores que cumplan simultáneamente distintos criterios, y mostrar todos los resultados coincidentes en una columna, una fila o consolidados en una sola celda. Esta guía explora métodos compatibles con todas las versiones de Excel, así como la función FILTRO disponible en Excel 365 y 2021.
Devolver varios valores coincidentes según múltiples criterios en una sola celda
En Excel, extraer varios valores coincidentes según múltiples criterios dentro de una sola celda es un desafío habitual. A continuación, se presentan dos métodos eficientes para lograrlo.
Método 1: Uso de la función TEXTO.UNIR (Excel 365 / 2021,2019)
Para obtener todos los valores coincidentes en una sola celda con delimitadores, la función TEXTO.UNIR le será de gran ayuda.
Introduzca o copie la siguiente fórmula en una celda vacía y pulse Intro (Excel 2021 y Excel 365) o Ctrl + Mayús + Intro en Excel 2019 para obtener el resultado:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) comprueba si cada fila cumple ambas condiciones: «vendedor igual a E2» y «mes igual a F2». Cuando se cumplen las dos, el resultado es 1; en caso contrario, es 0. El asterisco * indica que ambas condiciones deben ser verdaderas.
- SI(..., $C$2:$C$21, «») devuelve el nombre del producto si la fila coincide; de lo contrario, devuelve una celda vacía.
- TEXTO.UNIR(", ", VERDADERO, ...) combina todos los nombres de producto no vacíos en una sola celda, separados por «, ».
Método 2: Uso de Kutools para Excel
Kutools para Excel ofrece una solución potente y sencilla que le permite recuperar y combinar rápidamente varias coincidencias en una sola celda según múltiples criterios, sin necesidad de fórmulas complejas.
Tras instalar Kutools para Excel, proceda del siguiente modo:
- Seleccione el rango de datos del que desea obtener todos los valores que coincidan con los criterios.
- A continuación, haga clic en Kutools > Combinar y dividir > Combinar filas avanzado, consulte la captura de pantalla:
- En el cuadro de diálogo Combinar filas avanzado, configure las siguientes opciones:
- Seleccione las cabeceras de columna que contengan sus criterios de coincidencia (por ejemplo, Vendedor y Mes). Para cada columna seleccionada, haga clic en **Clave principal** para establecerlas como condiciones de búsqueda.
- Haga clic en la cabecera de la columna donde desee los resultados combinados (por ejemplo, Producto). En la sección Combinar, elija el delimitador que prefiera (por ejemplo, coma, espacio o un separador personalizado).
- Por último, haga clic en el botón Aceptar.
Resultado: Kutools combinará al instante todos los valores coincidentes en una sola celda para cada combinación única de criterios.
Devolver varios valores coincidentes según múltiples criterios en una columna
Cuando necesite extraer y mostrar varios registros coincidentes de un conjunto de datos según múltiples condiciones, devolviendo los resultados en formato de columna vertical, Excel le ofrece soluciones potentes y eficaces.
Método 1: Uso de una fórmula matricial (para todas las versiones)
Puede usar la siguiente fórmula matricial para devolver los resultados verticalmente en una columna:
1. Copie o introduzca la siguiente fórmula en una celda vacía:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Pulse Ctrl + Mayús + Intro para obtener el primer resultado coincidente, seleccione la primera celda con la fórmula y arrastre el controlador de relleno hacia abajo hasta que aparezca una celda vacía. Así, todos los valores coincidentes se mostrarán tal como se ilustra en la siguiente captura de pantalla:
- $A$2:$A$18=$E$2: Comprueba si el vendedor coincide con el valor de la celda E2.
- $B$2:$B$18=$F$2: Comprueba si el mes coincide con el valor de la celda F2.
- * es un operador lógico Y (ambas condiciones deben ser verdaderas).
- FILA($C$2:$C$18)-FILA($C$2)+1: Genera un número de fila relativo para cada producto.
- K.ESIMO.MENOR(..., FILA(1:1)): Obtiene la n-ésima fila coincidente más pequeña (a medida que arrastras la fórmula hacia abajo).
- INDICE(...): Devuelve el valor de la fila coincidente.
- SI.ERROR(..., «»): Devuelve una celda vacía cuando ya no hay más coincidencias.
Método 2: Uso de la función FILTRO (Excel 365 / 2021)
Si utiliza Excel 365 o Excel 2021, la función FILTRO es una opción excelente para obtener varios resultados según múltiples criterios, gracias a su simplicidad, claridad y capacidad para mostrar dinámicamente los resultados sin necesidad de fórmulas matriciales complejas.
Copie o introduzca la siguiente fórmula en una celda vacía y, a continuación, pulse la tecla Entrar para obtener todos los registros que coincidan con los múltiples criterios.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTRO(...) devuelve todos los valores del rango C2:C18 que cumplen ambas condiciones.
- (A2:A18=E2)*(B2:B18=F2): matriz lógica que verifica la coincidencia del vendedor y el mes.
- «Sin coincidencia»: Mensaje opcional que se muestra si no se encuentran valores.
Devolver varios valores coincidentes según múltiples criterios en una fila
Los usuarios de Excel suelen necesitar extraer varios valores de un conjunto de datos que cumplan distintas condiciones y mostrarlos horizontalmente (en una fila). Esta estrategia resulta especialmente útil para crear informes dinámicos, paneles o tablas resumen cuando el espacio vertical es limitado. En esta sección, exploraremos dos métodos potentes.
Método 1: Uso de una fórmula matricial (para todas las versiones)
Las fórmulas matriciales tradicionales permiten extraer varios valores coincidentes mediante las funciones ÍNDICE, K.ESIMO.MENOR, SI y COLUMNA. A diferencia de la extracción vertical (basada en columnas), adaptamos la fórmula para que devuelva los resultados en una fila.
1. Copie o introduzca la siguiente fórmula en una celda vacía:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Pulse las teclas Ctrl + Mayús + Entrar para obtener el primer resultado coincidente; luego, seleccione la primera celda con la fórmula y arrástrela hacia la derecha a través de las columnas para recuperar todos los resultados.
- $A$2:$A$18=$E$2: Comprueba si el vendedor coincide.
- $B$2:$B$18=$F$2: Comprueba si el mes coincide.
- *: Operador lógico Y: ambas condiciones deben cumplirse.
- FILA($C$2:$C$18)-FILA($C$2)+1: Genera números de fila relativos para establecer el número de filas.
- COLUMNA(A1): Ajusta qué coincidencia devolver en función de hasta dónde hayas arrastrado la fórmula hacia la derecha.
- SI.ERROR(...): Evita errores cuando se agoten las coincidencias.
Método 2: Uso de la función FILTRO (Excel 365 / 2021)
Copie o introduzca la siguiente fórmula en una celda vacía y, a continuación, pulse la tecla Entrar: se extraerán todos los valores coincidentes y se colocarán en una fila. Vea la captura de pantalla:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTRO(...): Recupera los valores coincidentes de la columna C según las dos condiciones.
- (A2:A18=E2)*(B2:B18=F2): Ambas condiciones deben cumplirse.
- TRANSPONER(...): Transforma la matriz vertical devuelta por FILTRO en una matriz horizontal.
🔚 Conclusión
La recuperación de varios valores coincidentes según múltiples criterios en Excel puede lograrse de diversas formas, dependiendo de cómo desee mostrar los resultados: en una columna, en una fila o dentro de una única celda.
- Para usuarios de Excel 365 o Excel 2021, la función FILTRO ofrece una solución moderna, dinámica y elegante que reduce al mínimo la complejidad.
- Para quienes utilizan versiones anteriores, las fórmulas matriciales siguen siendo herramientas potentes, aunque requieren un poco más de configuración y atención.
- Además, si desea consolidar los resultados en una sola celda o prefiere una solución sin código, la función TEXTO.UNIR o herramientas de terceros como Kutools para Excel pueden simplificar el proceso de forma notable.
Elija el método que mejor se adapte a su versión de Excel y al diseño que prefiera, y estará perfectamente preparado para realizar búsquedas con múltiples criterios de forma eficiente y precisa. Si desea descubrir más consejos y trucos de Excel, nuestra página web ofrece miles de tutoriales para ayudarle a dominar Excel.
Más artículos relacionados:
- Devolver varios Rango de valor de búsqueda en una celda separada por comas
- En Excel, podemos usar la función BUSCARV para obtener el primer valor coincidente de un rango de celdas, pero a veces necesitamos extraer todos los valores coincidentes y separarlos con un delimitador específico, como una coma o un guion, en una sola celda, tal como se muestra en la siguiente captura de pantalla. ¿Cómo podríamos obtener y devolver varios valores de búsqueda en una única celda, separados por comas, en Excel?
- BuscarV y devolver varios valores coincidentes a la vez en hoja de Google
- La función BUSCARV normal en la hoja de Google le permite encontrar y devolver el primer valor coincidente según unos datos dados. Sin embargo, a veces puede necesitar buscar y devolver todos los valores coincidentes, tal como se muestra en la siguiente captura de pantalla. ¿Dispone de algún método sencillo y eficaz para resolver esta tarea en la hoja de Google?
- BuscarV y devolver varios valores desde una lista desplegable
- En Excel, ¿cómo podría buscar y devolver varios valores correspondientes desde una lista desplegable, de modo que al seleccionar un elemento de dicha lista se muestren inmediatamente todos sus valores relacionados, tal como se muestra en la siguiente captura de pantalla? En este artículo, explicaré paso a paso la solución.
- BuscarV y devolver varios valores verticalmente en Excel
- Normalmente, puede utilizar la función BUSCARV para obtener el primer valor coincidente, pero a veces necesita devolver todos los registros que cumplen un criterio específico. En este artículo, le explicaré cómo buscar y devolver todos los valores coincidentes de forma vertical, horizontal o en una sola celda.
- BuscarV y devolver datos coincidentes entre dos valores en Excel
- En Excel, podemos usar la función BUSCARV estándar para obtener el valor correspondiente a partir de unos datos dados. Pero a veces necesitamos buscar y devolver el valor coincidente que se encuentra entre dos valores, como se muestra en la siguiente captura de pantalla. ¿Cómo se puede realizar esta tarea en Excel?
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos