Obtener múltiples valores coincidentes basados en varios criterios en Excel (Guía completa)
Los usuarios de Excel a menudo se encuentran con escenarios donde es necesario extraer múltiples valores que satisfagan varios criterios simultáneamente y presentar todos los resultados coincidentes en una columna, una 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.
Obtener múltiples valores coincidentes basados en varios criterios en una sola celda
En Excel, extraer múltiples valores coincidentes basados en varios criterios dentro de una sola celda es un desafío común. Aquí exploramos 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.
Ingresa 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 pero simple, permitiéndote recuperar y combinar rápidamente múltiples coincidencias en una sola celda basada en varios criterios sin fórmulas complejas.
Después de instalar Kutools para Excel, haz lo siguiente:
- Selecciona el rango de datos del que deseas 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 Avanzado, 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 definirlas 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 u otro 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.
Obtener múltiples valores coincidentes basados en varios criterios en una columna
Cuando necesitas extraer y mostrar múltiples registros coincidentes de un conjunto de datos basado 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 la fórmula y arrastra el controlador de relleno hacia abajo hasta que aparezca una celda en blanco. Ahora, todos los valores coincidentes son devueltos como se muestra en la siguiente 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 menor número de fila coincidente (a medida que se arrastra la fórmula hacia abajo).
- INDICE(...): Devuelve el producto de la fila coincidente.
- SI.ERROR(..., ""): Devuelve una celda en blanco 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 varios 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, todos los registros coincidentes son devueltos 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 ambas condiciones se cumplen.
- (A2:A18=E2)*(B2:B18=F2): Matriz lógica que verifica el vendedor y el mes coincidentes.
- "No hay coincidencia": Mensaje opcional si no se encuentran valores.
Obtener múltiples valores coincidentes basados en varios 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 utilizando 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 la 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 cuánto se haya arrastrado la fórmula hacia la derecha.
- SI.ERROR(...): Evita errores una vez que 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, todos los valores coincidentes son extraídos y ubicados 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 varios criterios en Excel puede lograrse 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 deseas 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 estás interesado en 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, pero, a veces, necesitamos extraer todos los valores coincidentes y luego separarlos por un delimitador específico, como coma, guion, etc., en una sola celda como se muestra en la siguiente captura de pantalla. ¿Cómo podemos 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 BUSCARV normal en Google Sheets puede ayudarte a encontrar y devolver el primer valor coincidente basado en un dato dado. Pero, a veces, es posible que necesites buscar y devolver todos los valores coincidentes como se muestra en la siguiente captura de pantalla. ¿Tienes alguna buena y fácil manera 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 elijas un elemento de la lista desplegable, todos sus valores relativos se muestren a la vez como se muestra en la siguiente captura de pantalla? Este artículo te mostrará 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. Este artículo hablará sobre cómo buscar y devolver todos los valores coincidentes vertical, horizontal o en una sola celda.
- BuscarV y devolver datos coincidentes entre dos valores en Excel
- En Excel, podemos aplicar la función BUSCARV normal 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
Potencia tus habilidades en 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 lleva la interfaz de pestañas a 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 nuevas ventanas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!