Cómo Usar La Función BUSCARV En Excel

¿Te gustaría saber cómo usar la función BUSCARV en Excel? En este tutorial te mostraremos cómo usar la función VLOOKUP para copiar datos de otra hoja de trabajo o libro de trabajo, Vlookup en varias hojas y buscar dinámicamente para devolver valores de diferentes hojas a diferentes celdas.

Al buscar información en Excel, es raro que todos los datos estén en la misma hoja. Más a menudo, tendrás que buscar en varias hojas o incluso en diferentes libros de trabajo. La buena noticia es que Microsoft Excel proporciona más de una forma de hacer esto, y la mala noticia es que todas las formas son un poco más complicadas que una fórmula VLOOKUP estándar. Pero con un poco de paciencia, aprenderás.

Cómo usar la función BUSCARV en Excel entre dos hojas

Para empezar, pondremos un caso simple: usar la función BUSCARV en Excel para copiar datos de otra hoja de trabajo. Es muy similar a una fórmula VLOOKUP normal que busca en la misma hoja de trabajo. La diferencia es que incluye el nombre de la hoja en el argumento table_array para indicarle a tu fórmula en qué hoja de trabajo se encuentra el rango de búsqueda.

Aquí puedes aprender sobre: Cómo Mejorar Tú Experiencia En Excel – 13 Útiles Consejos

La fórmula genérica para la función BUSCARV en Excel de otra hoja es la siguiente:

• VLOOKUP (lookup_value, Sheet! Range, col_index_num, [range_lookup])

Como ejemplo, vamos a extraer las cifras de ventas del informe de enero a la hoja Resumen. Para ello, debes definir los siguientes argumentos:

• Los valores de búsqueda están en la columna A de la hoja Resumen y nos referimos a la primera celda de datos, que es A2.
• Table_arrayes el rango A2: B6 en la hoja de enero. Para hacer referencia a él, debes anteponer la referencia del rango con el nombre de la hoja seguido del signo de exclamación: Jan! $ A $ 2: $ B $ 6.

NOTA: Aquí debes prestar atención a que bloqueas el rango con referencias de celda absolutas para evitar que cambie al copiar la fórmula a otras celdas.

• Col_index_numes 2 porque quieres copiar un valor de la columna B, que es la segunda columna en la matriz de la tabla.
• Range_lookupse establece en FALSE para buscar una coincidencia exacta.

Juntando los argumentos, obtienes esta fórmula:

• =VLOOKUP(A2, Jan!$A$2:$B$6, 2, FALSE)

Arrastra la fórmula hacia abajo en la columna y tendrás este resultado:

De manera similar, puede hacer el uso de la función Vlookup datos de las hojas de febrero y marzo:

• =VLOOKUP(A2, Feb!$A$2:$B$6, 2, FALSE)
• =VLOOKUP(A2, Mar!$A$2:$B$6, 2, FALSE)

Consejos y notas sobre la función BUSCARV en Excel:

• Si el nombre de la hoja contiene espacioso caracteres no alfabéticos, debe incluirse entre comillas simples, como ‘Ventas de enero’! $ A $ 2: $ B $ 6.
• En lugar de escribir el nombre de una hoja directamente en una fórmula, puedes cambiar a la hoja de trabajo de búsqueda y seleccionar el rango allí. Excel insertará una referencia con la sintaxis correcta automáticamente, lo que te evitará la molestia de verificar el nombre y solucionar el problema.

Métodos para usar la función BUSCARV en Excel

Ahora, entremos en materia sobre el uso de la función BUSCARV en Excel. aquí te dejamos algunas opciones:

Método 1: Vlookup de un libro de trabajo diferente

Para usar la función BUSCARV en Excel entre dos libros de trabajo, debes incluir el nombre del archivo entre corchetes, seguido del nombre de la hoja y el signo de exclamación.

• Por ejemplo: para buscar el valor A2 en el rango A2: B6 en la hoja de eneroen el libro de trabajo xlsx, usa esta fórmula:
• =VLOOKUP(A2, [Sales_reports.xlsx]Jan!$A$2:$B$6, 2, FALSE)

Método 2: Vlookup en varias hojas con IFERROR

Cuando necesites buscar entre más de dos hojas, la solución más sencilla es utilizar la función BUSCARV en Excel en combinación con IFERROR. La idea es anidar varias funciones IFERROR para verificar varias hojas de trabajo una por una: si la primera VLOOKUP no encuentra una coincidencia en la primera hoja, busque en la siguiente hoja, y así sucesivamente. Por ejemplo:

• ERROR (BUSCARV (…), SI.ERROR (BUSCARV (…),…, » No encontrado«))

Para ver cómo funciona este enfoque en datos de la vida real, vamos a considerar el siguiente ejemplo:

A continuación se muestra la tabla de resumen que debes completar con los nombres de los artículos y las cantidades al buscar el número de pedido en las hojas Oeste y Este:

• Paso 1: Primero, vas a sacar los elementos. Para ello, le indicamos a la fórmula VLOOKUP que busque el número de pedido en A2 en el hoja Este y devuelva el valor de la columna B (2ª columna en table_array A2: C6).
• Paso 2: Si no se encuentra una coincidencia exacta, busca en la hoja Oeste.
• Paso 3: Si ambos Vlookups fallan, devolverá el mensaje: «No encontrado».
  • =IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 2, FALSE), «Not found»))

• Paso 4: Para devolver la cantidad, simplemente cambia el número de índice de la columna a 3:
  • =IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 3, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 3, FALSE), «Not found»))

Consejo: Si es necesario, puedes especificar diferentes matrices de tablas para diferentes funciones VLOOKUP. En este ejemplo, ambas hojas de búsqueda tienen el mismo número de filas (A2: C6), pero tus hojas de trabajo pueden tener un tamaño diferente.

Método 3: Vlookup en varios libros de trabajo

Para buscar entre dos o más libros de trabajo, debes escribe el nombre del libro de trabajo entre corchetes y colócalo antes del nombre de la hoja.

• Por ejemplo: así es como puede usar Vlookup endos archivos diferentes (Libro1 y Libro2) con una sola fórmula:
  • =IFERROR(VLOOKUP(A2, [Book1.xlsx]East!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, [Book2.xlsx]West!$A$2:$C$6, 2, FALSE),»Not found»))

Método 4: Haz que el número de índice de columna sea dinámico para Vlookup múltiples columnas

En una situación en la que necesitas devolver datos de varias columnas, hacer que col_index_num sea dinámico podría ahorrarte algo de tiempo. Hay que realizar un par de ajustes:

• Para el argumento col_index_num, usa la función COLUMNS que devuelve el número de columnas en una matriz especificada: COLUMNS ($ A $ 1: B $ 1). (La coordenada de la fila realmente no importa, puede ser cualquier fila).
• En el argumento lookup_value, bloquea la referencia de la columna con el signo $ ($ A2), por lo que permanece fijo al copiar la fórmula a otras columnas.

Como resultado, obtendrás una especie de fórmula dinámica que extrae valores coincidentes de diferentes columnas, según en qué columna se copie la fórmula:

• =IFERROR(VLOOKUP($A2, East!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), IFERROR(VLOOKUP($A2, West!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), «Not found»))

Cuando se ingresa en la columna B, COLUMNS ($ A $ 1: B $ 1) se evalúa como 2 y le dice a VLOOKUP que devuelva un valor de 2 segunda columna en la matriz de la tabla.

Cuando se copia en la columna C (es decir, ha arrastrado la fórmula de B2 a C2), B $ 1 cambia a C $ 1 porque la referencia de la columna es relativa. En consecuencia, COLUMNS ($ A $ 1: C $ 1) se evalúa como 3, lo que obliga a la función BUSCARV en Excel a devolver un valor de la tercera columna.

Esta fórmula funciona muy bien para 2 o 3 hojas de búsqueda. Si tienes más, los IFERROR repetitivos se vuelven demasiado engorrosos. El siguiente ejemplo demuestra un enfoque un poco más complicado pero mucho más elegante.

Método 5: Vlookup múltiples hojas con INDIRECTO

Una forma más de Vlookup entre varias hojas en Excel es usar una combinación de funciones VLOOKUP e INDIRECT. Este método requiere un poco de preparación, pero al final, tendrás una fórmula más compacta para Vlookup en cualquier cantidad de hojas de cálculo.

Una fórmula genérica para Vlookup en hojas es la siguiente:

• VLOOKUP ( lookup_value, INDIRECT («‘» & INDEX ( Lookup_sheets , MATCH (1, – (COUNTIF (INDIRECT («‘» & Sheet_list & «‘! Lookup_range «), lookup_value )> 0), 0)) & «‘! table_array «), col_index_num , FALSE)

Dónde:

• Lookup_sheets: un rango con nombre que consta de los nombres de las hojas de búsqueda.
• Lookup_value: el valor a buscar.
• Lookup_range: el rango de columnas en las hojas de búsqueda donde buscar el valor de búsqueda.
• Matriz de tabla: el rango de datos en las hojas de búsqueda.
• Col_index_num: el número de la columna en la matriz de la tabla desde la que se devuelve un valor.

Para que la fórmula funcione correctamente, debes tener en cuenta las siguientes advertencias:

1. Se trata de un fórmula de matriz, que debe completarse presionando Ctrl + Mayús + Entrar llaves juntas.
2. Todas las hojas deben tener el mismo orden de columnas.
3. Como usas una matriz de tabla para todas las hojas de búsqueda, especifica el rango más grande si tus hojas tienen diferentes números de filas.

Cómo usar la fórmula para Vlookup en hojas

Para usar la función Vlookup en varias hojas a la vez, debes seguir estos pasos:

• Paso 1: Escribe todos los nombres de las hojas de búsqueda en algún lugar de tu libro de trabajo y nombre ese rango (Lookup_sheets en este caso).

• Paso 2: Ajuste la fórmula genérica para tus datos. En este ejemplo, sería:
  • buscando el valor A2 (lookup_value)
  • en el rango A2: A6 (lookup_range) en cuatro hojas de trabajo (Este, Norte, Sury Oeste), y
  • extraer valores coincidentes de la columna B, que es la columna 2 (сol_index_num) en el rango de datos A2: C6 (table_array).

Con los argumentos anteriores, la fórmula toma esta forma:

• =VLOOKUP ($A2, INDIRECT («‘»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («‘»& Lookup_sheets&»‘!$A$2:$A$6″), $A2)>0), 0)) &»‘!$A$2:$C$6»), 2, FALSE)

NOTA: Ten en cuenta que bloqueamos ambos rangos ($ A $ 2: $ A $ 6 y $ A $ 2: $ C $ 6) con referencias de celda absolutas.

• Paso 3: Ingresa la fórmula en la celda superior (B2 en este ejemplo) y presiona Ctrl + Mayús + Entrar para completarlo.
• Paso 4: Haz doble clic o arrastra el controlador de relleno para copiar la fórmula hacia abajo en la columna.

Como resultado, tendrás la fórmula para buscar el número de pedido en 4 hojas y recuperar el artículo correspondiente. Si no se encuentra un número de pedido específico, se muestra un error # N / A como en la fila 14:

Para devolver la cantidad, simplemente reemplace 2 con 3 en el argumento col_index_num ya que las cantidades están en la 3ª columna de la matriz de la tabla:

• =VLOOKUP($A2, INDIRECT(«‘»&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(«‘» & Lookup_sheets & «‘!$A$2:$A$6»), $A2)>0), 0)) & «‘!$A$2:$C$6»), 3, FALSE)

Si quieres reemplazar la notación de error estándar # N / A con tu propio texto, envuelva la fórmula en la función IFNA:

• =IFNA (VLOOKUP($A2, INDIRECT («‘»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («‘» & Lookup_sheets & «‘!$A$2:$A$6»), $A2)>0), 0)) & «‘!$A$2:$C$6»), 3, FALSE), «Not found»)

Vlookup varias hojas entre libros de trabajo

Esta fórmula genérica (o cualquier variación) también se puede utilizar para visualizar varias hojas en un libro de trabajo diferente. Para esto, concatena el nombre del libro de trabajo dentro de INDIRECTO como se muestra en la siguiente fórmula:

• =IFNA(VLOOKUP ($A2, INDIRECT («‘[Book1.xlsx]» & INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («‘[Book1.xlsx]» & Lookup_sheets & «‘!$A$2:$A$6»), $A2)>0), 0)) & «‘!$A$2:$C$6»), 2, FALSE), «Not found»)

Vlookup entre hojas y devuelve múltiples columnas

Si quieres extraer datos de varias columnas, una fórmula de matriz de varias celdas puedes hacerlo de una vez. Para crear dicha fórmula, proporciona una constante de matriz para el argumento col_index_num.

En este ejemplo, vamos devolver los nombres de los elementos (columna B) y las cantidades (columna C), que son las columnas 2ª y 3ª en la matriz de la tabla, respectivamente. Entonces, la matriz requerida es {2,3}.

• =VLOOKUP($A2, INDIRECT(«‘»&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(«‘»& Lookup_sheets &»‘!$A$2:$C$6″), $A2)>0), 0)) &»‘!$A$2:$C$6»), {2,3}, FALSE)

Para ingresar correctamente la fórmula en varias celdas, esto es lo que debe hacer:

• Paso 1: En la primera fila, selecciona todas las celdas que se llenarán (B2: C2 en este ejemplo).
• Paso 2: Escribe la fórmula y presiona las teclas Ctrl + Mayús + Entrar. Esto ingresa la misma fórmula en las celdas seleccionadas, que devolverá un valor diferente en cada columna.
• Paso 3: Arrastra la fórmula hacia las filas restantes.

Cómo funciona la fórmula de la función BUSCARV en Excel

Para comprender mejor la lógica, vamos a analizar esta fórmula básica en las funciones individuales:

• =VLOOKUP ($A2, INDIRECT («‘»&INDEX(Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («‘»& Lookup_sheets&»‘!$A$2:$A$6″), $A2)>0), 0)) &»‘!$A$2:$C$6»), 2, FALSE)

Trabajando desde adentro hacia afuera, esto es lo que hace la fórmula:

COUNTIF e INDIRECT

En pocas palabras, INDIRECT crea las referencias para todas las hojas de búsqueda, y COUNTIF cuenta las apariciones del valor de búsqueda (A2) en cada hoja:

• –(COUNTIF( INDIRECT(«‘»&Lookup_sheets&»‘!$A$2:$A$6»), $A2)>0)

Con más detalle:

Primero, concatena el nombre del rango (Lookup_sheets) y la referencia del rango ($ A $ 2: $ A $ 6), agregando apóstrofos y el signo de exclamación en los lugares correctos para hacer una referencia externa, y alimenta la cadena de texto resultante a la función INDIRECTA para hacer referencia dinámicamente a las hojas de búsqueda:

• INDIRECT ({«‘East’!$A$2:$A$6»; «‘South’!$A$2:$A$6»; «‘North’!$A$2:$A$6»; «‘West’!$A$2:$A$6»})

CONTAR.SI comprueba cada celda en el rango A2: A6 en cada hoja de búsqueda con el valor en A2 en la hoja principal y devuelve el recuento de coincidencias para cada hoja.

En este conjunto de datos, el número de orden en A2 (101) se encuentra en la hoja Oeste, que es 4 ^º en el rango con nombre, de modo de CONTAR.SI devuelve esta matriz:

• {0; 0; 0; 1}

A continuación, compara cada elemento de la matriz anterior con 0:

• –({0; 0; 0; 1}>0)

Esto produce una matriz de valores VERDADERO (mayor que 0) y FALSO (igual a 0), que coacciona a 1 y 0 mediante el uso de un unario doble (-), y obtienes la siguiente matriz como resultado:

• {0; 0; 0; 1}

Esta operación es una precaución adicional para manejar una situación en la que una hoja de búsqueda contiene varias apariciones del valor de búsqueda, en cuyo caso COUNTIF devolvería un recuento mayor que 1, mientras que solo quieres 1 y 0 en la matriz final (en un momento, entenderás por qué).

Después de todas estas transformaciones, la fórmula tiene el siguiente aspecto:

• VLOOKUP($A2, INDIRECT(«‘»&INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)) &»‘!$A$2:$C$6»), 2, FALSE)

ÍNDICE y PARTIDO

En este punto, una combinación clásica de PARTIDA DE ÍNDICE pasa a:

• INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0))

La función COINCIDIR configurada para coincidencia exacta (0 en el último argumento) busca el valor 1 en la matriz {0; 0; 0; 1} y devuelve su posición, que es 4:

INDEX(Lookup_sheets, 4)

La función INDICE usa el número devuelto por COINCIDIR como el argumento del número de fila (row_num) y devuelve el cuarto valor en el rango con nombre Lookup_sheets, que es Oeste.

Entonces, la fórmula se reduce aún más a:

• VLOOKUP($A2, INDIRECT(«‘»&»West»&»‘!$A$2:$C$6»), 2, FALSE)

VLOOKUP e INDIRECTO

La función INDIRECT procesa la cadena de texto dentro de ella:

• INDIRECT («‘»&»West»&»‘!$A$2:$C$6»)

Y lo convierte en una referencia que va al argumento table_array de VLOOKUP:

• VLOOKUP ($A2, ‘West’!$A$2:$C$6, 2, FALSE)

Finalmente, esta fórmula VLOOKUP muy estándar busca el valor A2 en la primera columna del rango A2: C6 en la hoja Oeste y devuelve una coincidencia de la segunda columna.

VLOOKUP dinámico para devolver datos de varias hojas a diferentes celdas

En primer lugar, definamos qué significa exactamente la palabra «dinámica» en este contexto y en qué se diferenciará esta fórmula de las anteriores.

En caso de que tenga grandes cantidades de datos en el mismo formato que se dividen en varias hojas de cálculo, es posible que quieras extraer información de diferentes hojas en diferentes celdas. La siguiente imagen ilustra el concepto:

A diferencia de las fórmulas anteriores que recuperaban un valor de una hoja específica en función de un identificador único, esta vez buscarás extraer valores de varias hojas a la vez.

Hay dos soluciones diferentes para esta tarea. En ambos casos, debes hacer un pequeño trabajo preparatorio y crear rangos con nombre para las celdas de datos en cada hoja de búsqueda. Para este ejemplo, definimos los siguientes rangos:

• East_Sales- A2: B6 en la hoja Este
• North_Sales- A2: B6 en la hoja Norte
• South_Sales- A2: B6 en la hoja Sur
• West_Sales- A2: B6 en la hoja Oeste

VLOOKUP e IF anidados

Si tienes un número razonable de hojas para buscar, puedes usar funciones IF anidadas para seleccionar la hoja según las palabras clave en las celdas predefinidas (celdas B1 a D1 en este caso).

Con el valor de búsqueda en A2, la fórmula es la siguiente:

• =VLOOKUP ($A2, IF (B$1=»east», East_Sales, IF (B$1=»north», North_Sales, IF (B$1=»south», South_Sales, IF (B$1=»west», West_Sales)))), 2, FALSE)

Traducido al inglés, la parte IF dice:

Si B1 es East, busca en el rango denominado East_Sales; si B1 es North, busca en el rango llamado North_Sales; si B1 es South, busca en el rango llamado South_Sales; y si B1 es West, busca en el rango llamado West_Sales.

El rango devuelto por IF va a table_array de VLOOKUP, que extrae un valor coincidente de la segunda columna en la hoja correspondiente.

El uso inteligente de referencias mixtas para el valor de búsqueda ($ A2 – columna absoluta y fila relativa) y la prueba lógica de IF (B $ 1 – columna relativa y fila absoluta) permite copiar la fórmula a otras celdas sin ningún cambio – Excel ajusta el referencias automáticamente basadas en la posición relativa de una fila y columna.

Entonces, ingresas la fórmula en B2, la copias hacia la derecha y hacia abajo en tantas columnas y filas como sea necesario, y obtendrás el siguiente resultado:

VLOOKUP INDIRECTO

Cuando se trabaja con muchas hojas, varios niveles anidados pueden hacer que la fórmula sea demasiado larga y difícil de leer. Una forma mucho mejor es crear un rango de vlookup dinámico con la ayuda de INDIRECT:

• =VLOOKUP ($A2, INDIRECT (B$1&»_Sales»), 2, FALSE)

Aquí, concatenamos la referencia a la celda que contiene una parte única del rango con nombre (B1) y la parte común (_Ventas). Esto produce una cadena de texto como «East_Sales», que INDIRECT convierte al nombre de rango comprensible para Excel.

Como resultado, obtienes una fórmula compacta que funciona maravillosamente en cualquier cantidad de hojas:

Tal vez te interese saber sobre: Cómo Agrupar Una Tabla Dinámica Por Meses En Excel

Cómo verás, estas son las formas de usar la función BUSCARV en Excel junto a otras funciones de búsqueda por hoja como Vlookup que te puede ayudar mucho para encontrar los datos que quieres conseguir entre hojas y archivos en Excel.  Esperamos haberte ayudado.