¿Cómo usar BUSCARV para devolver varios valores en una sola celda en Excel?
BUSCARV es una función potente en Excel, pero de forma predeterminada solo devuelve la primera coincidencia. ¿Y si necesita recuperar todos los valores coincidentes y combinarlos en una sola celda? Esta necesidad surge con frecuencia al analizar conjuntos de datos o resumir información. En esta guía le mostraremos métodos paso a paso para devolver varios valores en una sola celda, utilizando tanto fórmulas como funciones útiles.
Devolver varios valores en una celda con la función TEXTOJOIN (Excel 2019 y Office 365)
- Devolver todos los valores coincidentes en una sola celda
- Devolver todos los valores coincidentes sin duplicados en una sola celda
Devolver varios valores en una celda con Kutools
Devolver varios valores en una celda con una función definida por el usuario
- Devolver todos los valores coincidentes en una sola celda
- Devolver todos los valores coincidentes sin duplicados en una sola celda
Devolver varios valores en una celda con la función TEXTOJOIN (Excel 2019 y Office 365)
Si tiene una versión más reciente de Excel, como Excel 2019 u Office 365, cuenta con una función nueva: TEXTJOIN. Gracias a esta potente función, podrá realizar rápidamente una búsqueda con BUSCARV y obtener todos los valores coincidentes en una sola celda.
Devolver todos los valores coincidentes en una sola celda
Aplique la siguiente fórmula en una celda vacía donde desee colocar el resultado, pulse simultáneamente las teclas Ctrl + Mayús + Intro para obtener el primer valor y, a continuación, arrastre el controlador de relleno hasta la celda en la que quiera aplicar esta fórmula. Así obtendrá todos los valores correspondientes, tal como se muestra en la siguiente captura de pantalla:
Devolver todos los valores coincidentes sin duplicados en una sola celda
Si desea obtener todos los valores coincidentes según los criterios de búsqueda, sin duplicados, la siguiente fórmula puede ayudarle.
Copie y pegue la siguiente fórmula en una celda vacía, pulse Ctrl + Mayús + Intro simultáneamente para obtener el primer resultado y, a continuación, copie dicha fórmula para rellenar las demás celdas. Así obtendrá todos los valores correspondientes sin duplicados, tal como se muestra en la siguiente captura de pantalla:
Devolver varios valores en una celda con Kutools
¡Con la función «Combinar filas avanzado» de Kutools para Excel, puede recuperar fácilmente varios valores coincidentes en una sola celda sin recurrir a fórmulas complejas! Olvídese de los métodos manuales y descubra una forma más eficiente de gestionar sus tareas de búsqueda en Excel. ¡Veamos cómo lo hace posible Kutools para Excel!
Tras instalar Kutools para Excel, proceda del siguiente modo:
1. Seleccione el rango de datos en el que desea combinar los valores de una columna según los de otra.
2. Haga clic en «Kutools» > «Combinar y dividir» > «Combinar filas avanzado», tal como se muestra en la captura de pantalla:
3. En el cuadro de diálogo emergente «Combinar filas avanzado»:
- Haga clic en el nombre de la columna clave según la cual se combinarán los datos y, a continuación, seleccione «Clave principal».
- Luego, haga clic en otra columna cuyos datos desee combinar según la Columna clave, pulse la lista desplegable del campo «Operación» y elija un separador para distinguir los datos combinados en la sección «Combinar».
- A continuación, haga clic en el botón Aceptar.
Todos los valores correspondientes de otra columna que compartan el mismo valor se combinan en una única celda. Consulte las capturas de pantalla:
 |  |  |
Consejos: Si desea eliminar contenido duplicado al combinar celdas, active simplemente la opción «Eliminar valores duplicados» en el cuadro de diálogo. Así garantizará que solo se combinen entradas únicas en una sola celda, consiguiendo unos datos más limpios y organizados sin esfuerzo adicional. Consulte las capturas de pantalla:
 | |  |
¡Descargue y pruebe gratis Kutools para Excel ahora!
Devolver varios valores en una celda con una función definida por el usuario
La función TEXTOJOIN mencionada anteriormente solo está disponible en Excel 2019 y Office 365. Si usa versiones anteriores de Excel, deberá recurrir a código para completar esta tarea.
Devolver todos los valores coincidentes en una sola celda
1. Mantenga 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: BuscarV para devolver varios valores en una sola celda
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
3. A continuación, guarde y cierre este código, regrese a la hoja de cálculo e introduzca la siguiente fórmula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") en una celda vacía donde desee mostrar el resultado. Luego, arrastre el controlador de relleno hacia abajo para obtener todos los valores correspondientes en una sola celda, tal como se muestra en la captura de pantalla:
Devolver todos los valores coincidentes sin duplicados en una sola celda
Para omitir duplicados en los valores coincidentes devueltos, utilice el siguiente código.
1. Mantenga 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: BuscarV y devolver varios valores coincidentes únicos en una sola celda
Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
Dim xDic As New Dictionary
Dim xRows As Long
Dim xStr As String
Dim i As Long
On Error Resume Next
xRows = LookupRange.Rows.Count
For i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Next
xStr = ""
MultipleLookupNoRept = xStr
If xDic.Count > 0 Then
For i = 0 To xDic.Count - 1
xStr = xStr & xDic.Keys(i) & ","
Next
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
End Function
3. Tras insertar el código, haga clic en «Herramientas» > «Referencias» en la ventana de «Microsoft Visual Basic para Aplicaciones». A continuación, en el cuadro de diálogo emergente «Referencias: VBAProject», active la opción «Microsoft Scripting Runtime» en la lista de «Referencias disponibles». Consulte las capturas de pantalla:
 | |  |
4. Luego, haga clic en Aceptar para cerrar el cuadro de diálogo, guarde y cierre la ventana del código, regrese a la hoja de cálculo e introduzca la siguiente fórmula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) en una celda vacía donde desee mostrar el resultado. A continuación, arrastre el controlador de relleno hacia abajo para obtener todos los valores coincidentes. Consulte la captura de pantalla:
Tanto si opta por fórmulas como TEXTJOIN combinada con funciones matriciales, como si prefiere herramientas como Kutools para Excelo funciones definidas por el usuario: todos estos enfoques simplifican tareas complejas de búsqueda. ¡Elija el método que mejor se adapte a sus necesidades! Si desea descubrir más consejos y trucos de Excel,nuestra web ofrece miles de tutoriales.
Más artículos relacionados:
- Función BUSCARV con ejemplos básicos y avanzados
- En Excel, la función BUSCARV es una herramienta esencial para la mayoría de los usuarios, ya que permite buscar un valor en la primera columna de un rango de datos y devolver un valor correspondiente de la misma fila en una columna especificada. Este tutorial explica cómo utilizar la función BUSCARV mediante ejemplos básicos y avanzados en Excel.
- 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 gracias a la función BUSCARV. Pero, ¿ha intentado alguna vez devolver varios valores coincidentes según uno o más criterios? En este artículo, presento algunas fórmulas para resolver esta tarea compleja en Excel.
- BuscarV y devolver varios valores verticalmente
- Normalmente, puede usar la función BuscarV para obtener el primer valor correspondiente, pero a veces desea devolver todos los registros coincidentes según un criterio específico. En este artículo explicaré cómo realizar una búsqueda con BuscarV y devolver todos los valores coincidentes verticalmente, horizontalmente o en una única celda.
- BuscarV y devolver varios valores desde una lista desplegable
- En Excel, ¿cómo podría realizar una búsqueda con BuscarV 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? En este artículo presento la solución paso a paso.
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