23 cosas que debe saber sobre VLOOKUP

Cuando desee extraer información de una tabla, la función BUSCARV de Excel es una gran solución. La capacidad de buscar y recuperar información dinámicamente de una tabla es un cambio de juego para muchos usuarios, y encontrará BUSCARV en todas partes.

Y, sin embargo, aunque BUSCARV es relativamente fácil de usar, hay muchas cosas que pueden salir mal. Una razón es que BUSCARV tiene un defecto de diseño importante: de forma predeterminada, asume que está de acuerdo con una coincidencia aproximada. Lo cual probablemente no lo eres.

Esto puede causar resultados que parece completamente normal , a pesar de que son totalmente incorrecto . Créame, esto NO es algo que quiera intentar explicarle a su jefe, después de que ella ya envió su hoja de cálculo a la gerencia :)

Lea a continuación, aprenda a gestionar este desafío y descubra otros consejos para dominar el Función BUSCARV de Excel .

1. Cómo funciona BUSCARV

BUSCARV es una función para buscar y recuperar datos en una tabla. La 'V' en VLOOKUP significa vertical, lo que significa que los datos de la tabla deben organizarse verticalmente, con los datos en filas. (Para datos estructurados horizontalmente, consulte HLOOKUP ).

Si tiene una tabla bien estructurada, con información ordenada verticalmente y una columna a la izquierda que puede usar para hacer coincidir una fila, probablemente pueda usar BUSCARV.

VLOOKUP requiere que la tabla esté estructurada de modo que los valores de búsqueda aparezcan en la columna más a la izquierda. Los datos que desea recuperar (valores de resultado) pueden aparecer en cualquier columna a la derecha. Cuando utilice BUSCARV, imagine que todas las columnas de la tabla están numeradas, empezando por la izquierda. Para obtener un valor de una columna en particular, simplemente proporcione el número apropiado como 'índice de columna'. En el siguiente ejemplo, queremos buscar la dirección de correo electrónico, por lo que estamos usando el número 4 para el índice de la columna:

En la tabla anterior, las identificaciones de los empleados están en la columna 1 a la izquierda y las direcciones de correo electrónico están en la columna 4 a la derecha.

Para utilizar BUSCARV, debe proporcionar 4 piezas de información o 'argumentos':

1. El valor que busca ( valor de búsqueda )
2. El rango de celdas que componen la tabla ( matriz de tabla )
3. El número de la columna de la que se recuperará un resultado ( column_index )
4. El modo de partido ( rango de búsqueda , VERDADERO = aproximado, FALSO = exacto)

Video: Cómo utilizar BUSCARV (3 min)

Si aún no tiene la idea básica de BUSCARV, Jon Acampora, en Excel Campus, tiene una gran explicacion basado en el menú de café de Starbucks.

2. VLOOKUP solo se ve bien

Quizás la mayor limitación de VLOOKUP es que solo puede mirar hacia la derecha para recuperar datos.

Esto significa que BUSCARV solo puede obtener datos de las columnas a la derecha de la primera columna de la tabla. Cuando los valores de búsqueda aparecen en la primera columna (más a la izquierda), esta limitación no significa mucho, ya que todas las demás columnas ya están a la derecha. Sin embargo, si la columna de búsqueda aparece dentro de la tabla en algún lugar, solo podrá buscar valores de las columnas a la derecha de esa columna. También deberá proporcionar una tabla más pequeña a BUSCARV que comience con la columna de búsqueda.

Puede superar esta limitación utilizando ÍNDICE y COINCIDIR en lugar de BUSCARV.

3. VLOOKUP encuentra la primera coincidencia

En el modo de coincidencia exacta, si una columna de búsqueda contiene valores duplicados, BUSCARV solo coincidirá con el primer valor. En el siguiente ejemplo, usamos BUSCARV para encontrar un nombre y BUSCARV está configurado para realizar una coincidencia exacta. Aunque hay dos 'Janet's en la lista, BUSCARV solo coincide con la primera:

Nota: el comportamiento puede cambiar cuando se usa BUSCARV en modo de coincidencia aproximada. Este artículo explica el tema en detalle.

4. VLOOKUP no distingue entre mayúsculas y minúsculas

Al buscar un valor, BUSCARV no procesa el texto en mayúsculas y minúsculas de manera diferente. Para BUSCARV, un código de producto como 'PQRF' es idéntico a 'pqrf'. En el siguiente ejemplo, buscamos 'JANET' en mayúsculas pero BUSCARV no distingue entre mayúsculas y minúsculas, por lo que simplemente coincide con 'Janet', ya que esa es la primera coincidencia que encuentra:

5. VLOOKUP tiene dos modos de coincidencia

VLOOKUP tiene dos modos de funcionamiento: coincidencia exacta y coincidencia aproximada. En la mayoría de los casos, probablemente desee utilizar BUSCARV en el modo de coincidencia exacta. Esto tiene sentido cuando desea buscar información basada en una clave única de algún tipo, por ejemplo, información de producto basada en un código de producto o datos de película basados ​​en el título de una película:

La fórmula en H6 para buscar el año en función de una coincidencia exacta del título de la película es:

Sin embargo, querrá usar el modo aproximado en los casos en los que no coincida con una identificación única, sino que busque la 'mejor coincidencia' o la 'mejor categoría'. Por ejemplo, tal vez esté buscando gastos de envío basados ​​en el peso, buscando la tasa de impuestos basada en los ingresos o buscando una tasa de comisión basada en un número de ventas mensual. En estos casos, es probable que no encuentre el valor de búsqueda exacto en la tabla. En su lugar, desea que BUSCARV le proporcione la mejor coincidencia para un valor de búsqueda determinado.

La fórmula en D5 hace una coincidencia aproximada para recuperar la comisión correcta:

6. Precaución: BUSCARV utiliza una coincidencia aproximada de forma predeterminada

La coincidencia exacta y aproximada en BUSCARV está controlada por el cuarto argumento, llamado 'búsqueda de rango'. Este nombre no es intuitivo, por lo que solo tendrá que memorizar cómo funciona.

Para una coincidencia exacta, use FALSE o 0. Para una coincidencia aproximada, establezca range_lookup en TRUE o 1:

Desafortunadamente, el cuarto argumento, range_lookup, es opcional y por defecto es TRUE, lo que significa que BUSCARV hará una coincidencia aproximada por defecto. Al hacer una coincidencia aproximada, BUSCARV asume que la tabla está ordenada y realiza una búsqueda binaria. Durante una búsqueda binaria, si BUSCARV encuentra un valor de coincidencia exacto, devuelve un valor de esa fila. Sin embargo, si BUSCARV encuentra un valor mayor que el valor de búsqueda, devolverá un valor de la fila anterior.

Este es un valor predeterminado peligroso porque muchas personas, sin saberlo, dejan VLOOKUP en su modo predeterminado, lo que puede causar una resultado incorrecto cuando la tabla no está ordenada.

Para evitar este problema, asegúrese de utilizar FALSE o cero como cuarto argumento cuando desee una coincidencia exacta.

7. Puede forzar VLOOKUP a hacer una coincidencia exacta

Para forzar VLOOKUP a encontrar una coincidencia exacta, asegúrese de establecer el argumento 4 (range_lookup) en FALSE o cero. Estas dos fórmulas son equivalentes:

En el modo de coincidencia exacta, cuando BUSCARV no puede encontrar un valor, devolverá # N / A. Esta es una clara indicación de que el valor no se encuentra en la tabla.

8. Puede decirle a BUSCARV que haga una coincidencia aproximada.

Para usar BUSCARV en modo de coincidencia aproximada, omita el cuarto argumento (range_lookup) o proporciónelo como TRUE o 1. Estas 3 fórmulas son equivalentes:

Recomendamos que siempre establezca el argumento range_lookup explícitamente, aunque BUSCARV no lo requiera. De esa manera, siempre tendrá un recordatorio visual del modo de partido que espera.

Video: Cómo usar BUSCARV para coincidencias aproximadas

9. Para coincidencias aproximadas, los datos deben estar ordenados

Si utiliza la concordancia en modo aproximado, sus datos debe ser ordenado en orden ascendente por valor de búsqueda. De lo contrario, puede obtener una resultados incorrectos . También tenga en cuenta que a veces los datos de texto pueden Mira ordenados, aunque no lo sea.

Felienne Hermans tiene un gran ejemplo de este problema aquí , a partir de un interesante análisis que hizo sobre Enron hojas de cálculo!

10. VLOOKUP puede combinar datos en diferentes tablas

Un caso de uso común de BUSCARV es unir datos de dos o más tablas. Por ejemplo, tal vez tenga datos de pedidos en una tabla y datos de clientes en otra y desee traer algunos datos de clientes a la tabla de pedidos para su análisis:

Debido a que la identificación del cliente existe en ambas tablas, puede usar este valor para obtener los datos que desea con BUSCARV. Simplemente configure BUSCARV para usar el valor de id en la tabla uno, y los datos en la tabla 2, con el índice de columna requerido. En el siguiente ejemplo, estamos usando dos fórmulas BUSCARV. Uno para ingresar el nombre del cliente y el otro para ingresar el estado del cliente.

Enlace: Ejemplo de fusión con BUSCARV .

Video: Cómo utilizar BUSCARV para fusionar tablas .

11. VLOOKUP puede clasificar o categorizar datos

Si alguna vez necesita aplicar categorías arbitrarias a los registros de datos, puede hacerlo fácilmente con BUSCARV, utilizando una tabla que actúa como la 'clave' para asignar categorías.

Un ejemplo clásico son las calificaciones, donde debe asignar una calificación basada en una puntuación:

En este caso, BUSCARV está configurado para una coincidencia aproximada, por lo que es importante que la tabla se ordene en orden ascendente.

Pero también puede usar BUSCARV para asignar categorías arbitrarias. En el siguiente ejemplo, estamos usando BUSCARV para calcular un grupo para cada departamento usando una pequeña tabla (llamada 'clave') que define la agrupación.

12. Las referencias absolutas hacen que BUSCARV sea más portátil

En situaciones en las que planea recuperar información de más de una columna en una tabla, o si necesita copiar y pegar BUSCARV, puede ahorrar tiempo y molestias al usar referencias absolutas para el valor de búsqueda y la matriz de la tabla. Esto le permite copiar la fórmula y luego cambiar solo el número de índice de la columna para usar la misma búsqueda para obtener un valor de una columna diferente.

Por ejemplo, debido a que el valor de búsqueda y la matriz de la tabla son absolutos, podemos copiar la fórmula en las columnas, luego regresar y cambiar el índice de la columna según sea necesario.

13. Los rangos con nombre hacen que BUSCARV sea más fácil de leer (y más portátil)

Los rangos absolutos tienen un aspecto bastante feo, por lo que pueden hacer que sus fórmulas VLOOKUP sean mucho más limpias y fáciles de leer reemplazando las referencias absolutas con rangos con nombre, que son automáticamente absolutos.

Por ejemplo, en el ejemplo de datos de empleados anterior, puede nombrar la celda de entrada 'id' y luego nombrar los datos en la tabla 'datos', puede escribir su fórmula de la siguiente manera:

Esta fórmula no solo es más fácil de leer, sino que también es más portátil, ya que los rangos con nombre son automáticamente absolutos.

14. Insertar una columna puede romper las fórmulas VLOOKUP existentes

Si tiene fórmulas VLOOKUP existentes en una hoja de trabajo, las fórmulas pueden romperse si inserta una columna en la tabla. Esto se debe a que los valores de índice de columna codificados no cambian automáticamente cuando se insertan o eliminan columnas.

En este ejemplo, las búsquedas de Rango y Ventas se interrumpieron cuando se insertó una nueva columna entre Año y Rango. El año sigue funcionando porque está a la izquierda de la columna insertada:

Para evitar este problema, puede calcular un índice de columna como se describe en los dos consejos siguientes.

15. Puede utilizar FILA o COLUMNA para calcular un índice de columna.

Si es del tipo al que le molesta cualquier cantidad de edición después de copiar una fórmula, puede usar FILA o COLUMNA para generar índices de columna dinámicos. Si obtiene datos de columnas consecutivas, este truco le permite configurar una fórmula VLOOKUP y luego copiarla sin necesidad de cambios.

Por ejemplo, con los datos de los empleados a continuación, podemos usar la función COLUMNA para generar un índice de columna dinámico. Para la primera fórmula en la celda C3, COLUMN por sí sola devolverá 3 (porque la columna C es la tercera en la hoja de trabajo), por lo que simplemente necesitamos restar uno y copiar la fórmula:

Todas las fórmulas son idénticas y no se requiere edición posterior.

La fórmula que estamos usando es la siguiente:

16. Use BUSCARV + COINCIDIR para obtener un índice de columna completamente dinámico

Llevando el consejo anterior un paso más allá, puede usar MATCH para buscar la posición de una columna en una tabla y devolver un índice de columna completamente dinámico.

Esto a veces se denomina búsqueda bidireccional, ya que busca tanto la fila como la columna.

Un ejemplo sería buscar las ventas de un vendedor en un mes en particular o buscar el precio de un producto en particular de un proveedor en particular.

Por ejemplo, suponga que tiene ventas por mes, desglosadas por vendedor:

VLOOKUP puede encontrar fácilmente al vendedor, pero no tiene forma de manejar el nombre del mes automáticamente. El truco consiste en utilizar la función COINCIDIR en lugar de un índice de columna estática.

Tenga en cuenta que le damos a la coincidencia un rango que incluye todas las columnas de la tabla para 'sincronizar' los números de columna utilizados por BUSCARV.

Nota: a menudo verá búsquedas bidireccionales realizadas con INDEX y MATCH, un enfoque que ofrece más flexibilidad y mejor rendimiento en grandes conjuntos de datos. Vea cómo en este video rápido: Cómo hacer una búsqueda bidireccional con ÍNDICE y COINCIDIR .

17. VLOOKUP permite comodines para coincidencias parciales

Cada vez que use BUSCARV en modo de coincidencia exacta, tiene la opción de usar comodines en el valor de búsqueda. Puede parecer contradictorio, pero los comodines le permiten hacer una coincidencia exacta basada en una coincidencia parcial :)

Excel proporciona dos caracteres comodín: un asterisco (*) coincide con uno o más caracteres y un signo de interrogación (?) Coincide con un carácter.

Por ejemplo, puede escribir un asterisco directamente en una celda y referirse a él como un valor de búsqueda con BUSCARV. En la siguiente pantalla, hemos ingresado 'Mon *' en H3, que es un rango con nombre llamado 'val'. Esto hace que BUSCARV coincida con el nombre 'Monet'.

La fórmula en este caso es simple:

Si lo desea, puede ajustar la fórmula BUSCARV para usar un comodín integrado, como en el ejemplo siguiente, donde simplemente concatenamos el valor en H3 con un asterisco.

En este caso, estamos concatenando el asterisco al valor de búsqueda dentro de la función BUSCARV:

Nota: tenga cuidado con los comodines y BUSCARV. Le brindan una manera fácil de crear una 'combinación perezosa', pero también facilitan la búsqueda de la combinación incorrecta.

18. Puede atrapar errores # N / A y mostrar un mensaje amigable

En el modo de coincidencia exacta, BUSCARV mostrará el error # N / A cuando no se encuentre ninguna coincidencia. De alguna manera, esto es útil porque le dice definitivamente que no hay ninguna coincidencia en la tabla de búsqueda. Sin embargo, los errores # N / A no son muy divertidos de ver, por lo que hay varias formas de atrapar este error y mostrar algo más en su lugar.

Una vez que comience a usar BUSCARV, es probable que se encuentre con el error # N / A, que ocurre cuando BUSCARV no puede encontrar una coincidencia.

cómo escribir vlookup en Excel

Este es un error útil, porque BUSCARV le dice claramente que no puede encontrar el valor de búsqueda. En este ejemplo, 'Latte' no existe como bebida en la mesa, por lo que BUSCARV arroja un error # N / A

La fórmula en este caso es una coincidencia exacta completamente estándar:

Sin embargo, los errores # N / A no son muy divertidos de ver, por lo que es posible que desee detectar este error y mostrar un mensaje más amigable.

La forma más fácil de atrapar errores con BUSCARV es envolver BUSCARV en la función SI.ERROR. IFERROR le permite 'capturar' cualquier error y devolver un resultado de su elección.

Para atrapar este error y mostrar un mensaje de 'no encontrado' en lugar del error, simplemente puede envolver la fórmula original dentro de IFERROR y establecer el resultado que desea:

Si se encuentra el valor de búsqueda, no se produce ningún error y la función BUSCARV devuelve un resultado normal. Aquí está la fórmula:

19. Los números como texto pueden provocar un error de coincidencia.

A veces, la tabla con la que está trabajando en BUSCARV puede contener números ingresados ​​como texto. Si simplemente está recuperando números como texto de una columna en una tabla, no importa. Pero si la primera columna de la tabla contiene números ingresados ​​como texto, obtendrá un error # N / A si el valor de búsqueda no es también texto.

En el siguiente ejemplo, los identificadores de la tabla de planetas son números ingresado como texto , lo que hace que BUSCARV devuelva un error ya que el valor de búsqueda es el número 3:

Para resolver este problema, debe asegurarse de que el valor de búsqueda y la primera columna de la tabla sean del mismo tipo de datos (ambos números o ambos texto).

Una forma de hacerlo es convertir los valores de la columna de búsqueda en números. Una forma fácil de hacer esto es agregar cero usando una pasta especial.

Si no tiene un control fácil sobre la tabla de origen, también puede ajustar la fórmula VLOOKUP para convertir el valor de búsqueda en texto concatenando '' al valor así:

Si no puede estar seguro de cuándo tendrá números y cuándo tendrá texto, puede atender ambas opciones ajustando VLOOKUP en IFERROR y escribiendo una fórmula para manejar ambos casos:

20. Puede usar BUSCARV para reemplazar declaraciones IF anidadas

Uno de los usos más interesantes de BUSCARV es reemplazar declaraciones IF anidadas. Si alguna vez ha construido una serie de IF anidados, sabrá que funcionan bien, pero requieren un poco de discusión entre paréntesis. También debes tener cuidado con el orden en el que trabajas, para no introducir un error lógico.

Por ejemplo, un uso común de los IF anidados es asignar calificaciones en función de una puntuación de algún tipo. En el siguiente ejemplo, puede ver que se ha creado una fórmula con IF anidados para hacer precisamente eso, utilizando la tecla de calificación a la derecha como guía.

La fórmula IF anidada completa se ve así:

Esto funciona bien, pero tenga en cuenta que tanto la lógica como los puntajes reales se integran directamente en la fórmula. Si la puntuación cambia por algún motivo, deberá actualizar cuidadosamente una fórmula y luego copiarla en toda la tabla.

Por el contrario, VLOOKUP puede asignar las mismas calificaciones con una fórmula simple. Todo lo que necesita hacer es asegurarse de que la tabla de claves de calificaciones esté configurada para BUSCARV (es decir, que la mayoría de las veces esté ordenada por puntaje y contenga corchetes para manejar todos los puntajes).

Después de definir una 'clave' de rango con nombre para la tabla de clave de calificación, la fórmula VLOOKUP es muy simple y generó las mismas calificaciones que la fórmula de IF anidada original:

Con la tabla de claves de grado llamada 'clave' tenemos una fórmula VLOOKUP muy simple:

Una buena ventaja de este enfoque es que tanto la lógica como los puntajes se integran directamente en la tabla de claves de calificaciones. Si algo cambia, simplemente puede actualizar la tabla directamente y las fórmulas VLOOKUP se actualizarán automáticamente, sin necesidad de editar.

Video: Cómo reemplazar los IF anidados con BUSCARV

21. VLOOKUP solo puede manejar un único criterio

Por diseño, BUSCARV solo puede encontrar valores basados ​​en un único criterio, que se proporciona como un valor de búsqueda para buscar en la primera columna de la tabla (la columna de búsqueda).

Esto significa que no puede hacer cosas fácilmente como buscar a un empleado con el apellido 'Smith' en 'Contabilidad', o buscar a un empleado por nombre y apellido en columnas separadas.

Sin embargo, hay formas de superar esta limitación. Una solución es crear una columna auxiliar que concatene valores de diferentes columnas para crear valores de búsqueda que se comporten como varias condiciones. Por ejemplo, aquí queremos encontrar el departamento y el grupo de un empleado, pero el nombre y el apellido aparecen en columnas separadas. ¿Cómo podemos buscar ambos a la vez?

Primero, agregue una columna auxiliar que simplemente concatene el nombre y el apellido juntos:

Luego configure VLOOKUP para usar una tabla que incluya esta nueva columna, y combine el nombre y apellido para el valor de búsqueda:

La fórmula final de BUSCARV busca el nombre y el apellido juntos usando la columna auxiliar como clave:

22. Dos VLOOKUPS son más rápidos que una VLOOKUPS

Puede parecer una locura, pero cuando tiene un gran conjunto de datos y necesita hacer una coincidencia exacta, puede acelerar mucho la BUSCARV agregando otra BUSCARV a la fórmula.

El trasfondo: imagina que tienes una gran cantidad de datos de pedidos, digamos, más de 10,000 registros y estás usando BUSCARV para buscar el total del pedido en función del ID del pedido. Entonces, estás usando algo como esto:

El FALSO al final obliga a BUSCARV a hacer una coincidencia exacta. Quiere una coincidencia exacta porque existe la posibilidad de que no se encuentre un número de pedido. En este caso, la configuración de coincidencia exacta hará que BUSCARV devuelva el error # N / A.

El problema es que las coincidencias exactas son realmente lentas, porque Excel debe proceder de forma lineal a través de todos los valores hasta que encuentre una coincidencia o no.

Por el contrario, las coincidencias aproximadas son ultrarrápidas porque Excel es capaz de hacer lo que se llama búsqueda binaria .

Sin embargo, el problema con las búsquedas binarias (es decir, BUSCARV en modo de coincidencia aproximada) es que BUSCARV puede devolver un resultado incorrecto cuando no se encuentra un valor. Peor aún, el resultado puede parecer completamente normal, por lo que puede ser muy difícil de detectar.

La solución es usar BUSCARV dos veces, ambas en modo de coincidencia aproximada. La primera instancia simplemente verifica que el valor realmente exista. Si es así, se ejecuta otra BUSCARV (nuevamente, en modo de coincidencia aproximada) para obtener los datos que desea. De lo contrario, puede devolver cualquier valor que desee para indicar que no se encontró un resultado.

La fórmula final se ve así:

Aprendí este enfoque de Charles Williams de FastExcel, quien tiene un artículo fantástico y detallado aquí: Por qué 2 VLOOKUPS son mejores que 1 VLOOKUPS .

Nota: sus datos deben estar ordenados para utilizar este truco. Es simplemente una forma de protegerse contra un valor de búsqueda faltante, mientras se mantiene una búsqueda rápida.

23. ÍNDICE y COINCIDIR juntos pueden hacer todo lo que BUSCARV puede hacer y más

Si sigue Excel en línea, probablemente se encontrará con el Debate VLOOKUP vs. ÍNDICE / PARTIDO. La discusión puede calentarse sorprendentemente :)

La esencia es la siguiente: INDEX + MATCH puede hacer todo lo que VLOOKUP (y HLOOKUP) puede hacer, con mucha más flexibilidad, a costa de un poco más de complejidad. Entonces, aquellos a favor de INDEX + MATCH argumentarán (muy sensatamente) que también podría comenzar aprendiendo INDEX y MATCH, ya que al final le brinda un mejor conjunto de herramientas.

El argumento en contra de INDEX + MATCH es que requiere dos funciones en lugar de una, por lo que es inherentemente más complejo para los usuarios (especialmente los nuevos) de aprender y dominar.

Mi granito de arena es que si usa Excel con frecuencia, querrá aprender a usar INDICE y MATCH. Es una combinación muy poderosa.

Pero también creo que debería aprender BUSCARV, que encontrará en todas partes, a menudo en las hojas de trabajo que hereda de otros. En situaciones sencillas, BUSCARV hará el trabajo bien sin problemas.

Para obtener más información sobre INDICE y MATCH, ver este articulo .