Estos ejemplos le enseñarán cómo Vlookup varios criterios, devolver una instancia específica o todas las coincidencias, hacer Vlookup dinámico en varias hojas y más.
Es la segunda parte de la serie que le ayudará a aprovechar el poder de Excel VLOOKUP. Los ejemplos implican que sabe cómo funciona esta función. Si no, es lógico comenzar con los usos básicos de BUSCARV en Excel.
Antes de continuar, permítame recordarle brevemente la sintaxis:
Ahora que todos están en la misma página, echemos un vistazo más de cerca a los ejemplos de fórmulas avanzadas de VLOOKUP:
- Cómo Vlookup de varios criterios en Excel
- Vlookup y devuelve la enésima coincidencia
- Vlookup múltiples valores
- Vlookup basado en valores de fila y columna
- Fórmula de Vlookup anidada
- VLOOKUP e INDIRECT para extraer datos de varias hojas
Cómo Vlookup múltiples criterios
La función VLOOKUP de Excel es realmente útil cuando se trata de buscar en una base de datos para un cierto valor. Sin embargo, carece de una característica importante: su sintaxis permite solo un valor de búsqueda. Pero, ¿qué sucede si desea buscar con varias condiciones? Hay algunas soluciones diferentes entre las que puede elegir.
Fórmula 1. VLOOKUP basado en tw o valores
Suponga que tiene una lista de pedidos y desea encontrar la cantidad según 2 criterios, Nombre del cliente y Producto. Un factor que complica la situación es que cada cliente pidió varios productos, como se muestra en la tabla siguiente:
Una fórmula VLOOKUP habitual no funcionará en este situación porque devuelve la primera coincidencia encontrada en función de un único valor de búsqueda que especifique.
Para superar esto, puede agregar una columna auxiliar y concatenar los valores de dos columnas de búsqueda (Cliente y Producto) allí. Es importante que la columna auxiliar sea la columna más a la izquierda en la matriz de la tabla porque es donde Excel VLOOKUP siempre busca el valor de búsqueda.
Por lo tanto, agregue una columna a la izquierda de su tabla y copie la fórmula siguiente en esa columna. Esto llenará la columna auxiliar con los valores de las columnas B y C (el carácter de espacio está concatenado entre ellos para una mejor legibilidad):
=B2&" "&C2
Y luego, use una fórmula VLOOKUP estándar y coloque ambos criterios en el argumento lookup_value, separados por un espacio:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
O ingrese los criterios en celdas separadas (G1 y G2 en nuestro caso) y concatene esas celdas:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Como queremos devolver un valor de la columna D, que es el cuarto en la matriz de la tabla, usamos 4 para col_index_num. El argumento range_lookup se establece en FALSE para Vlookup una coincidencia exacta. La siguiente captura de pantalla muestra el resultado:
En caso de que su tabla de búsqueda esté en otra hoja, incluya el nombre de la hoja en su fórmula VLOOKUP. Por ejemplo:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativamente, cree un rango con nombre para la tabla de búsqueda (por ejemplo, Pedidos) para facilitar la fórmula. para leer:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Para obtener más información, consulte Cómo Vlookup desde otra hoja en Excel.
Fórmula 2. VLOOKUP de Excel con múltiples condiciones
En teoría, puede utilizar el enfoque anterior para Vlookup más de dos criterios. H Sin embargo, hay un par de advertencias. En primer lugar, un valor de búsqueda está limitado a 255 caracteres y, en segundo lugar, es posible que el diseño de la hoja de trabajo no permita agregar una columna de ayuda.
Afortunadamente, Microsoft Excel a menudo proporciona más de una forma de hacer lo mismo. Para Vlookup múltiples criterios, puede usar una combinación de COINCIDIR ÍNDICE o la función XLOOKUP introducida recientemente en Office 365.
Por ejemplo, para buscar en función de 3 valores diferentes (Fecha, Nombre del cliente y Producto), utilice una de las siguientes fórmulas:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Donde:
Para obtener una explicación detallada de las fórmulas, consulte:
- XLOOKUP con múltiples criterios
- Fórmula INDEX MATCH con múltiples criterios
Cómo usar BUSCARV para obtener la segunda, tercera o enésima coincidencia
Como ya sabe, BUSCARV de Excel solo puede obtener un valor coincidente, más precisamente, devuelve la primera coincidencia encontrada. Pero, ¿qué pasa si hay varias coincidencias en su matriz de búsqueda y desea obtener la segunda o la tercera instancia? La tarea suena bastante compleja, ¡pero la solución existe!
Fórmula 1. Vlookup Nth instancia
Suponga que tiene los nombres de los clientes en una columna, los productos que compraron en otra y usted están buscando encontrar el segundo o tercer producto comprado por un cliente determinado.
La forma más sencilla es agregar una columna de ayuda a la izquierda de la tabla como hicimos en el primer ejemplo. Pero esta vez, lo completaremos con nombres de clientes y números de ocurrencia como «John Doe1», «John Doe2», etc.
Para obtener la ocurrencia, use la función COUNTIF con una referencia de rango mixto (el la primera referencia es absoluta y la segunda es relativa como $ B $ 2: B2). Dado que la referencia relativa cambia según la posición de la celda donde se copia la fórmula, en la fila 3 se convertirá en $ B $ 2: B3, en la fila 4 – $ B $ 2: B4, y así sucesivamente.
Concatenado con el nombre del cliente (B2), la fórmula toma esta forma:
=B2&COUNTIF($B$2:B2, B2)
La fórmula anterior va a A2, y luego lo copia en tantas celdas como sea necesario.
Después de eso, ingrese el nombre del objetivo y el número de ocurrencia en celdas separadas (F1 y F2), y use la siguiente fórmula para Vlookup una ocurrencia específica:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Fórmula 2. Segunda aparición de Vlookup
Si está buscando la segunda instancia del valor de búsqueda, puede prescindir de la columna auxiliar. En su lugar, cree la matriz de la tabla dinámicamente utilizando la función INDIRECTA junto con COINCIDIR:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Donde:
- E1 es el valor de búsqueda
- A2: A11 es el rango de búsqueda
- B11 es la última celda (abajo a la derecha) de la tabla de búsqueda
Tenga en cuenta que la fórmula anterior está escrita para un caso específico donde las celdas de datos en la tabla de búsqueda comienzan en la fila 2. Si su tabla está en algún lugar en el medio de la hoja, use esta fórmula universal, donde A1 es la celda superior izquierda de la tabla de búsqueda que contiene un encabezado de columna:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Cómo funciona esta fórmula
Aquí está la parte clave de la fórmula que crea un rango dinámico de vlookup:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
La función COINCIDIR configurada para coincidencia exacta (0 en el último argumento) compara el nombre del objetivo (E1) con la lista de nombres (A2: A11) y devuelve la posición de la primera coincidencia encontrada, que es 3 en o tu caso. Este número se utilizará como la coordenada de fila inicial para el rango de vlookup, por lo que le agregamos 2 (+1 para excluir la primera instancia y +1 para excluir la fila 1 con los encabezados de columna). Alternativamente, puede usar 1 + FILA (A1) para calcular el ajuste necesario automáticamente según la posición de la fila del encabezado (A1 en nuestro caso).
Como resultado, obtenemos la siguiente cadena de texto, que INDIRECTO convierte en una referencia de rango:
INDIRECT("A"&5&":B11") -> A5:B11
Este rango va al argumento table_array de BUSCARV, lo que lo obliga a comenzar a buscar en la fila 5, dejando fuera la primera instancia del valor de búsqueda:
VLOOKUP(E1, A5:B11, 2, FALSE)
Cómo Vlookup y devolver múltiples valores en Excel
La función BUSCARV de Excel está diseñada para devolver solo una coincidencia. ¿Hay alguna forma de Vlookup en varias instancias? Sí, la hay, aunque no fácil. Esto requiere un uso combinado de varias funciones como INDICE, SMALL y ROW es una fórmula de matriz.
Por ejemplo, a continuación se pueden encontrar todas las apariciones del valor de búsqueda F2 en el rango de búsqueda B2: B16 y devolver múltiples coincidencias de la columna C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Hay 2 formas de ingresar la fórmula en su hoja de trabajo:
- Escriba la fórmula en la primera celda, presione Ctrl + Shift + Enter, y luego arrástrela hacia abajo hasta algunas celdas más.
- Seleccione varias celdas adyacentes en una sola columna (F1: F11 en la captura de pantalla a continuación), escriba la fórmula y presione Ctrl + Shift + Enter para completarla.
De cualquier manera, el número de celdas en las que ingresa la fórmula debe ser igual o mayor que el número máximo de coincidencias posibles.
Para obtener una explicación detallada de la lógica de la fórmula y más ejemplos, consulte Cómo BUSCAR V múltiples valores en Excel .
Cómo Vlookup en filas y columnas (búsqueda bidireccional)
La búsqueda bidireccional (también conocida como búsqueda de matriz o búsqueda bidimensional) es una palabra elegante para buscar un valor en la intersección de una determinada fila y columna.Hay algunas formas diferentes de realizar búsquedas bidimensionales en Excel, pero como el enfoque de este tutorial está en la función BUSCARV, naturalmente la usaremos.
Para este ejemplo, tomaremos el la siguiente tabla con las ventas mensuales y elabore una fórmula VLOOKUP para recuperar la cifra de ventas de un artículo específico en un mes determinado.
Con los nombres de los artículos en A2: A9, los nombres de los meses en B1: F1, el artículo de destino en I1 y el mes objetivo en I2, la fórmula es la siguiente:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Cómo funciona esta fórmula
El núcleo de la fórmula es la función VLOOKUP estándar que busca una coincidencia exacta con el valor de búsqueda en I1. Pero como no lo sabemos en qué columna exactamente están las ventas de un mes específico, no podemos proporcionar el número de columna directamente al argumento col_index_num. Para encontrar esa columna, usamos la siguiente función COINCIDIR:
MATCH(I2, A1:F1, 0)
Traducido al inglés, la fórmula dice: busque el valor I2 en A1: F1 y devuelve su posición relativa en la matriz. Al proporcionar 0 al tercer argumento, le indica a MATCH que encuentre el valor exactamente igual al valor de búsqueda (es como usar FALSE para el argumento range_lookup de BUSCARV).
Dado que Mar está en la cuarta columna en la matriz de búsqueda, la función COINCIDIR devuelve 4, que va directamente al argumento col_index_num de BUSCARV:
VLOOKUP(I1, A2:F9, 4, FALSE)
Preste atención que aunque los nombres de los meses comienzan en la columna B, usamos A1: I1 para la matriz de búsqueda. Esto se hace para que el número devuelto por MATCH corresponda a la posición de la columna en la matriz_tabla de BUSCARV.
Para conocer más formas de realizar búsquedas de matrices en Excel, consulte INDEX MATCH MATCH y otras fórmulas para búsquedas bidimensionales.
Cómo hacer múltiples Vlookup en Excel (Vlookup anidado)
A veces puede suceder que su tabla principal y la tabla de búsqueda no tengan una sola columna en común, lo que le impide hacer una Vlookup entre dos tablas. Sin embargo, existe otra tabla, que no contiene la información que está buscando, pero tiene una columna común con la tabla principal y otra columna común con la tabla de búsqueda.
En la siguiente imagen se ilustra la situación:
El objetivo es copiar los precios en la tabla principal según los ID de artículo. El problema es que la tabla que contiene los precios no tiene los ID de artículo, lo que significa que tendremos que hacer dos Vlookups en una fórmula.
Por conveniencia, creemos primero un par de rangos con nombre :
- La tabla de búsqueda 1 se llama Productos (D3: E3).
- La tabla de búsqueda 2 se llama Precios (G3: H3).
Las tablas pueden estar en la misma hoja de trabajo o en hojas de trabajo diferentes.
Y ahora, realizaremos el llamado Vlookup doble, también conocido como Vlookup anidado.
Primero, haga un VLOOKUP fórmula para encontrar el nombre del producto en la tabla de búsqueda 1 (productos denominados) según el ID del artículo (A3):
=VLOOKUP(A3, Products, 2, FALSE)
A continuación, coloque la fórmula anterior en el argumento lookup_value de otra función BUSCARV para extraer precios de la tabla de búsqueda 2 (denominada Precios) según el nombre del producto devuelto por BUSCARV anidado:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
La captura de pantalla siguiente muestra nuestra fórmula de Vlookup anidada en acción:
Cómo Vlookup múltiples hojas de forma dinámica
A veces, puede tener datos en el mismo formato divididos en varias hojas de trabajo. Y su objetivo es extraer datos de una hoja específica según el valor de la clave en una celda determinada.
Esto puede ser más fácil de entender con un ejemplo. Supongamos que tiene algunos informes de ventas regionales en el mismo formato y desea obtener las cifras de ventas de un producto específico en determinadas regiones:
Como en el ejemplo anterior, comenzamos definiendo algunos nombres:
- El rango A2: B5 en la hoja de CA se llama CA_Sales.
- Rango A2 : B5 en la hoja FL se llama FL_Sales.
- El rango A2: B5 en la hoja KS se llama KS_Sales.
Como puede ver, todos los rangos nombrados tienen un parte (Ventas) y partes únicas (CA, FL, KS). Asegúrese de nombrar sus rangos de manera similar, ya que es esencial para la fórmula que vamos a crear.
Fórmula 1. BUSCARV INDIRECTA para extraer datos de diferentes hojas de forma dinámica
Si su tarea es recuperar datos de varias hojas, una fórmula BUSCARV INDIRECTA es la mejor solución: compacta y fácil de entender.
Para este ejemplo, organizamos la tabla resumen de esta manera:
- Ingrese los productos de interés en A2 y A3. Esos son nuestros valores de búsqueda.
- Ingrese las partes únicas de los rangos nombrados en B1, C1 y D1.
Y ahora, concatenamos la celda que contiene la parte única (B1) con la parte común («_Sales»), y alimentamos la cadena resultante a INDIRECT:
INDIRECT(B$1&"_Sales")
La función INDIRECTA transforma la cadena en un nombre que Excel puede entender, y lo coloca en el argumento table_array de BUSCARV:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
La fórmula anterior va a B2 y luego la copia hacia abajo y hacia la derecha.
Por favor, preste atención a que, en el valor de búsqueda ($ A2), hemos bloqueado la coordenada de la columna con la referencia de celda absoluta para que la columna permanezca fija cuando la fórmula se copia a la derecha. En la referencia de B $ 1, bloqueamos la fila porque queremos la columna coordinar para cambiar y proporcionar una parte de nombre apropiada a INDIRECTO dependiendo de la columna en la que se copia la fórmula:
Si su tabla principal está organizada de manera diferente, los valores de búsqueda en una fila y las partes únicas de los nombres de rango en una columna mn, entonces debe bloquear la coordenada de la fila en el valor de búsqueda (B $ 1) y la coordenada de la columna en las partes del nombre ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Fórmula 2. VLOOKUP e IF anidados para buscar varias hojas
En una situación en la que acaba de dos o tres hojas de búsqueda, puede usar una fórmula VLOOKUP bastante simple con funciones SI anidadas para seleccionar la hoja correcta en función del valor clave en una celda en particular:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Donde $ A2 es el valor de búsqueda (nombre del elemento) y B $ 1 es el valor clave (estado):
En este caso, no es necesario que defina nombres y puede utilizar referencias externas para hacer referencia a otra hoja o libro de trabajo.
Para obtener más ejemplos de fórmulas, consulte Cómo buscar V en varias hojas en Excel .
Así es como se usa BUSCARV en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Libro de práctica para descargar
Ejemplos de fórmulas avanzadas de BUSCARV (archivo .xlsx)
- Tutorial de VLOOKUP de Excel para principiantes
- Cómo buscar múltiples coincidencias en Excel
- Cómo usar VLOOKUP & funciones SUM o SUMIF en Excel
- INDEX & Funciones COINCIDIR en Excel: una mejor alternativa a VLOOKUP
- 4 formas de hacer una Vlookup que distingue entre mayúsculas y minúsculas en Excel
- Excel VLOOKUP no funciona: errores N / A, NAME y VALUE