Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo vlookup y concatenar múltiples valores correspondientes en Excel?

Como todos sabemos, el Vlookup la función en Excel puede ayudarnos a buscar un valor y devolver los datos correspondientes en otra columna, pero en general, solo puede obtener el primer valor relativo si hay varios datos coincidentes. En este artículo, hablaré sobre cómo buscar y concatenar múltiples valores correspondientes en una sola celda o en una lista vertical.

Vlookup y devuelve múltiples valores coincidentes verticalmente con fórmula

Vlookup y concatenar múltiples valores coincidentes en una celda con función definida por el usuario

Vlookup y concatenar múltiples valores coincidentes en una celda con Kutools para Excel


Vlookup y concatenar múltiples valores coincidentes en una celda:

Con Kutools for Excel, Filas de combinación avanzadas de hecho, puede combinar rápidamente múltiples filas duplicadas en un registro basado en columnas clave, y también puede aplicar algunos cálculos como suma, promedio, recuento, etc. para otras columnas.

  • 1. Especifique la columna clave en la que desea combinar otra columna;
  • 2. Elija el separador para sus datos combinados.

doc combine el texto basado en los criterios 10

Kutools for Excel: con más de 200 útiles complementos de Excel, de prueba sin límite en días 60. Descargar y prueba gratis ahora!



Supongamos que tengo el siguiente rango de datos, para obtener todos los valores correspondientes basados ​​en un valor específico verticalmente como se muestra en la siguiente captura de pantalla, puede aplicar una fórmula de matriz.

doc vlookup concatenar 1

1. Ingresa esta fórmula: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") en una celda en blanco donde desea poner el resultado, por ejemplo, E2, y luego presione Ctrl + Shift + Enter para obtener la base del valor relativo en un criterio específico, vea la captura de pantalla:

doc vlookup concatenar 2

Nota: En la fórmula anterior:

A1: A16 es el rango de columna que contiene el valor específico que desea buscar;

D2 indica el valor específico que quiere vlookup;

B1: B16 es el rango de columna del que desea devolver los datos correspondientes;

$ 1: $ 16 indica la referencia de filas dentro del rango.

2. A continuación, seleccione la celda E2 y arrastre el controlador de relleno hasta las celdas hasta que obtenga celdas en blanco, y todos los valores coincidentes se enumeran en la columna como se muestra en la siguiente captura de pantalla:

doc vlookup concatenar 3


En lugar de obtener los valores relativos verticalmente, a veces, desea que los valores coincidan en una celda y concatenarlos con un separador específico. En este caso, la siguiente función definida por el usuario puede hacerte un favor.

1. Mantenga pulsado el botón ALT + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.

2. Hacer clic recuadro > Móduloy pegue el siguiente código en Módulo Ventana.

Código VBA: visualice y concatenar múltiples valores coincidentes en una celda

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. A continuación, guarde y cierre este código, vuelva a la hoja de trabajo e ingrese esta fórmula: = cusvlookup (D2, A1: B16,2) en una celda en blanco donde desea poner el resultado, y presione entrar clave, todos los valores correspondientes basados ​​en datos específicos han sido devueltos a una celda con separador de espacio, ver captura de pantalla:

doc vlookup concatenar 4

Nota: En la fórmula anterior: D2 indica los valores de celda que desea buscar, A1: B16 es el rango de datos que desea buscar los datos, el número 2 es el número de columna del que se devolverá el valor coincidente, puede cambiar estas referencias a su necesidad.


Si usted tiene Kutools for Excel, Con su Filas de combinación avanzadas función, puede terminar rápidamente este trabajo con facilidad. Esta característica puede ayudarle a combinar todos los valores coincidentes con un delimitador específico basado en los mismos datos en otra columna.

Kutools for Excel : con más de 300 útiles complementos de Excel, de prueba sin límite en días 60.

Después de instalar Kutools for Excel, haz lo siguiente:

1. Seleccione el rango de datos que desea para obtener los valores correspondientes en función de los datos específicos.

2. Luego haga clic Kutools > Contenido > Filas de combinación avanzadas, mira la captura de pantalla:

doc vlookup concatenar 5

3. En la Filas de combinación avanzadas cuadro de diálogo, haga clic en el nombre de la columna sobre la que desea combinar, luego haga clic Clave primaria botón, ver captura de pantalla:

doc vlookup concatenar 6

4. A continuación, haga clic en el nombre de otra columna que desea devolver los valores coincidentes y haga clic en Combinar para elegir un separador para separar los valores combinados, ver captura de pantalla:

doc vlookup concatenar 7

5. Y luego haz clic Ok botón, todos los valores correspondientes basados ​​en los mismos valores se combinaron junto con un separador específico, ver capturas de pantalla:

doc vlookup concatenar 8  2 doc vlookup concatenar 9

Descargue y pruebe de forma gratuita Kutools for Excel Now!


Kutools for Excel: con más de 200 útiles complementos de Excel, de prueba sin límite en días 60. Descargar y prueba gratis ahora!


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Más de potentes funciones de 300. Funciona con Office 2007-2019 y 365. Soporta todos los idiomas. Fácil despliegue en compañía. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    sym-john · 7 days ago
    Is there any way to get the unique "name" for "class1"
  • To post as a guest, your comment is unpublished.
    Russell · 4 months ago
    This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
    Column A: Dependencies (eg 10003 10004 10008)
    Column B: Task Reference (eg 10001)
    Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

    E.g:

    Row | Column A | Column B | Column C
    1 | | 10001 | 10002 10003
    2 | 10001 | 10002 | 10003
    3 | 10001 10002 | 10003 |
    • To post as a guest, your comment is unpublished.
      Jeff F · 10 days ago
      you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
  • To post as a guest, your comment is unpublished.
    jeff · 6 months ago
    The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

    Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 1 years ago
    I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
    I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
    My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

    Thank you so much for your help!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 years ago
    This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
  • To post as a guest, your comment is unpublished.
    Brett Bieker · 2 years ago
    Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values

    Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :)

    Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long)
    Dim rng As Range
    Dim xResult As String
    xResult = ""
    For Each rng In pWorkRng
    If rng = pValue Then
    xResult = xResult & ";" & rng.Offset(0, pIndex - 1)
    If Left(xResult, 1) = ";" Then
    xResult = MID(xResult,2,255)
    End If
    End If
    Next
    CusVlookup = xResult
    End Function
    • To post as a guest, your comment is unpublished.
      Anand · 1 years ago
      Make if condition for result if empty.

      Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
      'updateby Extendoffice 20151118
      Dim x As Range
      Dim result As String
      result = ""
      For Each x In lookuprange
      If x = lookupval Then
      If Not result = "" Then
      result = result & " " & x.Offset(0, indexcol - 1)
      Else
      result = x.Offset(0, indexcol - 1)
      End If
      Next x
      CusVlookup = result
      End Function
  • To post as a guest, your comment is unpublished.
    slohman · 2 years ago
    When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
  • To post as a guest, your comment is unpublished.
    Deepan Saha · 3 years ago
    How to get the result. Please help.

    data data1 result
    a 1 a1
    b 2 a2
    c b1
    b2
    c1
    c2