¿Cómo usar vlookup para devolver múltiples valores en una sola celda en Excel?
VLOOKUP es una función poderosa en Excel, pero por defecto solo devuelve el primer valor coincidente. ¿Qué pasa si necesitas recuperar todos los valores coincidentes y combinarlos en una sola celda? Esta es una necesidad común al analizar conjuntos de datos o resumir información. En esta guía, te guiaremos paso a paso sobre cómo devolver múltiples valores en una sola celda utilizando tanto fórmulas como funciones útiles.
Devolver múltiples valores en una celda con la función TEXTJOIN (Excel 2019 y Office 365)
- Devolver todos los valores coincidentes en una celda
- Devolver todos los valores coincidentes sin duplicados en una celda
Devolver múltiples valores en una celda con Kutools
Devolver múltiples valores en una celda con una Función Definida por el Usuario
- Devolver todos los valores coincidentes en una celda
- Devolver todos los valores coincidentes sin duplicados en una celda
Devolver múltiples valores en una celda con la función TEXTJOIN (Excel 2019 y Office 365)
Si tienes una versión superior de Excel, como Excel 2019 u Office 365, hay una nueva función: TEXTJOIN. Con esta poderosa función, puedes buscar rápidamente y devolver todos los valores coincidentes en una sola celda.
Devolver todos los valores coincidentes en una celda
Por favor, aplica la siguiente fórmula en una celda en blanco donde deseas colocar el resultado, luego presiona las teclas Ctrl + Shift + Enter juntas para obtener el primer resultado, y luego arrastra el controlador de relleno hacia abajo hasta la celda donde deseas usar esta fórmula, y obtendrás todos los valores correspondientes como se muestra en la captura de pantalla a continuación:
Devolver todos los valores coincidentes sin duplicados en una celda
Si deseas devolver todos los valores coincidentes basados en los datos de búsqueda sin duplicados, la siguiente fórmula puede ayudarte.
Por favor, copia y pega la siguiente fórmula en una celda en blanco, luego presiona las teclas Ctrl + Shift + Enter juntas para obtener el primer resultado, y luego copia esta fórmula para llenar otras celdas, y obtendrás todos los valores correspondientes sin los duplicados como se muestra en la captura de pantalla a continuación:
Devolver múltiples valores en una celda con Kutools
Con la función "Combinar filas avanzado" de Kutools para Excel, puedes recuperar fácilmente varios valores coincidentes en una sola celda, ¡sin fórmulas complejas! Di adiós a las soluciones manuales y descubre una forma más eficiente de manejar tus tareas de búsqueda en Excel. ¡Veamos cómo Kutools para Excel lo hace posible!
Después de instalar Kutools para Excel, por favor haz lo siguiente:
1. Selecciona el rango de datos que deseas combinar una columna de datos basada en otra columna.
2. Haz clic en "Kutools" > "Combinar y dividir" > "Combinar filas avanzado", ver captura de pantalla:
3. En el cuadro de diálogo emergente "Combinar filas avanzado":
- Haz clic en el nombre de la columna clave que se va a combinar, y luego haz clic en "Clave principal".
- Luego haz clic en otra columna que deseas combinar sus datos basados en la columna clave, y haz clic en la lista desplegable del campo "Operación", elige un separador para separar los datos combinados de la sección "Combinar".
- Luego, haz clic en el botón Aceptar.
Todos los valores correspondientes de otra columna, basados en el mismo valor, se combinan en una sola celda. Ver capturas de pantalla:
 |  |  |
Consejos: Si deseas eliminar contenido duplicado mientras combinas celdas, simplemente marca la opción "Eliminar valores duplicados" en el cuadro de diálogo. Esto asegura que solo las entradas únicas se combinen en una sola celda, haciendo que tus datos sean más limpios y organizados sin esfuerzo adicional. Ver capturas de pantalla:
 | |  |
¡Descarga y prueba gratis Kutools para Excel ahora!
Devolver múltiples valores en una celda con una Función Definida por el Usuario
La función TEXTJOIN mencionada anteriormente solo está disponible para Excel 2019 y Office 365; si tienes otras versiones inferiores de Excel, deberías usar algunos códigos para completar esta tarea.
Devolver todos los valores coincidentes en una celda
1. Mantén presionadas las teclas "ALT + F11", y se abrirá la ventana "Microsoft Visual Basic para Aplicaciones".
2. Haz clic en "Insertar" > "Módulo", y pega el siguiente código en la Ventana de Módulo.
Código VBA: Vlookup para devolver múltiples valores en una 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. Luego guarda y cierra este código, regresa a la hoja de trabajo, e introduce esta fórmula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") en una celda en blanco específica donde deseas colocar el resultado, luego arrastra el controlador de relleno hacia abajo para obtener todos los valores correspondientes en una celda que desees, ver captura de pantalla:
Devolver todos los valores coincidentes sin duplicados en una celda
Para ignorar los duplicados en los valores coincidentes devueltos, por favor usa el siguiente código.
1. Mantén presionadas las teclas "Alt + F11" para abrir la ventana "Microsoft Visual Basic para Aplicaciones".
2. Haz clic en "Insertar" > "Módulo", y pega el siguiente código en la Ventana de Módulo.
Código VBA: Vlookup y devolver múltiples valores únicos coincidentes en una 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. Después de insertar el código, haz clic en "Herramientas" > "Referencias" en la ventana abierta "Microsoft Visual Basic para Aplicaciones", y luego, en el cuadro de diálogo emergente "Referencias – VBAProject", marca la opción "Microsoft Scripting Runtime" en el cuadro de lista "Referencias disponibles", ver capturas de pantalla:
 | |  |
4. Luego haz clic en Aceptar para cerrar el cuadro de diálogo, guarda y cierra la ventana de código, regresa a la hoja de trabajo, e introduce esta fórmula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) en una celda en blanco donde deseas mostrar el resultado, y luego arrastra el controlador de relleno hacia abajo para obtener todos los valores coincidentes, ver captura de pantalla:
Ya sea que optes por fórmulas como TEXTJOIN combinadas con funciones de matriz, aproveches herramientas como Kutools para Excel o una Función Definida por el Usuario, todos los enfoques ayudan a simplificar tareas de búsqueda complejas. Elige el método que mejor se adapte a tus necesidades. Si estás interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales.
Más artículos relacionados:
- Función VLOOKUP con algunos ejemplos básicos y avanzados
- En Excel, la función VLOOKUP es una función poderosa para la mayoría de los usuarios de Excel, que se utiliza para buscar un valor en el extremo izquierdo del rango de datos y devolver un valor coincidente en la misma fila desde una columna especificada. Este tutorial trata sobre cómo usar la función VLOOKUP con algunos ejemplos básicos y avanzados en Excel.
- Devolver múltiples valores coincidentes basados en uno o varios criterios
- Normalmente, buscar un valor específico y devolver el elemento coincidente es fácil para la mayoría de nosotros usando la función VLOOKUP. Pero, ¿alguna vez has intentado devolver múltiples valores coincidentes basados en uno o más criterios? En este artículo, presentaré algunas fórmulas para resolver esta tarea compleja en Excel.
- Buscar verticalmente y devolver múltiples valores
- Normalmente, puedes usar la función Vlookup 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 verticalmente, horizontalmente o en una sola celda.
- Buscar 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 seleccionas un elemento de la lista desplegable, todos sus valores relativos se muestran a la vez. Este artículo te presentará la solución paso a paso.
Las mejores herramientas de productividad para Office
Impulsa al máximo tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para potenciar la productividad y ahorrar tiempo.Haz clic aquí para obtener la función que más necesitas...
Office Tab aporta una interfaz de pestañas a Office y hace tu trabajo mucho más sencillo
- 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 vez de en nuevas ventanas.
- ¡Aumenta tu productividad hasta un50% y reduce cientos de clics de ratón cada día!