Saltar al contenido principal

Vlookup y devuelve múltiples valores basados ​​en uno o múltiples criterios

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 todos los valores correspondientes verticalmente

Vlookup y devuelve todos los valores correspondientes horizontalmente

Vlookup y devuelve todos los valores correspondientes en una celda


Vlookup y devuelve todos los valores correspondientes verticalmente

Para devolver todos los valores coincidentes verticalmente según un criterio específico, aplique la siguiente fórmula de matriz:

1. Ingrese o copie esta fórmula en una celda en blanco donde desea generar el resultado:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Note: En la fórmula anterior, C2: C20 es la columna que contiene el registro coincidente que desea devolver; A2: A20 es la columna que contiene el criterio; y E2 es el criterio específico en el que desea devolver valores basados. Cámbielos según sus necesidades.

2. Entonces presione Ctrl + Shift + Enter teclas juntas para obtener el primer valor, y luego arrastre el controlador de relleno hacia abajo para obtener todos los registros correspondientes que necesite, vea la captura de pantalla:

Consejos:

Para Vlookup y devolver todos los valores coincidentes basados ​​en valores más específicos verticalmente, aplique la fórmula a continuación y presione Ctrl + Shift + Enter llaves.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )


Vlookup y devuelve todos los valores correspondientes horizontalmente

Si desea que los valores coincidentes se muestren en orden horizontal, la siguiente fórmula de matriz puede ayudarlo.

1. Ingrese o copie esta fórmula en una celda en blanco donde desea generar el resultado:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Note: En la fórmula anterior, C2: C20 es la columna que contiene el registro coincidente que desea devolver; A2: A20 es la columna que contiene el criterio; y F1 es el criterio específico en el que desea devolver valores basados. Cámbielos según sus necesidades.

2. Entonces presione Ctrl + Shift + Enter claves juntas para obtener el primer valor, y luego arrastre el controlador de relleno hacia la derecha para obtener todos los registros correspondientes que necesite, vea la captura de pantalla:

Consejos:

Para Vlookup y devolver todos los valores coincidentes en función de valores más específicos horizontalmente, aplique la fórmula a continuación y presione Ctrl + Shift + Enter llaves.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")


Vlookup y devuelve todos los valores correspondientes en una celda

Para visualizar y devolver todos los valores correspondientes en una sola celda, debe aplicar la siguiente fórmula de matriz.

1. Ingrese o copie la siguiente fórmula en una celda en blanco:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Note: En la fórmula anterior, C2: C20 es la columna que contiene el registro coincidente que desea devolver; A2: A20 es la columna que contiene el criterio; y F1 es el criterio específico en el que desea devolver valores basados. Cámbielos según sus necesidades.

2. Entonces presione Ctrl + Shift + Enter claves juntas para obtener todos los valores coincidentes en una sola celda, vea la captura de pantalla:

Consejos:

Para Vlookup y devolver todos los valores coincidentes basados ​​en valores más específicos en una sola celda, aplique la siguiente fórmula y presione Ctrl + Shift + Enter llaves.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Nota: Esta fórmula solo se aplicó con éxito en Excel 2016 y versiones posteriores. Si no tiene Excel 2016, consulte aquí para bajarlo.

Artículos más relativos de Vlookup:

  • 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 como se muestra en la siguiente captura de pantalla? En este artículo, presentaré la solución paso a paso.
  • Vlookup para devolver en blanco en lugar de 0 o N / A en Excel
  • Normalmente, cuando aplica la función vlookup para devolver el valor correspondiente, si su celda coincidente está en blanco, devolverá 0, y si no se encuentra su valor coincidente, obtendrá un valor de error # N / A. En lugar de mostrar el valor 0 o # N / A, ¿cómo puede hacer que muestre una celda en blanco?
  • Vlookup para devolver múltiples columnas de la tabla de Excel
  • En la hoja de cálculo de Excel, puede aplicar la función Vlookup para devolver el valor coincidente de una columna. Pero, a veces, es posible que deba extraer valores coincidentes de varias columnas como se muestra en la siguiente captura de pantalla. ¿Cómo podría obtener los valores correspondientes al mismo tiempo de múltiples columnas usando la función Vlookup?
  • Valores de Vlookup en varias hojas de trabajo
  • En Excel, podemos aplicar fácilmente la función vlookup para devolver los valores coincidentes en una sola tabla de una hoja de trabajo. Pero, ¿alguna vez ha considerado cómo visualizar el valor en varias hojas de trabajo? Supongamos que tengo las siguientes tres hojas de trabajo con un rango de datos, y ahora quiero obtener parte de los valores correspondientes según los criterios de estas tres hojas de trabajo.

  • 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 y conservación de datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... 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 ...
  • Fórmulas favoritas e insertar rápidamente, Rangos, gráficos e imágenes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • 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...
  • Agrupación de tablas dinámicas por número de semana, día de la semana y más ... Mostrar celdas bloqueadas y desbloqueadas por diferentes colores; Resalte las celdas que tienen fórmula / nombre...
pestaña kte 201905
  • 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
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations