Note: The other languages of the website are Google-translated. Back to English

¿Cómo vlookup para devolver múltiples valores en una celda en Excel?

Normalmente, en Excel, cuando usa la función BUSCARV, si hay varios valores que coinciden con los criterios, solo puede obtener el primero. Pero, a veces, desea devolver todos los valores correspondientes que cumplen con los criterios en una celda como se muestra en la siguiente captura de pantalla, ¿cómo podría resolverlo?

Vlookup para devolver múltiples valores en una celda con la función TEXTJOIN (Excel 2019 y Office 365)

Vlookup para devolver múltiples valores en una celda con la función definida por el usuario

Vlookup para devolver múltiples valores en una celda con una característica útil


Vlookup para devolver múltiples valores en una celda con la función TEXTJOIN (Excel 2019 y Office 365)

Si tiene la versión superior de Excel, como Excel 2019 y Office 365, hay una nueva función: TEXTJOIN, con esta poderosa función, puede visualizar rápidamente y devolver todos los valores coincidentes en una celda.

Vlookup para devolver todos los valores coincidentes en una celda

Aplique la fórmula a continuación en una celda en blanco donde desea colocar el resultado, luego presione Ctrl + Shift + Enter teclas juntas para obtener el primer resultado, y luego arrastre el controlador de relleno hacia la celda en la que desea usar esta fórmula, y obtendrá todos los valores correspondientes como se muestra a continuación:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Nota: En la fórmula anterior, A2: A11 es el rango de búsqueda que contiene los datos de búsqueda, E2 es el valor de búsqueda, C2: C11 es el rango de datos del que desea devolver los valores coincidentes, ","es el separador para separar los múltiples registros.

Vlookup para devolver todos los valores coincidentes sin duplicados en una celda

Si desea devolver todos los valores coincidentes basados ​​en los datos de búsqueda sin duplicados, la siguiente fórmula puede ayudarlo.

Copie y pegue la siguiente fórmula en una celda en blanco, luego presione Ctrl + Shift + Enter claves juntas para obtener el primer resultado, y luego copie esta fórmula para llenar otras celdas, y obtendrá todos los valores correspondientes sin los dulpicates como se muestra a continuación:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Nota: En la fórmula anterior, A2: A11 es el rango de búsqueda que contiene los datos de búsqueda, E2 es el valor de búsqueda, C2: C11 es el rango de datos del que desea devolver los valores coincidentes, ","es el separador para separar los múltiples registros.

Vlookup para devolver múltiples valores en una celda con la función definida por el usuario

La función TEXTJOIN anterior solo está disponible para Excel 2019 y Office 365, si tiene otras versiones inferiores de Excel, debe usar algunos códigos para finalizar esta tarea.

Vlookup para devolver todos los valores coincidentes en una celda

1. Mantenga pulsado el ALT + F11 llaves, y abre el Microsoft Visual Basic para aplicaciones ventana.

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

Código de 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 guarde y cierre este código, regrese a la hoja de trabajo e ingrese esta fórmula: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") en una celda en blanco específica donde desea colocar el resultado, luego arrastre el controlador de relleno hacia abajo para obtener todos los valores correspondientes en una celda que desee, vea la captura de pantalla:

Nota: En la fórmula anterior, A2: A11 es el rango de búsqueda que contiene los datos de búsqueda, E2 es el valor de búsqueda, C2: C11 es el rango de datos del que desea devolver los valores coincidentes, ","es el separador para separar los múltiples registros.

Vlookup para devolver todos los valores coincidentes sin duplicados en una celda

Para ignorar los duplicados en los valores coincidentes devueltos, hágalo con el siguiente código.

1. Mantenga pulsado el Alt + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.

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

Código de VBA: Vlookup y devuelve múltiples valores coincidentes únicos 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, haga clic en Herramientas > Referencias en el abierto Microsoft Visual Basic para aplicaciones ventana, y luego, en la ventana emergente Referencias - VBAProject cuadro de diálogo, comprobar Tiempo de ejecución de secuencias de comandos de Microsoft opción en el Referencias disponibles cuadro de lista, ver capturas de pantalla:

4. Luego haga clic OK para cerrar el cuadro de diálogo, guarde y cierre la ventana de código, vuelva a la hoja de trabajo e ingrese esta fórmula: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Nota: En la fórmula anterior, A2: C11 es el rango de datos que desea usar, E2 es el valor de búsqueda, el número 3 es el número de columna que contiene los valores devueltos.

Vlookup para devolver múltiples valores en una celda con una característica útil

 Si tienes nuestro Kutools for Excel, Con su Filas combinadas avanzadas función, puede fusionar o combinar rápidamente las filas en función del mismo valor y hacer algunos cálculos que necesite.

Nota:Para aplicar esto Filas combinadas avanzadas, en primer lugar, debe descargar el Kutools for Excely, a continuación, aplique la función de forma rápida y sencilla.

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

1. Seleccione el rango de datos en el que desea combinar los datos de una columna en función de otra columna.

2. Hacer clic Kutools > Fusionar y dividir > Filas combinadas avanzadas, ver captura de pantalla:

3. En el estalló Filas combinadas avanzadas caja de diálogo:

  • Haga clic en el nombre de la columna de clave que se combinará en función y luego haga clic Clave primaria
  • Luego haga clic en otra columna en la que desee combinar sus datos según la columna clave y haga clic en Combinar para elegir un separador para separar los datos combinados.

4. A continuación, haga clic en OK botón, y obtendrá los siguientes resultados:

¡Descargue y pruebe Kutools para Excel ahora!


Artículos más relativos:

  • Función VLOOKUP con algunos ejemplos básicos y avanzados
  • En Excel, la función BUSCARV es una función poderosa para la mayoría de los usuarios de Excel, que se usa para buscar un valor en el extremo izquierdo del rango de datos y devolver un valor coincidente en la misma fila de una columna que especificó. Este tutorial habla sobre cómo usar la función VLOOKUP con algunos ejemplos básicos y avanzados en Excel.
  • Devuelve varios valores coincidentes según 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 BUSCARV. Pero, ¿alguna vez ha intentado devolver varios valores coincidentes en función de uno o más criterios? En este artículo, presentaré algunas fórmulas para resolver esta compleja tarea en Excel.
  • Vlookup y devuelve múltiples valores verticalmente
  • Normalmente, puede usar la función Vlookup 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, hablaré sobre cómo visualizar y devolver todos los valores coincidentes verticalmente, horizontalmente o en una sola celda.
  • Vlookup y devuelve varios valores de la lista desplegable
  • En Excel, ¿cómo podría visualizar y devolver múltiples valores correspondientes de una lista desplegable, lo que significa que cuando elige un elemento de la lista desplegable, todos sus valores relativos se muestran a la vez? En este artículo, presentaré la solución paso a paso.

Las mejores herramientas de productividad de oficina

Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%

  • Reutilizar: Inserte rápidamente fórmulas complejas, gráficos y cualquier cosa que hayas usado antes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas sin perder datos; Contenido de celdas divididas; Combinar filas / columnas duplicadas... Prevenir celdas 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 Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Más de 300 potentes funciones. Compatible con Office/Excel 2007-2021 y 365. Compatible con todos los idiomas. Fácil implementación en su empresa u organización. Funciones completas Prueba gratuita de 30 días. Garantía de devolución de dinero de 60 días.
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

  • Habilite 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 de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior
Comentarios (43)
Aún no hay calificaciones. ¡Sé el primero en calificar!
Este comentario fue minimizado por el moderador en el sitio
¿Cómo ajustaría esta fórmula para separar cada valor devuelto excepto "," y solo devolvería valores únicos?
Este comentario fue minimizado por el moderador en el sitio
¡¡Gracias por el código!!

En cuanto a los comodines, una forma de evitarlos es usar INSTR

Puede reemplazar [If rng = pValue Then] con [InStr(1, rng.Value, pValue) Then] y si no quiere que se distinga entre mayúsculas y minúsculas, use [InStr(1, rng.Value, pValue, vbTextCompare) Entonces]
Este comentario fue minimizado por el moderador en el sitio
Gracias por el código VBA anterior. ¿Puede decirme cómo hacer que los resultados ingresen en una nueva línea en la celda, es decir, como Alt-Enter 300 400 1000 1300
Este comentario fue minimizado por el moderador en el sitio
Gracias por compartir el código anterior. He estado usando esto durante varios meses, pero hoy parece que no funciona. Recibo celdas en blanco en lugar del error habitual cuando hay datos para devolver. ¿Alguna idea?
Este comentario fue minimizado por el moderador en el sitio
Impresionante trabajo... ¡Obtuve exactamente lo que quiero! Me encanta !!
Este comentario fue minimizado por el moderador en el sitio
Hola, estoy realmente impresionado con el trabajo y es muy fácil crear uno para usar esta función. sin embargo, necesito más apoyo. Mi ? es que, ¿cómo puedo seleccionar un número de una celda con varias celdas en mi matriz vlookup? es decir, si la celda A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Apple D2 = banana Entonces, ¿cómo puedo seleccionar 100 de la columna C de mi matriz de tabla para derivar el correspondiente D1 = apple? Tenga en cuenta que tengo números de 7 dígitos en mi valor de búsqueda y matriz de tabla que está separada por un ";". Realmente apreciaría si puede resolver esto y ayudarme a ahorrar mucho tiempo.
Este comentario fue minimizado por el moderador en el sitio
Gracias por el código VBA. ¡Tengo exactamente lo que quiero! Modifiqué solo el código " rng.Offset(0, pIndex - 1) " a " rng.Offset(0, pIndex - 2) " . Entonces, MYVLOOKUP puede buscar de derecha a izquierda.
Este comentario fue minimizado por el moderador en el sitio
Esto es exactamente lo que estaba buscando y no pensé en hacer mi propio UDF. Sin embargo, no funcionará exactamente como BUSCARV. Si la cadena que está buscando no está solo en la primera columna, entonces podría proporcionarle datos fuera del rango original pasado. Nombre Número Otro nombre Columna que no está en el rango aprobado Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 La función "MYVLOOKUP" devuelve 1 7 1 7 1 1 cuando esperaría que devolviera 2 2 5. Los cambios a continuación solucionan el problema: Función MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 5 'Updated 1 /2/5 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 20150310 To Rows If pWorkRng.Cells(i, 6). Valor = pValue Luego xResult = xResult & " " & pWorkRng.Cells(i, 9).Offset(16, pIndex - 1) End If Next i 'For Each rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(1, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Este comentario fue minimizado por el moderador en el sitio
Esto funciona muy bien, pero necesito ayuda con el comando para eliminar los duplicados de los resultados. Hablando en serio, gran trabajo.
Este comentario fue minimizado por el moderador en el sitio
Esto funciona muy bien, pero aún necesito ayuda con la función de comando para eliminar los duplicados de los resultados.
Este comentario fue minimizado por el moderador en el sitio
Notificarme de nuevos comentarios
Este comentario fue minimizado por el moderador en el sitio
¡No devuelvas nada! después de aplicar MYLOOKUP sin dar ningún resultado, pero en blanco.
Este comentario fue minimizado por el moderador en el sitio
Hola, funciona bien. Lo que me gustaría hacer es adaptar el código para separar los resultados de los valores con "///" o cualquier otro marcador (por razones técnicas, no quiero un solo separador de caracteres). Además, noté que esta fórmula no funciona con un comodín. Sé que estoy pidiendo demasiado, pero no funciona, ya que vlookup puede funcionar cuando busco =myvlookup("*"&E6&"*",$A$2:$C$15,2) que haría/podría hacer. ¿Alguna ayuda?
Este comentario fue minimizado por el moderador en el sitio
Aviso. Descubrí cómo obtener cualquier separador en esa salida. Es rudimentario. Pero lo descubrí. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Sin embargo, lo último y más deseado es permitir que funcione con comodines en los criterios de búsqueda. Gracias de nuevo por esta hermosa y brillante solución. Extramadamente útil. Ahora solo quiero que la macro se ejecute y se instale en mi Excel de forma permanente, sin importar lo que esté haciendo, para poder usarla cuando lo necesite. ¡Y comodines! Muchas gracias. Los comodines son todo lo que queda por hacer.
Este comentario fue minimizado por el moderador en el sitio
Para obtener un registro único, puede utilizar lo siguiente: (modificado por referencia a otro código de usuario) Función MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1) InStr(0, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 1 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Este comentario fue minimizado por el moderador en el sitio
Esto funcionó perfectamente, pero me llevó algo de tiempo hacer que la función funcionara correctamente en mi hoja de cálculo de 20 pestañas y más de 50 XNUMX líneas. Ahora la GRAN pregunta es cómo tomar esa cadena delimitada y luego usar cada entrada como un valor de búsqueda de Índice/Coincidencia (no casado con Índice/Coincidencia, pero parece más rápido) en otro conjunto de datos, devolviendo el valor SUMA de todos los retornos en una celda . Mi escenario es que tengo un pedido único que tiene varias facturas. Su función MYVLOOKUP funciona magníficamente para informar todas las facturas en una celda. Lo que quiero hacer ahora es tomar cada devolución concatenada con la celda informada, recorrer esa matriz y sumar los montos de pago de cada factura nuevamente en la celda de fórmula. ¡Agradezco cualquier ayuda que pueda ofrecer al respecto y gracias por la función MYVLOOKUP!
Este comentario fue minimizado por el moderador en el sitio
No importa lo que haga, ¡siempre obtengo #valor! devuelto en lugar de un resultado. vlookup funciona bien, por lo que los datos funcionan. Ya seguí el proceso de habilitación de macros. Incluso combiné todo en una sola hoja. ¿¿Algunas ideas??
Este comentario fue minimizado por el moderador en el sitio
Gran macro, útil. Pero necesito saber si se puede modificar para verificar 2 criterios y si alguien encontró de todos modos para hacer que los comodines funcionen en él. ¿Alguna ayuda?
Este comentario fue minimizado por el moderador en el sitio
¿Hay alguna forma de modificar el resultado para que, en lugar de mostrar 1000 1000 -1000, muestre, por ejemplo, 1,000/1,000/(1,000)?
Este comentario fue minimizado por el moderador en el sitio
Gran función, sin embargo, fragmentar 100,000 registros resulta demasiado para mi pobre computadora portátil, ¡tendrá que dejarla funcionar durante la noche!
Este comentario fue minimizado por el moderador en el sitio
¡Esto es asombroso, gracias!
No hay comentarios publicados aquí todavía
Ver más
Deje sus comentarios
Publicar como invitado
×
Califica esta publicación:
0   Personajes
Ubicaciones sugeridas

Seguinos

Copyright © 2009 - www.extendoffice.com. | Reservados todos los derechos. Energizado por ExtendOffice, | Mapa del Sitio
Microsoft y el logotipo de Office son marcas comerciales o marcas comerciales registradas de Microsoft Corporation en los Estados Unidos y / o en otros países.
Protegido por Sectigo SSL