¿Cómo devolver varios rangos de valores de búsqueda separados por comas en una sola celda?
Al trabajar con datos en Excel, es posible que te encuentres en una situación en la que necesites devolver varios valores coincidentes a partir de una búsqueda y mostrarlos en una sola celda, separados por comas, guiones u otros delimitadores. Aunque las funciones integradas de búsqueda de Excel, como BUSCARV e ÍNDICE-COINCIDIR, son ideales para encontrar una única coincidencia, combinar varios resultados en una misma celda requiere un enfoque más avanzado. En este artículo te presentamos algunos métodos útiles para resolver esta tarea en Excel.
Devolver varios Rango de valor de búsqueda en una celda separados por comas
Devolver varios Rango de valor de búsqueda en una celda separados por comas con las funciones TEXTJOIN y FILTER
Si dispone de Excel 365, Excel 2021 o versiones posteriores, utilizar una fórmula de matriz dinámica es el método más sencillo.
Aplique la siguiente fórmula en una celda vacía, pulse la tecla «Intro» y obtenga el resultado.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Después, arrastre el controlador de relleno hacia abajo para copiar la fórmula en otras celdas; consulte la captura de pantalla:
- FILTER(B2:B15; A2:A15=D2): extrae todos los valores de la columna B cuyas filas en la columna A coincidan con el valor de la celda D2.
- TEXTJOIN(", "; VERDADERO; ...): combina los valores resultantes en una única cadena, separándolos con comas.
Devolver varios Rango de valor de búsqueda en una celda separados por comas con Kutools para Excel
«Kutools para Excel» es un complemento diseñado para simplificar tareas complejas en Excel. Su avanzada función «Búsqueda uno a muchos» le permite extraer fácilmente todos los valores coincidentes en una sola celda, separados por el delimitador que especifique, sin necesidad de fórmulas complicadas ni código VBA.
Tras instalar Kutools para Excel, siga estos pasos:
Haga clic en **Kutools** > **Super BUSCARV** > **Búsqueda uno a muchos (devuelve varios resultados)** para abrir el cuadro de diálogo. Una vez abierto, configure las operaciones tal como se indica a continuación:
- Seleccione «Área de ubicación de la lista y Rango de valor de búsqueda» en los cuadros de texto por separado;
- Seleccione el rango de la tabla que desea utilizar;
- Especifique Columna clave y Columna de devolución en los desplegables «Columna clave» y «Columna de devolución» respectivamente;
- Por último, haga clic en el botón «Aceptar».
Resultado:
Todas las celdas correspondientes con el mismo valor se han combinado en una sola celda, separadas por comas; consulte la captura de pantalla:
Devolver varios Rango de valor de búsqueda en una celda separados por comas con una función definida por el usuario
Si no dispone de Excel 365 ni de Excel 2021, esta sección le guiará paso a paso en la creación y uso de una función definida por el usuario (UDF) para devolver varios rangos de valores de búsqueda en una sola celda, separados por comas.
1. Mantén pulsadas las teclas «ALT» + «F11» para abrir la ventana de «Microsoft Visual Basic para Aplicaciones».
2. Haga clic en «Insertar» > «Módulo» y pegue el siguiente código en la ventana del módulo.
Código VBA: Devolver varios Rango de valor de búsqueda en una celda separada por comas
Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
'Updateby Extendoffice
Dim result As String
Dim i As Long
result = ""
For i = 1 To lookupRange.Rows.Count
If lookupRange.Cells(i, 1).Value = lookupValue Then
result = result & returnRange.Cells(i, 1).Value & delimiter
End If
Next i
If Len(result) > 0 Then
JoinLookupValues = Left(result, Len(result) - Len(delimiter))
Else
JoinLookupValues = ""
End If
End Function
3. A continuación, guarde este código y cierre la ventana del módulo. Vuelva a su hoja de cálculo e introduzca la siguiente fórmula en una celda vacía donde desee obtener el resultado. Luego, arrastre el controlador de relleno hacia abajo para aplicar la fórmula a otras celdas; consulte la captura de pantalla:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
En conclusión, devolver varios rangos de valores de búsqueda en una sola celda separados por comas puede lograrse eficazmente mediante diversos métodos, según su versión de Excel y sus necesidades. Al elegir el método que mejor se adapte a sus requisitos, podrá optimizar su análisis de datosy potencie su productividad. Si desea descubrir más consejos y trucos de Excel,nuestra página web ofrece miles de tutoriales.
Artículos relacionados:
- BuscarV y devolver varios valores sin duplicados
- Al trabajar con datos en Excel, es posible que necesite devolver varios valores coincidentes según un criterio de búsqueda específico. Sin embargo, la función BUSCARV estándar solo recupera un único valor. Cuando existen varias coincidencias y desea mostrarlas todas en una sola celda sin duplicados, puede recurrir a métodos alternativos para lograrlo.
- Devolver varios valores coincidentes según uno o varios criterios
- Normalmente, buscar un valor específico y devolver el elemento coincidente resulta sencillo para la mayoría de nosotros gracias a la función BUSCARV. Pero, ¿ha intentado alguna vez devolver varios valores coincidentes según uno o más criterios, como se muestra en la siguiente captura de pantalla? En este artículo, le presento algunas fórmulas para resolver esta tarea compleja en Excel.
- BuscarV y devolver el último valor coincidente
- Si tiene una lista de elementos que se repiten muchas veces y ahora desea conocer únicamente el último valor coincidente con sus datos especificados, ¿cómo lo haría?
- BuscarV y devolver datos coincidentes entre dos valores
- En Excel, podemos aplicar la función BUSCARV normal para obtener el valor correspondiente a partir de un dato dado. Pero a veces queremos realizar una búsqueda vertical y devolver el valor coincidente comprendido entre dos valores, tal como se muestra en la siguiente captura de pantalla. ¿Cómo podría abordar 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