¿Cómo hacer una búsqueda vertical y concatenar múltiples valores correspondientes en Excel?

Cuando se utiliza VLOOKUP en Excel, la función normalmente devolverá solo el primer valor coincidente que encuentre para un criterio de búsqueda dado. Sin embargo, existen muchos escenarios comunes donde puede ser necesario recuperar y combinar todos los valores coincidentes asociados con una clave particular, como listar a todos los estudiantes en una clase o todos los productos asociados con una categoría específica. Dado que la función VLOOKUP estándar es limitada en este aspecto, podrías preguntarte cómo lograr la capacidad de buscar y concatenar múltiples resultados correspondientes en una sola celda. A continuación, exploraremos varios métodos prácticos y eficientes para realizar esta tarea, adecuados para diferentes versiones de Excel y preferencias del usuario.

Búsqueda vertical y concatenación de múltiples valores correspondientes en Excel

• Usando las funciones TEXTJOIN y FILTER (Excel 365/2021)
• Usando Kutools para Excel
• Usando Función Definida por el Usuario
• Usando código VBA para múltiples concatenaciones

Búsqueda vertical y concatenación de múltiples valores correspondientes con las funciones TEXTJOIN y FILTER

Si estás utilizando Excel 365 o Excel 2021, la combinación de las funciones TEXTJOIN y FILTER ofrece un enfoque eficiente basado en fórmulas para buscar y concatenar todos los valores coincidentes. Esta solución es especialmente adecuada para conjuntos de datos dinámicos y actualizados, ya que actualizará automáticamente el resultado cuando cambien los datos de origen. Es mejor aplicarla si tu versión de Excel soporta la función FILTER, que es exclusiva de las versiones más recientes de Office.

En la celda objetivo, introduce la siguiente fórmula, luego arrastra la fórmula hacia abajo si deseas aplicarla a otras filas también. Todos los valores coincidentes correspondientes se extraen y combinan en una sola celda. Ver captura de pantalla:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Búsqueda vertical y concatenación de múltiples valores correspondientes con Kutools para Excel

Si encuentras complicados los métodos de fórmulas integradas o tu versión de Excel no soporta funciones avanzadas como TEXTJOIN y FILTER, Kutools para Excel ofrece una solución gráfica amigable para el usuario. La función Búsqueda uno a muchos en Kutools te permite buscar y concatenar múltiples resultados coincidentes con unos pocos pasos, lo que la hace adecuada tanto para principiantes como para usuarios avanzados. Con Kutools, no es necesario escribir fórmulas o códigos complicados, y es especialmente útil al manejar grandes o variables conjuntos de datos que requieren búsquedas y agregaciones repetidas.

Después de instalar Kutools para Excel, sigue los siguientes pasos:

Haz clic en Kutools > Super BUSCARV > Búsqueda uno a muchos (devuelve múltiples resultados) para abrir el cuadro de diálogo de configuración. Dentro de este cuadro de diálogo, puedes configurar rápidamente tus ajustes de búsqueda y salida siguiendo estos pasos:

1. Selecciona las celdas de salida objetivo para los resultados concatenados y las celdas que contienen los valores que deseas buscar;
2. Indica el rango de tabla que contiene tanto la clave de búsqueda como las columnas de resultados;
3. Especifica qué columna contiene las claves de búsqueda (Columna Clave) y la columna cuyos valores se concatenarán (Columna de Devolución);
4. Haz clic en el botón Aceptar para confirmar tus ajustes y procesar los datos.
   

Resultado: Kutools ahora mostrará todos los valores coincidentes y concatenados en tu celda de salida seleccionada. Ver captura de pantalla:

Este método es altamente recomendado para aquellos que prefieren trabajar desde la interfaz de Excel sin fórmulas o códigos complejos. También reduce la probabilidad de errores en las fórmulas y mejora la productividad al manejar tareas repetitivas de búsqueda y concatenación.

Búsqueda vertical y concatenación de múltiples valores correspondientes mediante Función Definida por el Usuario

Para usuarios que son expertos en VBA (Visual Basic for Applications), o aquellos que usan versiones antiguas de Excel que carecen de compatibilidad con matrices dinámicas o la función FILTER, puedes crear una Función Definida por el Usuario (UDF) personalizada para lograr la concatenación flexible de múltiples resultados. Este método es universalmente compatible con todas las versiones de Excel y puede adaptarse a símbolos de separación particulares o condiciones.

1. Mantén presionadas las teclas ALT + F11 para abrir la ventana de 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: Búsqueda vertical y concatenación de múltiples valores coincidentes en una celda

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. Guarda y cierra el editor de VBA. Regresa a tu hoja de trabajo, y usa esta UDF ingresando la fórmula: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) en una celda en blanco donde quieras tu resultado. Arrastra el controlador de relleno hacia abajo para copiar la fórmula en otras celdas según sea necesario. Todos los valores correspondientes basados en un valor de búsqueda específico se devolverán y concatenarán en una sola celda, separados por una coma y un espacio. Ver captura de pantalla:

Búsqueda vertical y concatenación de múltiples valores correspondientes con código VBA

Para escenarios que requieran uso repetido o para aquellos que deseen evitar funciones personalizadas en las celdas de la hoja de trabajo, puedes utilizar una macro VBA lista para usar para concatenar resultados directamente. Este método funciona bien en entornos compartidos donde no todos los usuarios puedan tener la misma versión o complementos.

1. Haz clic en Herramientas de Desarrollo > Visual Basic para abrir el editor de VBA.

2. En la ventana de VBA, haz clic en Insertar > Módulo, luego pega este código en el módulo:

Sub VLookupAndConcatenate()
    Dim ws As Worksheet
    Dim dataRange As Range, lookupRange As Range, resultRange As Range
    Dim dict As Object
    Dim i As Long, lastRow As Long
    Dim lookupValue As Variant, result As String
    Dim delimiter As String
    delimiter = ", "
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Please select the data range (contains lookup column and result column)", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    If dataRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set lookupRange = Application.InputBox( _
        Prompt:="Please select the lookup range (single column)", _
        Title:="Select Lookup Range", _
        Type:=8)
    On Error GoTo 0
    If lookupRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set resultRange = Application.InputBox( _
        Prompt:="Please select the starting cell for results output", _
        Title:="Select Output Location", _
        Type:=8)
    On Error GoTo 0
    If resultRange Is Nothing Then Exit Sub
    resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
    For i = 1 To dataRange.Rows.Count
        lookupValue = dataRange.Cells(i, 1).Value
        If Not dict.Exists(lookupValue) Then
            dict.Add lookupValue, dataRange.Cells(i, 2).Value
        Else
            dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
        End If
    Next i
    For i = 1 To lookupRange.Rows.Count
        lookupValue = lookupRange.Cells(i, 1).Value
        If dict.Exists(lookupValue) Then
            resultRange.Cells(i, 1).Value = dict(lookupValue)
        Else
            resultRange.Cells(i, 1).Value = "Not Found"
        End If
    Next i
    MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub

3. Haz clic en el botón para ejecutar la macro. Los cuadros de entrada te pedirán que selecciones tu rango de datos, rango de búsqueda, rango de resultados. El resultado concatenado se muestra directamente en las celdas de salida seleccionadas.

Este enfoque de macro es particularmente útil si realizas frecuentemente búsquedas de concatenación múltiple con diferentes valores, ya que evita llenar la hoja de trabajo con llamadas UDF.

Puedes ajustar fácilmente el delimitador en el código si es necesario, y extender la macro para generar resultados a una celda o archivo según tu flujo de trabajo.

Concatenar múltiples valores correspondientes en Excel es posible usando varios enfoques, cada uno con beneficios específicos dependiendo de tu situación. Ya sea que elijas fórmulas de matriz dinámica, complementos como Kutools para Excel o métodos basados en VBA, mejorarás tu capacidad para analizar y mostrar datos agrupados de manera eficiente. Dependiendo del tamaño y la complejidad de tu conjunto de datos, considera qué enfoque ofrece el mejor rendimiento y facilidad de mantenimiento para ti o tu equipo. En operaciones diarias, verifica la consistencia de los datos, evita las celdas combinadas y verifica los rangos de referencia para obtener los mejores resultados. Si encuentras errores en los cálculos de fórmulas, revisa nuevamente que tus rangos coincidan con los datos y que utilices el método correcto de entrada de fórmulas para tu versión de Excel.

Para técnicas avanzadas de Excel y una amplia gama de guías prácticas paso a paso, visita nuestra extensa biblioteca de tutoriales.