Note: The other languages of the website are Google-translated. Back to English
Iniciar sesión  \/ 
x
or
x
Regístrate  \/ 
x

or

Función VLOOKUP con algunos ejemplos básicos y avanzados en Excel

En Excel, la función BUSCARV 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 de una columna que especificó como se muestra a continuación. . Este tutorial habla sobre cómo usar la función BUSCARV con algunos ejemplos básicos y avanzados en Excel.

Tabla de contenidos:

1. Introducción de la función BUSCARV: sintaxis y argumentos

2. Ejemplos básicos de VLOOKUP

3. Ejemplos avanzados de BUSCARV

4. Los valores coincidentes de VLOOKUP mantienen el formato de celda

5. Descargue los archivos de muestra VLOOKUP


Introducción de la función BUSCARV: sintaxis y argumentos

La sintaxis de la función BUSCARV:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Argumentos:

Valor de búsqueda: El valor que desea buscar. Debe estar en la primera columna del rango table_array.

Matriz de tabla: El rango de datos o la tabla donde se ubican la columna de valor de búsqueda y la columna de valor de resultado.

Col_index_num: El número de columna de la que se devolverá el valor coincidente. Comienza con 1 de la columna más a la izquierda en la matriz de la tabla.

Rango de búsqueda: Un valor lógico que determina si esta función BUSCARV devolverá una coincidencia exacta o una coincidencia aproximada.

  • Partido aproximado , 1 / VERDADERO: Si no se encuentra una coincidencia exacta, la fórmula busca la coincidencia más cercana: el valor más grande que es menor que el valor de búsqueda. En este caso, debe ordenar la columna de búsqueda en orden ascendente.
    = BUSCARV (lookup_value, table_array, col_index, TRUE)
    = BUSCARV (lookup_value, table_array, col_index, 1)
  • Coincidencia exacta , 0 / FALSO: Se utiliza para buscar un valor exactamente igual al valor de búsqueda. Si no se encuentra una coincidencia exacta, se devolverá el valor de error # N / A.
    = BUSCARV (lookup_value, table_array, col_index, FALSE)
    = BUSCARV (lookup_value, table_array, col_index, 0)

Notas

  • 1. La función Vlookup solo busca valor de izquierda a derecha.
  • 2. Si hay varios valores coincidentes basados ​​en el valor de búsqueda, solo se devolverá el primer coincidente mediante la función Vlookup.
  • 3. Devolverá el valor de error # N / A si no se puede encontrar el valor de búsqueda.

Ejemplos básicos de VLOOKUP

1. Haga una coincidencia exacta de Vlookup y una coincidencia aproximada de Vlookup

Haga una coincidencia exacta de Vlookup en Excel

Normalmente, si está buscando una coincidencia exacta con la función Vlookup, solo necesita usar FALSE en el último argumento.

Por ejemplo, para obtener los puntajes matemáticos correspondientes basados ​​en los números de identificación específicos, haga lo siguiente:

1. Aplique la fórmula a continuación en una celda en blanco donde desea obtener el resultado:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Y luego, arrastre el controlador de relleno hacia las celdas en las que desea llenar esta fórmula, y obtendrá los resultados que necesite. Ver captura de pantalla:

Notas

  • 1. En la fórmula anterior, F2 es el valor que desea devolver su valor coincidente, A2: D7 es la matriz de la tabla, el número 3 es el número de columna desde el que se devuelve su valor coincidente y el FALSO se refiere a la coincidencia exacta.
  • 2. Si el valor de su criterio no se encuentra en el rango de datos, se mostrará un valor de error # N / A.

Haga una coincidencia aproximada de Vlookup en Excel

La coincidencia aproximada es útil para buscar valores entre rangos de datos. Si no se encuentra la coincidencia exacta, el Vlookup aproximado devolverá el valor más grande que es menor que el valor de búsqueda.

Por ejemplo, si tiene los siguientes datos de rango, los pedidos especificados no están en la columna Pedidos, ¿cómo obtener su descuento más cercano en la columna B?

1. Ingrese la siguiente fórmula en una celda donde desee colocar el resultado:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Luego, arrastre el controlador de relleno hacia las celdas para aplicar esta fórmula, y obtendrá las coincidencias aproximadas según los valores dados, vea la captura de pantalla:

Notas

  • 1. En la fórmula anterior, D2 es el valor que desea devolver su información relativa, A2: B9 es el rango de datos, el número 2 indica el número de columna en el que se devuelve su valor coincidente y el VERDADERO se refiere a la coincidencia aproximada.
  • 2. La coincidencia aproximada devolverá el valor más grande que sea menor que su valor de búsqueda específico.
  • 3. Para utilizar la función Vlookup para obtener un valor de coincidencia aproximado, debe ordenar la columna más a la izquierda del rango de datos en orden ascendente; de ​​lo contrario, devolverá un resultado incorrecto.

2. Hacer un Vlookup sensible a mayúsculas y minúsculas en Excel

De forma predeterminada, la función Vlookup realiza una búsqueda que no distingue entre mayúsculas y minúsculas, lo que significa que trata los caracteres en minúsculas y mayúsculas como idénticos. En algún momento, es posible que deba hacer una búsqueda sensible a mayúsculas y minúsculas en Excel, las funciones de índice, coincidencia y exacta o las funciones de búsqueda y exacta pueden hacerle un favor.

Por ejemplo, tengo el siguiente rango de datos cuya columna de ID contiene una cadena de texto con mayúsculas o minúsculas, ahora quiero devolver la puntuación matemática correspondiente del número de ID dado.

Fórmula 1: uso de funciones EXACT, INDEX, MATCH

1. Ingrese o copie la fórmula de matriz a continuación en una celda en blanco donde desea obtener el resultado:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Entonces presione Ctrl + Shift + Enter teclas simultáneamente para obtener el primer resultado, y luego seleccione la celda de la fórmula, arrastre el controlador de relleno hacia las celdas en las que desea completar esta fórmula, luego obtendrá los resultados correctos que necesita. Ver captura de pantalla:

Notas

  • 1. En la fórmula anterior, A2: A10 es la columna que contiene los valores específicos que desea buscar, F2 es el valor de búsqueda, C2: C10 es la columna de donde se devolverá el resultado.
  • 2. Si se encuentran varias coincidencias, esta fórmula siempre devolverá la primera coincidencia.

Fórmula 2: uso de funciones de búsqueda y exacta

1. Aplique la fórmula siguiente en una celda en blanco donde desea obtener el resultado:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Luego, arrastre el controlador de relleno hacia las celdas en las que desea copiar esta fórmula, y obtendrá los valores coincidentes con mayúsculas y minúsculas como se muestra a continuación:

Notas

  • 1. En la fórmula anterior, A2: A10 es la columna que contiene los valores específicos que desea buscar, F2 es el valor de búsqueda, C2: C10 es la columna de donde se devolverá el resultado.
  • 2. Si se encuentran varias coincidencias, esta fórmula siempre devolverá la última coincidencia.

3. Valores de Vlookup de derecha a izquierda en Excel

La función Vlookup siempre busca un valor en la columna más a la izquierda de un rango de datos y devuelve el valor correspondiente de una columna a la derecha. Si desea hacer un Vlookup inverso, lo que significa buscar un valor específico a la derecha y devolver su valor correspondiente en la columna izquierda como se muestra a continuación:

Haga clic para conocer los detalles paso a paso sobre esta tarea ...


4. Busque el segundo, enésimo o último valor coincidente en Excel

Normalmente, si se encuentran varios valores coincidentes al usar la función Vlookup, solo se devolverá el primer registro coincidente. En esta sección, hablaré sobre cómo obtener el segundo, nésimo o último valor coincidente con la función Vlookup.

Vlookup y devuelve el segundo o enésimo valor coincidente

Suponga que tiene una lista de nombres en la columna A, el curso de capacitación que compraron en la columna B, y ahora busca encontrar el segundo o enésimo curso de capacitación comprado por el cliente dado. Ver captura de pantalla:

1. Para obtener el segundo o enésimo valor coincidente según los criterios dados, aplique la siguiente fórmula de matriz en una celda en blanco:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Entonces presione Ctrl + Shift + Enter teclas juntas para obtener el primer resultado, y luego seleccione la celda de la fórmula, arrastre el controlador de relleno hacia las celdas en las que desea completar esta fórmula, y todos los segundos valores coincidentes basados ​​en los nombres de pila se han mostrado a la vez, vea la captura de pantalla:

<b>Nota:</b>

  • En esta fórmula, A2: A14 es el rango con todos los valores para la búsqueda, B2: B14 es el rango de los valores coincidentes del que desea regresar, E2 es el valor de búsqueda y el último número 2 indica el segundo valor coincidente que desea obtener, si desea devolver el tercer valor coincidente, solo debe cambiarlo a 3 según sea necesario.

Vlookup y devuelve el último valor coincidente

Si desea visualizar y devolver el último valor coincidente como se muestra a continuación, este Vlookup y devuelve el último valor coincidente El tutorial puede ayudarlo a obtener el último valor coincidente en detalles.


5. Valores coincidentes de Vlookup entre dos valores o fechas dados

A veces, es posible que desee buscar valores entre dos valores o fechas y devolver los resultados correspondientes como se muestra a continuación en la captura de pantalla, en este caso, puede usar la función BUSCAR y una tabla ordenada.

Vlookup valores coincidentes entre dos valores o fechas dados con fórmula

1. Primero, su tabla original debe ser un rango de datos ordenados. Y luego, copie o ingrese la siguiente fórmula en una celda en blanco:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Luego, arrastre el controlador de relleno para llenar esta fórmula a otras celdas que necesita, y ahora, obtendrá todos los registros coincidentes según el valor dado, vea la captura de pantalla:

Notas

  • 1. En la fórmula anterior, A2: A6 es el rango de valores más pequeños y B2: B6 es el rango de números más grandes en su rango de datos, el E2 es el valor dado que desea obtener su valor correspondiente, C2: C6 son los datos de la columna de los que desea extraer.
  • 2. Esta fórmula también se puede utilizar para extraer valores coincidentes entre dos fechas como se muestra a continuación:

Valores coincidentes de Vlookup entre dos valores o fechas dados con una característica útil

Si le duele la fórmula anterior, aquí le presentaré una herramienta fácil: Kutools for Excel, Con su BUSCAR entre dos valores característica, puede devolver el elemento correspondiente en función del valor o fecha específicos entre dos valores o fechas sin recordar ninguna fórmula.   ¡Haga clic para descargar Kutools para Excel ahora!


6. Uso de comodines para coincidencias parciales en la función Vlookup

En Excel, los comodines se pueden usar dentro de la función Vlookup, lo que hace que se realice una coincidencia parcial en un valor de búsqueda. Por ejemplo, puede usar Vlookup para devolver un valor coincidente de una tabla en función de parte de un valor de búsqueda.

Supongamos que tengo un rango de datos como se muestra a continuación en la captura de pantalla, ahora quiero extraer la puntuación basada en el nombre (no el nombre completo). ¿Cómo podría resolver esta tarea en Excel?

1. La función normal de Vlookup no funciona correctamente, necesita unir el texto o la referencia de celda con un comodín, copie o ingrese la siguiente fórmula en una celda en blanco:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2. Luego, arrastre el controlador de relleno para completar esta fórmula a otras celdas que necesita, y todas las puntuaciones coincidentes se han devuelto como se muestra a continuación:

Notas

  • 1. En la fórmula anterior, E2 & ”*” es el valor de búsqueda, el valor en E2 y el * comodín ("*" indica cualquier carácter o cualquier carácter), A2: C11 es el rango de búsqueda, el número 3 la columna que contiene el valor a devolver.
  • 2. Vlookup al usar comodines, debe establecer el modo de coincidencia exacta con FALSE o 0 para el último argumento en la función Vlookup.

Consejos:

1. Busque y devuelva los valores coincidentes que terminen con un valor específico, aplique esta fórmula: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Para buscar y devolver el valor coincidente basado en parte de la cadena de texto, ya sea que el texto especificado esté delante, detrás o en medio de la cadena de texto, solo necesita unir dos * caracteres alrededor de la referencia de celda o texto. Hazlo con esta fórmula: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Valores de Vlookup de otra hoja de trabajo

Por lo general, es posible que tenga que trabajar con más de una hoja de trabajo, la función Vlookup se puede usar para buscar datos de otra hoja de la misma manera que en una hoja de trabajo.

Por ejemplo, tiene dos hojas de trabajo como se muestra a continuación, para buscar y devolver los datos correspondientes de la hoja de trabajo que especificó, siga los siguientes pasos:

1. Ingrese o copie la fórmula a continuación en una celda en blanco donde desea obtener los elementos coincidentes:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. Luego, arrastre el controlador de relleno hacia las celdas a las que desea aplicar esta fórmula, y obtendrá los resultados correspondientes que necesite, vea la captura de pantalla:

<b>Nota:</b> En la fórmula anterior:

  • A2 representa el valor de búsqueda;
  • Ficha de datos es el nombre de la hoja de trabajo de la que desea buscar datos, (si el nombre de la hoja contiene espacios o caracteres de puntuación, debe incluir comillas simples alrededor del nombre de la hoja; de lo contrario, puede usar directamente el nombre de la hoja como = BUSCARV (A2, Hoja de datos! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 es el rango de datos en la hoja de datos donde estamos buscando datos;
  • el número 3 es el número de columna que contiene datos coincidentes de los que desea regresar.

8. Valores de Vlookup de otro libro de trabajo

Esta sección hablará sobre la búsqueda y devolverá los valores coincidentes de un libro de trabajo diferente utilizando la función Vlookup.

Por ejemplo, el primer libro de trabajo contiene las listas de productos y costos, ahora, desea extraer el costo correspondiente en el segundo libro de trabajo según el artículo del producto como se muestra a continuación.

1. Para recuperar el costo relativo de otro libro de trabajo, primero, abra los dos libros de trabajo que desea usar, luego aplique la siguiente fórmula en una celda donde desea colocar el resultado:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2. Luego, arrastre y copie esta fórmula a otras celdas que necesite, vea la captura de pantalla:

Notas

  • 1. En la fórmula anterior:
    B2 representa el valor de búsqueda;
    [Lista de productos.xlsx] Hoja1 es el nombre del libro de trabajo y la hoja de trabajo de los que desea buscar datos (la referencia al libro de trabajo está entre corchetes y todo el libro de trabajo + la hoja está entre comillas simples);
    A2: B6 es el rango de datos en la hoja de trabajo de otro libro de trabajo donde estamos buscando datos;
    el número 2 es el número de columna que contiene datos coincidentes de los que desea regresar.
  • 2. Si el libro de trabajo de búsqueda está cerrado, la ruta completa del archivo para el libro de trabajo de búsqueda se mostrará en la fórmula como se muestra en la siguiente captura de pantalla:

9. Vlookup y devuelve texto en blanco o específico en lugar de 0 o # N / A valor de error

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 como se muestra a continuación. En lugar de mostrar el valor 0 o # N / A con una celda en blanco u otro valor que desee, este Vlookup para devolver un valor en blanco o específico en lugar de 0 o N / A El tutorial puede hacerte un favor paso a paso.


Ejemplos avanzados de BUSCARV

1. Búsqueda bidireccional con función Vlookup (Vlookup en fila y columna)

A veces, es posible que deba realizar una búsqueda bidimensional, lo que significa buscar Vlookup tanto en la fila como en la columna al mismo tiempo. Digamos, si tiene el siguiente rango de datos, y ahora, es posible que deba obtener el valor de un producto en particular en un trimestre específico. Esta sección presentará alguna fórmula para manejar este trabajo en Excel.

Fórmula 1: uso de las funciones BUSCARV y COINCIDIR

En Excel, puede utilizar una combinación de las funciones BUSCARV y COINCIDIR para realizar una búsqueda bidireccional, aplique la siguiente fórmula en una celda en blanco y luego presione Entrar clave para obtener el resultado.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

<b>Nota:</b> En la fórmula anterior:

  • H1: el valor de búsqueda en la columna en el que desea obtener el valor correspondiente basado;
  • A2: E6: el rango de datos, incluidos los encabezados de fila;
  • H2: el valor de búsqueda en la fila en el que desea obtener el valor correspondiente basado;
  • A1: E1: las celdas de los encabezados de columna.

Fórmula 2: uso de las funciones INDICE y MATCH

Aquí hay otra fórmula que también puede ayudarlo a realizar una búsqueda bidimensional, aplique la fórmula a continuación y luego presione Entrar clave para obtener el resultado que necesita.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

<b>Nota:</b> En la fórmula anterior:

  • B2: E6: el rango de datos para devolver el artículo coincidente;
  • H1: el valor de búsqueda en la columna en el que desea obtener el valor correspondiente basado;
  • A2: A6: los encabezados de las filas contienen el producto que desea buscar.
  • H2: el valor de búsqueda en la fila en el que desea obtener el valor correspondiente basado;
  • B1: E1: los encabezados de las columnas contienen el trimestre que desea buscar.

2. Valor de coincidencia de Vlookup basado en dos o más criterios

Es fácil para usted buscar el valor de coincidencia basado en un criterio, pero si tiene dos o más criterios, ¿qué puede hacer? Las funciones BÚSQUEDA o COINCIDIR e ÍNDICE en Excel pueden ayudarlo a resolver este trabajo rápida y fácilmente.

Por ejemplo, tengo la siguiente tabla de datos, para devolver el precio igualado según el producto y el tamaño específicos, las siguientes fórmulas pueden ayudarlo.

Fórmula 1: Uso de la función BUSCAR

Aplique la fórmula a continuación en una celda donde desea obtener el resultado y luego presione la tecla Entrar, vea la captura de pantalla:

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Notas

  • 1. En la fórmula anterior:
    A2: A12 = G1: significa buscar los criterios de G1 en el rango A2: A12;
    B2: B12 = G2: significa buscar los criterios de G2 en el rango B2: B12;
    D2: D12: el rango en el que desea devolver el valor correspondiente.
  • 2. Si tiene más de dos criterios, solo necesita unir los otros criterios en la fórmula, como: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Fórmula 2: uso de las funciones INDEXT Y MATCH

La combinación de la función Índice y Coincidencia también se puede utilizar para devolver el valor coincidente según varios criterios. Copie o ingrese la siguiente fórmula:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Luego, presione Ctrl + Shift + Enter juntas para obtener el valor relativo que necesita. Ver captura de pantalla:

Notas

  • 1. En la fórmula anterior:
    A2: A12 = G1: significa buscar los criterios de G1 en el rango A2: A12;
    B2: B12 = G2: significa buscar los criterios de G2 en el rango B2: B12;
    D2: D12: el rango en el que desea devolver el valor correspondiente.
  • 2. Si tiene más de dos criterios, solo necesita unir los nuevos criterios en la fórmula, como: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup para devolver múltiples valores coincidentes con una o más condiciones

En Excel, la función Vlookup busca un valor y solo devuelve el primer valor coincidente si se encuentran varios valores correspondientes. A veces, es posible que desee devolver todos los valores correspondientes en una fila, en una columna o en una sola celda. Esta sección hablará sobre cómo devolver los múltiples valores coincidentes con una o más condiciones en un libro.

Vlookup todos los valores coincidentes basados ​​en una o más condiciones horizontalmente

Vlookup todos los valores coincidentes basados ​​en una condición horizontalmente:

Para Vlookup y devolver todos los valores coincidentes basados ​​en un valor específico horizontalmente, la fórmula genérica es:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
<b>Nota</b>: m es el número de fila de la primera celda en el rango de retorno menos 1.
      n es el número de columna de la primera celda de fórmula menos 1.

1. Aplique la fórmula a continuación en una celda en blanco, luego presione Ctrl + Shift + Enter claves juntas para obtener el primer valor coincidente, vea la captura de pantalla:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Y luego, seleccione la primera celda de fórmula y arrastre el controlador de relleno a las celdas de la derecha hasta que se muestre la celda en blanco y se hayan extraído todos los elementos correspondientes, vea la captura de pantalla:

Consejos:

Si hay valores coincidentes duplicados en la lista devuelta, para ignorar los duplicados, utilice estas fórmulas y luego presione Entrar para obtener el primer resultado: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Continúe ingresando esta fórmula: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") en una celda al lado del primer resultado, y luego presione Ctrl + Shift + Enter claves juntas para obtener el segundo resultado, luego arrastre esta fórmula a las celdas de la derecha para obtener todos los demás valores coincidentes hasta que se muestre la celda en blanco, vea la captura de pantalla:


Vlookup todos los valores coincidentes basados ​​en dos o más condiciones horizontalmente:

Para Vlookup y devolver todos los valores coincidentes basados ​​en valores más específicos horizontalmente, la fórmula genérica es:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
<b>Nota</b>: m es el número de fila de la primera celda en el rango de retorno menos 1.
      n es el número de columna de la primera celda de fórmula menos 1.

1. Aplique la siguiente fórmula en una celda en blanco donde desea generar el resultado:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Luego, seleccione la celda de fórmula y arrastre el controlador de relleno a las celdas de la derecha hasta que se muestre la celda en blanco y se hayan devuelto todos los valores coincidentes basados ​​en los criterios específicos, vea la captura de pantalla:

<b>Nota</b>: Para obtener más criterios, solo necesita unir lookup_value y lookup_range en la fórmula, como: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Vlookup todos los valores coincidentes basados ​​en una o más condiciones verticalmente

Vlookup todos los valores coincidentes basados ​​en una condición verticalmente:

Para Vlookup y devolver todos los valores coincidentes basados ​​en un valor específico verticalmente, la fórmula genérica es:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
<b>Nota</b>: m es el número de fila de la primera celda en el rango de retorno menos 1.
      n es el número de fila de la primera celda de fórmula menos 1.

1. Copie o escriba la siguiente fórmula en una celda donde desea obtener el resultado y luego presione Ctrl + Shift + Enter claves juntas para obtener el primer valor coincidente, vea la captura de pantalla:

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

2. Luego, seleccione la primera celda de fórmula y arrastre el controlador de relleno hacia abajo a otras celdas hasta que se muestre la celda en blanco y todos los elementos correspondientes se hayan enumerado en una columna, vea la captura de pantalla:

Consejos:

Para ignorar los duplicados en los valores coincidentes devueltos, utilice estas fórmulas: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Entonces presione Ctrl + Shift + Enter claves juntas para obtener el primer valor coincidente, y luego arrastre esta celda de fórmula hacia abajo a otras celdas hasta que se muestre la celda en blanco, y obtendrá el resultado que necesite:


Vlookup todos los valores coincidentes basados ​​en dos o más condiciones verticalmente:

Para Vlookup y devolver todos los valores coincidentes basados ​​en valores más específicos verticalmente, la fórmula genérica es:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
<b>Nota</b>: m es el número de fila de la primera celda en el rango de retorno menos 1.
      n es el número de fila de la primera celda de fórmula menos 1.

1. Copie la siguiente fórmula en una celda en blanco y luego presione Ctrl + Shift + Enter claves juntas para obtener el primer elemento coincidente.

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

2. Luego arrastre la celda de fórmula hacia abajo a otras celdas hasta que se muestre la celda en blanco, vea la captura de pantalla:

<b>Nota</b>: Para obtener más criterios, solo necesita unir lookup_value y lookup_range en la fórmula, como: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup todos los valores coincidentes basados ​​en dos o más condiciones en una sola celda

Si desea Vlookup y devolver múltiples valores coincidentes en una sola celda con un separador especificado, la nueva función de TEXTJOIN puede ayudarlo a resolver este trabajo rápida y fácilmente.

Vlookup todos los valores coincidentes basados ​​en una condición en una sola celda:

Aplique la fórmula simple a continuación en una celda en blanco y luego presione Ctrl + Shift + Enter claves juntas para obtener el resultado:

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

Consejos:

Para ignorar los duplicados en los valores coincidentes devueltos, utilice estas fórmulas: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup todos los valores coincidentes basados ​​en dos o más condiciones en una sola celda:

Para lidiar con múltiples condiciones al devolver todos los valores coincidentes en una sola celda, aplique la siguiente fórmula y luego presione Ctrl + Shift + Enter claves juntas para obtener el resultado:

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

Notas

1. La función TEXTJOIN solo está disponible en Excel 2019 y Office 365.

2. Si usa Excel 2016 y versiones anteriores, use la función definida por el usuario de los siguientes artículos:


4. Vlookup para devolver la fila completa o completa de una celda coincidente

En esta sección, hablaré sobre cómo recuperar la fila completa de un valor coincidente usando la función Vlookup.

1. Copie o escriba la fórmula a continuación en una celda en blanco donde desea generar el resultado y presione Entrar clave para obtener el primer valor.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2. Luego, arrastre la celda de fórmula hacia el lado derecho hasta que se muestren los datos de toda la fila, vea la captura de pantalla:

<b>Nota</b>: En la fórmula anterior, F2 es el valor de búsqueda en el que desea devolver toda la fila en función, A1: D12 es el rango de datos que desea usar, A1 indica el número de la primera columna dentro de su rango de datos.

Consejos:

Si se encuentran varias filas según el valor coincidente, para devolver todas las filas correspondientes, aplique esta fórmula: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")y luego presione Ctrl + Shift + Enter teclas juntas para obtener el primer resultado, luego arrastre el controlador de relleno hacia las celdas, vea la captura de pantalla:

Y luego arrastre el controlador de relleno hacia abajo a través de las celdas para obtener todas las filas coincidentes como se muestra a continuación:


5. Hacer varias funciones de Vlookup (Vlookup anidado) en Excel

A veces, es posible que desee buscar valores en varias tablas, si alguna de las tablas contiene el valor de búsqueda dado como se muestra a continuación, en la captura de pantalla, en este caso, puede combinar una o más funciones de Vlookup junto con la función IFERROR para realizar búsquedas múltiples.

La fórmula genérica para la función Vlookup anidada es:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

<b>Nota:</b>

  • valor de búsqueda: el valor que busca;
  • Table1, Table2, Table3, ...: las tablas en las que existen el valor de búsqueda y el valor de retorno;
  • columna: el número de columna de la tabla de la que desea devolver el valor coincidente.
  • 0: Se utiliza para una coincidencia exacta.

1. Aplique la siguiente fórmula en una celda en blanco donde desea colocar el resultado:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Luego, arrastre el controlador de relleno hacia las celdas a las que desea aplicar esta fórmula, y todos los valores coincidentes se han devuelto como se muestra a continuación:

Notas

  • 1. En la fórmula anterior, J3 es el valor que busca; A3: B7, D3: E7, G3: H7 son los rangos de la tabla en los que existen el valor de búsqueda y el valor de retorno; El número 2 es el número de columna en el rango desde el que devolver el valor coincidente.
  • 2. Si no se puede encontrar el valor de búsqueda, se muestra un valor de error, para reemplazar el error con un texto legible, utilice esta fórmula: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup para verificar si el valor existe según los datos de una lista en otra columna

La función Vlookup también puede ayudarlo a verificar si existen valores basados ​​en otra lista, por ejemplo, si desea buscar los nombres en la columna C y simplemente devolver Sí o No si el nombre se encuentra o no en la columna A como la siguiente captura de pantalla mostrado.

1. Aplica la siguiente fórmula en una celda en blanco:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2. Luego, arrastre el controlador de relleno hacia las celdas en las que desea completar esta fórmula y obtendrá el resultado que necesite, vea la captura de pantalla:

<b>Nota</b>: En la fórmula anterior, C2 es el valor de búsqueda que desea verificar; A2: A10 es la lista de rango desde donde se encontrarán los valores de búsqueda; el número 1 es el número de columna desde donde desea obtener el valor en su rango.


7. Vlookup y sumar todos los valores coincidentes en filas o columnas

Si trabaja con datos numéricos, a veces, al extraer los valores coincidentes de la tabla, es posible que también deba sumar los números en varias columnas o filas. Esta sección presentará algunas fórmulas para terminar este trabajo en Excel.

Vlookup y sumar todos los valores coincidentes en una fila o varias filas

Suponga que tiene una lista de productos con ventas durante varios meses, como se muestra a continuación, ahora necesita sumar todos los pedidos de todos los meses según los productos dados.

Vlookup y sume los primeros valores coincidentes en una fila:

1. Copie o ingrese la siguiente fórmula en una celda en blanco y luego presione Ctrl + Shift + Enter claves juntas para obtener el primer resultado.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2. Luego, arrastre el controlador de relleno hacia abajo para copiar esta fórmula en otras celdas que necesita, y todos los valores en una fila del primer valor coincidente se han sumado, vea la captura de pantalla:

<b>Nota</b>: En la fórmula anterior: H2 es la celda que contiene el valor que está buscando; A2: F9 es el rango de datos (sin encabezados de columna) que incluye el valor de búsqueda y los valores coincidentes; El número 2,3,4,5,6 {} son números de columna que se utilizan para calcular el total del rango.


Vlookup y sume todos los valores coincidentes en varias filas:

La fórmula anterior solo puede sumar valores en una fila para el primer valor coincidente. Si desea sumar todas las coincidencias en varias filas, use la siguiente fórmula y luego arrastre el controlador de relleno hacia las celdas en las que desea aplicar esta fórmula, y obtendrá el resultado deseado que necesita, vea la captura de pantalla:

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

<b>Nota</b>: En la fórmula anterior: H2 es el valor de búsqueda que está buscando; A2: A9 son los encabezados de fila que contienen el valor de búsqueda; B2: F9 el rango de datos de los valores numéricos que desea sumar.


Vlookup y sume todos los valores coincidentes en una columna o varias columnas

Vlookup y sume los primeros valores coincidentes en una columna:

Si desea sumar el valor total de los meses específicos como se muestra en la captura de pantalla a continuación.

Aplique la fórmula a continuación en una celda en blanco y luego arrastre el controlador de relleno hacia abajo para copiar esta fórmula en otras celdas, ahora, los primeros valores coincidentes basados ​​en el mes específico en una columna se han sumado, vea la captura de pantalla:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

<b>Nota</b>: En la fórmula anterior: H2 es el valor de búsqueda que está buscando; B1: F1 son los encabezados de columna que contienen el valor de búsqueda; B2: F9 el rango de datos de los valores numéricos que desea sumar.


Vlookup y sume todos los valores coincidentes en varias columnas:

Para Vlookup y sumar todos los valores coincidentes en varias columnas, debe usar la siguiente fórmula:

=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))

<b>Nota</b>: En la fórmula anterior: H2 es el valor de búsqueda que está buscando; B1: F1 son los encabezados de columna que contienen el valor de búsqueda; B2: F9 el rango de datos de los valores numéricos que desea sumar.


Vlookup y sume los primeros valores coincidentes o todos los valores coincidentes con una característica poderosa

Tal vez las fórmulas anteriores le resulten difíciles de recordar, en este caso, recomendaré una función útil: Búsqueda y suma of Kutools for Excel, con esta función, puede obtener el resultado lo más fácil posible.    ¡Haga clic para descargar Kutools para Excel ahora!


Vlookup y suma todos los valores coincidentes tanto en filas como en columnas

Si desea sumar los valores cuando necesita hacer coincidir tanto la columna como la fila, por ejemplo, para obtener el valor total del producto Sweater en el mes de marzo, como se muestra a continuación.

Aplique la siguiente fórmula en una celda y luego presione la tecla Enter para obtener el resultado, vea la captura de pantalla:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

<b>Nota</b>: En la fórmula anterior: B2: F9 es el rango de datos de los valores numéricos que desea sumar; B1: F1 is los encabezados de columna contienen el valor de búsqueda en el que desea sumar; I2 es el valor de búsqueda dentro de los encabezados de columna que está buscando; A2: A9 ¿Los encabezados de fila contienen el valor de búsqueda en el que desea sumar? H2 es el valor de búsqueda dentro de los encabezados de fila que está buscando.


8. Vlookup para fusionar dos tablas basadas en una o más columnas clave

En su trabajo diario, al analizar los datos, es posible que deba recopilar toda la información necesaria en una sola tabla basada en una o más columnas clave. Para solucionar este problema, la función Vlookup también puede hacerte un favor.

Vlookup para fusionar dos tablas basadas en una columna clave

Por ejemplo, tiene dos tablas, la primera tabla contiene los datos de productos y nombres, y la segunda tabla contiene los productos y pedidos; ahora, desea combinar estas dos tablas haciendo coincidir la columna de producto común en una tabla.

Fórmula 1: uso de la función BUSCARV

Para fusionar las dos tablas en una basada en una columna clave, aplique la siguiente fórmula en una celda en blanco donde desea obtener el resultado, y luego arrastre el controlador de relleno hacia las celdas en las que desea aplicar esta fórmula, lo hará obtenga una tabla combinada con la columna de orden que se une a los datos de la primera tabla en función de los datos de la columna clave.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

<b>Nota</b>: En la fórmula anterior, A2 es el valor que busca, E2: F8 es la tabla para buscar, el número 2 es el número de columna de la tabla de la que se recupera el valor.

Fórmula 2: uso de las funciones INDICE y MATCH

Si sus datos comunes en el lado derecho y los datos devueltos en la columna izquierda dentro de la segunda tabla, para combinar la columna de orden, la función Vlookup no puede hacer el trabajo. Para mirar hacia arriba de derecha a izquierda, puede usar las funciones INDICE y COINCIDIR para reemplazar la función Vlookup.

Copie o ingrese la fórmula a continuación en una celda en blanco, luego copie la fórmula en la columna y la columna de orden se unió a la primera tabla, vea la captura de pantalla:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

<b>Nota</b>: En la fórmula anterior, A2 es el valor de búsqueda que busca, E2: E8 es el rango de datos que desea devolver, F2: F8 es el rango de búsqueda que contiene el valor de búsqueda.


Vlookup para fusionar dos tablas basadas en múltiples columnas clave

Si las dos tablas que desea unir tienen varias columnas clave, para fusionar las tablas en función de estas columnas comunes, las funciones INDICE y COINCIDIR pueden ayudarlo.

La fórmula genérica para fusionar dos tablas basadas en múltiples columnas clave es:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Aplique la fórmula a continuación en una celda en blanco donde desea colocar el resultado y luego presione Ctrl + Shift + Enter claves juntas para obtener el primer valor coincidente, vea la captura de pantalla:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

<b>Nota</b>: En la fórmula anterior, lo que representan las referencias de celda como se muestra a continuación:

2Luego, seleccione la primera celda de fórmula y arrastre el controlador de relleno para copiar esta fórmula en otras celdas que necesite:

Consejos: En Excel 2016 y versiones posteriores, también puede usar el Power Query función para fusionar dos o más tablas en una basada en columnas clave. Haga clic para conocer los detalles paso a paso.

9. Valores coincidentes de Vlookup en varias hojas de trabajo

¿Alguna vez ha intentado Vlookup valores en varias hojas de trabajo? Supongamos que tengo las siguientes tres hojas de trabajo con rango de datos, y ahora, quiero obtener parte de los valores correspondientes según los criterios de estas tres hojas de trabajo para obtener el resultado como se muestra a continuación. En este caso, el Valores de Vlookup en varias hojas de trabajo El tutorial puede hacerte un favor paso a paso.


Los valores coincidentes de VLOOKUP mantienen el formato de celda

1. Vlookup para obtener el formato de celda (color de celda, color de fuente) junto con el valor de búsqueda

Como todos sabemos, la función normal de Vlookup solo puede ayudarnos a devolver el valor coincidente de otro rango de datos, pero a veces, es posible que desee devolver el valor correspondiente junto con el formato de celda, como el color de relleno, el color de fuente, el estilo de fuente como se muestra a continuación. Esta sección hablará sobre cómo obtener el formato de celda con el valor devuelto en Excel.

Siga los siguientes pasos para buscar y devolver su valor correspondiente junto con el formato de celda:

1. En la hoja de trabajo contiene los datos que desea Vlookup, haga clic derecho en la pestaña de la hoja y seleccione Ver código desde el menú contextual. Ver captura de pantalla:

2. En el abierto Microsoft Visual Basic para aplicaciones ventana, copie el código VBA a continuación en la ventana Código.

Código VBA 1: Vlookup para obtener el formato de celda junto con el valor de búsqueda

Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
                Range(xDic.Keys(I)).Font.FontStyle = _
                Range(xDic.Items(I)).Font.FontStyle
                Range(xDic.Keys(I)).Font.Size = _
                Range(xDic.Items(I)).Font.Size
                Range(xDic.Keys(I)).Font.Color = _
                Range(xDic.Items(I)).Font.Color
                Range(xDic.Keys(I)).Font.Name = _
                Range(xDic.Items(I)).Font.Name
                Range(xDic.Keys(I)).Font.Underline = _
                Range(xDic.Items(I)).Font.Underline
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. Todavía en el Microsoft Visual Basic para aplicaciones ventana, haga clic recuadro > Módulo, y luego copie el código 2 de VBA a continuación en la ventana del Módulo.

Código VBA 2: Vlookup para obtener el formato de celda junto con el valor de búsqueda

Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. Después de insertar los códigos anteriores, haga clic en Las herramientas > Referencias en el objeto Microsoft Visual Basic para aplicaciones ventana. Entonces revisa el Tiempo de ejecución de Microsoft Script casilla de verificación en el Referencias - VBAProject caja de diálogo. Ver capturas de pantalla:

5. Luego, haz clic OK para cerrar el cuadro de diálogo, y luego guardar y cerrar la ventana del código, ahora, regrese a la hoja de trabajo y luego aplique esta fórmula: =LookupKeepFormat(E2,$A$1:$C$10,3) en una celda en blanco donde desea generar el resultado, y luego presione la tecla Enter. Ver captura de pantalla:

<b>Nota</b>: En la fórmula anterior, E2 es el valor que buscarás, A1: C10 es el rango de la tabla y el número 3 es el número de columna de la tabla en la que desea que se devuelva el valor coincidente.

6. Luego, seleccione la primera celda de resultados y arrastre el controlador de relleno hacia abajo para obtener todos los resultados junto con su formato. Ver captura de pantalla.


2. Mantenga el formato de fecha de un valor devuelto de Vlookup

Normalmente, cuando se usa la función Vloook para buscar y devolver el valor del formato de fecha coincidente, se mostrará algún formato de número como se muestra a continuación. Para mantener el formato de fecha de un resultado devuelto, debe adjuntar la función TEXT a la función Vlookup.

Aplique la fórmula a continuación en una celda en blanco y luego arrastre el controlador de relleno para copiar esta fórmula a otras celdas, y todas las fechas coincidentes se han devuelto como se muestra a continuación:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

<b>Nota</b>: En la fórmula anterior, E2 es el valor de la apariencia, A2: C9 es el rango de búsqueda, el número 3 es el número de columna que desea que se devuelva, mm/dd/yyy es el formato de fecha que desea mantener.


3. Vlookup y devuelve el valor coincidente con el comentario de la celda

¿Alguna vez ha intentado que Vlookup devuelva no solo los datos de la celda coincidente, sino también el comentario de la celda en Excel, como se muestra en la siguiente captura de pantalla? Para resolver esta tarea, la siguiente función definida por el usuario puede hacerle un favor.

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

2. Hacer clic recuadro > Módulo, luego copie y pegue el siguiente código en la ventana del módulo.

Código de VBA: Vlookup y devuelve el valor coincidente con el comentario de la celda:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. Luego guarde y cierre la ventana de código, ingrese esta fórmula: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) en una celda en blanco para ubicar el resultado, y luego arrastre el controlador de relleno para copiar esta fórmula a otras celdas, ahora, los valores coincidentes y los comentarios se devuelven a la vez, vea la captura de pantalla:

<b>Nota</b>: En la fórmula anterior, D2 es el valor de búsqueda que desea devolver su valor correspondiente, A2: B9 es la tabla de datos que desea utilizar, el número 2 es el número de columna que contiene el valor coincidente que desea devolver.


4. Manejar el texto y los números reales en Vlookup

Por ejemplo, tengo un rango de datos, el número de identificación en la tabla original es el formato numérico, en la celda de búsqueda que se almacena como texto, cuando se aplica la función normal de Vlookup, se muestra un resultado de error # N / A como la siguiente captura de pantalla mostrado. En este caso, ¿cómo podría obtener la información correcta si el número de búsqueda y el número original en la tabla tienen un formato de datos diferente?

Para tratar con el texto y los números reales en la función Vlookup, aplique la siguiente fórmula en una celda en blanco y luego arrastre el controlador de relleno hacia abajo para copiar esta fórmula, y obtendrá los resultados correctos como se muestra a continuación:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Notas

  • 1. En la fórmula anterior, D2 es el valor de búsqueda que desea devolver su valor correspondiente, A2: B8 es la tabla de datos que desea utilizar, el número 2 es el número de columna que contiene el valor coincidente que desea devolver.
  • 2. Esta fórmula también funciona bien si no está seguro de dónde tiene números y dónde tiene texto.

Descargar archivos de muestra VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • 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
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.