Devolver varios valores coincidentes basados en múltiples criterios en Excel (Guía completa)
Los usuarios de Excel a menudo encuentran escenarios donde es necesario extraer múltiples valores que satisfagan varios criterios simultáneamente y presentar todos los resultados coincidentes en una columna, fila o consolidados dentro de una sola celda. Esta guía explora métodos para todas las versiones de Excel, así como la nueva función FILTRAR disponible en Excel 365 y 2021.
Devolver varios valores coincidentes basados en múltiples criterios en una sola celda
En Excel, extraer varios valores coincidentes basados en múltiples criterios dentro de una sola celda es un desafío común. Aquí se exploran dos métodos eficientes.
Método 1: Usando la función TEXTJOIN (Excel 365 / 2021, 2019)
Para obtener todos los valores coincidentes en una sola celda con delimitadores, la función TEXTJOIN puede ayudarte.
Escribe o copia la siguiente fórmula en una celda en blanco, luego presiona la tecla Enter (Excel 2021 y Excel 365) o Ctrl + Shift + Enter 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) verifica si cada fila cumple ambas condiciones: “Vendedor igual a E2” y “Mes igual a F2”. Si ambas condiciones se cumplen, el resultado es 1; de lo contrario, es 0. El asterisco * significa que ambas condiciones deben ser verdaderas.
- SI(..., $C$2:$C$21, "") devuelve el nombre del producto si la fila coincide; de lo contrario, devuelve un espacio en blanco.
- TEXTJOIN(", ", VERDADERO, ...) combina todos los nombres de productos no vacíos en una celda, separados por ", ".
Método 2: Usando Kutools para Excel
Kutools para Excel ofrece una solución poderosa y simple, permitiéndote recuperar y combinar rápidamente varias coincidencias en una sola celda basada en múltiples criterios sin fórmulas complejas.
Después de instalar Kutools para Excel, sigue estos pasos:
- Selecciona el rango de datos que deseas utilizar para obtener todos los valores correspondientes según los criterios.
- Luego, haz clic en Kutools > Combinar y Dividir > Combinar Filas Avanzado, ver captura de pantalla:
- En el cuadro de diálogo Combinar Filas Avanzadas, configura las siguientes opciones:
- Elige los encabezados de columna que contienen tus criterios de coincidencia (por ejemplo, Vendedor y Mes). Para cada columna seleccionada, haz clic en Clave Principal para definirla como tus condiciones de búsqueda.
- Haz clic en el encabezado de columna donde deseas los resultados combinados (por ejemplo, Producto). Desde la sección Combinar, selecciona tu delimitador preferido (por ejemplo, coma, espacio o separador personalizado).
- Por último, haz clic en el botón Aceptar.
Resultado: Kutools fusionará instantáneamente todos los valores coincidentes en una sola celda por cada combinación única de criterios.
Devolver varios valores coincidentes basados en múltiples criterios en una columna
Cuando necesites extraer y mostrar múltiples registros coincidentes de un conjunto de datos basados en varias condiciones, devolviendo los resultados en formato de columna vertical, Excel ofrece varias soluciones poderosas.
Método 1: Usando una fórmula matricial (para todas las versiones)
Puedes usar la siguiente fórmula matricial para devolver resultados verticalmente en una columna:
1. Copia o ingresa la siguiente fórmula en una celda en blanco:
=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. Presiona Ctrl + Shift + Enter para obtener el primer resultado coincidente, y luego selecciona la primera celda de fórmula y arrastra el controlador de relleno hacia abajo hasta que aparezca una celda en blanco, ahora se devuelven todos los valores coincidentes como se muestra en la captura de pantalla:
- $A$2:$A$18=$E$2: Verifica si el Vendedor coincide con el valor en la celda E2.
- $B$2:$B$18=$F$2: Verifica si el Mes coincide con el valor en 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.
- PEQUEÑO(..., FILA(1:1)): Obtiene el n-ésimo valor más pequeño de las filas coincidentes (a medida que se arrastra la fórmula hacia abajo).
- INDICE(...): Devuelve el producto de la fila coincidente.
- SI.ERROR(..., ""): Devuelve una celda vacía si no hay más coincidencias.
Método 2: Usando la función FILTRAR (Excel 365 / 2021)
Si estás usando Excel 365 o Excel 2021, la función FILTRAR es una excelente opción para devolver múltiples resultados basados en múltiples criterios, gracias a su simplicidad, claridad y capacidad para derramar dinámicamente resultados sin fórmulas matriciales complejas.
Copia o ingresa la siguiente fórmula en una celda en blanco, luego presiona la tecla Enter, y todos los registros coincidentes se devuelven basados en los múltiples criterios.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTRAR(...) devuelve todos los valores de C2:C18 donde se cumplen ambas condiciones.
- (A2:A18=E2)*(B2:B18=F2): Matriz lógica que verifica al vendedor y mes coincidentes.
- "No hay coincidencia": Mensaje opcional si no se encuentran valores.
Devolver varios valores coincidentes basados en múltiples criterios en una fila
Los usuarios de Excel a menudo necesitan extraer múltiples valores de un conjunto de datos que cumplan con varias condiciones y mostrarlos horizontalmente (en una fila). Esto es útil para crear informes dinámicos, tableros o tablas resumen donde el espacio vertical es limitado. En esta sección, exploraremos dos métodos poderosos.
Método 1: Usando una fórmula matricial (para todas las versiones)
Las fórmulas matriciales tradicionales permiten extraer múltiples valores coincidentes usando funciones ÍNDICE, PEQUEÑO, SI y COLUMNA. A diferencia de la extracción vertical (basada en columnas), ajustamos la fórmula para devolver resultados en una fila.
1. Copia o ingresa la siguiente fórmula en una celda en blanco:
=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. Presiona Ctrl + Shift + Enter para obtener el primer resultado coincidente, y luego selecciona la primera celda de fórmula y arrastra la fórmula hacia la derecha a través de las columnas para recuperar todos los resultados.
- $A$2:$A$18=$E$2: Verifica si el Vendedor coincide.
- $B$2:$B$18=$F$2: Verifica si el Mes coincide.
- *: Lógica Y - ambas condiciones deben ser verdaderas.
- FILA($C$2:$C$18)-FILA($C$2)+1: Crea números de fila relativos.
- COLUMNA(A1): Ajusta qué coincidencia devolver, dependiendo de qué tan lejos se haya arrastrado la fórmula hacia la derecha.
- SI.ERROR(...): Evita errores cuando se agotan las coincidencias.
Método 2: Usando la función FILTRAR (Excel 365 / 2021)
Copia o ingresa la siguiente fórmula en una celda en blanco, luego presiona la tecla Enter, y todos los valores coincidentes se extraen y se colocan en una fila. Ver captura de pantalla:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTRAR(...): Recupera valores coincidentes de la columna C basados en las dos condiciones.
- (A2:A18=E2)*(B2:B18=F2): Ambas condiciones deben ser verdaderas.
- TRANSPONER(...): Convierte la matriz vertical devuelta por FILTRAR en una matriz horizontal.
🔚 Conclusión
Recuperar múltiples valores coincidentes basados en múltiples criterios en Excel se puede lograr de varias maneras, dependiendo de cómo quieras mostrar los resultados, ya sea en una columna, una fila o dentro de una sola celda.
- Para usuarios con Excel 365 o Excel 2021, la función FILTRAR ofrece una solución moderna, dinámica y elegante que minimiza la complejidad.
- Para aquellos que usan versiones anteriores, las fórmulas matriciales siguen siendo herramientas poderosas, aunque requieren un poco más de configuración y cuidado.
- Además, si quieres consolidar resultados en una sola celda o prefieres una solución sin código, la función TEXTJOIN o herramientas de terceros como Kutools para Excel pueden simplificar significativamente el proceso.
Elige el método que mejor se adapte a tu versión de Excel y a tu diseño preferido, y estarás bien equipado para manejar búsquedas multicriterio de manera eficiente y precisa. Si te interesa explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales para ayudarte a dominar Excel.
Más artículos relacionados:
- Devolver múltiples valores de búsqueda en una celda separada por comas
- En Excel, podemos aplicar la función BUSCARV para devolver el primer valor coincidente de una tabla de celdas, pero, a veces, necesitamos extraer todos los valores coincidentes y luego separarlos por un delimitador específico, como una coma, guion, etc., en una sola celda como se muestra en la siguiente captura de pantalla. ¿Cómo podríamos obtener y devolver múltiples valores de búsqueda en una celda separada por comas en Excel?
- BuscarV y devolver múltiples valores coincidentes a la vez en Google Sheets
- La función normal BUSCARV en Google Sheets puede ayudarte a encontrar y devolver el primer valor coincidente basado en un dato dado. Pero, a veces, puedes necesitar buscar y devolver todos los valores coincidentes como se muestra en la siguiente captura de pantalla. ¿Tienes alguna buena y fácil forma de resolver esta tarea en Google Sheets?
- BuscarV y devolver múltiples valores desde una lista desplegable
- En Excel, ¿cómo podrías buscar y devolver múltiples valores correspondientes desde una lista desplegable, lo que significa que cuando eliges un elemento de la lista desplegable, todos sus valores relativos se muestran a la vez como se muestra en la siguiente captura de pantalla. En este artículo, te presentaré la solución paso a paso.
- BuscarV y devolver múltiples valores verticalmente en Excel
- Normalmente, puedes usar la función BUSCARV para obtener el primer valor correspondiente, pero, a veces, quieres devolver todos los registros coincidentes basados en un criterio específico. En este artículo, hablaré sobre cómo buscar y devolver todos los valores coincidentes verticalmente, horizontalmente o en una sola celda.
- BuscarV y devolver datos coincidentes entre dos valores en Excel
- En Excel, podemos aplicar la función normal BUSCARV para obtener el valor correspondiente basado en un dato dado. Pero, a veces, queremos buscar y devolver el valor coincidente entre dos valores como se muestra en la siguiente captura de pantalla, ¿cómo podrías manejar esta tarea en Excel?
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos