Cómo usar una fórmula de VLOOKUP para obtener datos de una lista de Excel. Solucione problemas de BUSCARV cuando las cosas vayan mal. Videos paso a paso y libros de trabajo de Excel con ejemplos de VLOOKUP.
VLOOKUP Video
Vea los pasos para crear una fórmula de VLOOKUP en el video de búsqueda de precios de productos, que se muestra a continuación. Las instrucciones escritas están debajo del video. Obtenga el archivo de muestra de búsqueda de precios de productos para seguir junto con el video (archivo de descarga n. ° 4).
Seleccione una ubicación para una tabla de búsqueda
Es una buena idea almacenar cada búsqueda tabla en una hoja de trabajo separada en el libro de trabajo. Luego, a medida que agrega y elimina filas en las tablas de búsqueda, no agregará ni eliminará accidentalmente filas en ninguna otra tabla.
En este ejemplo, la tabla de búsqueda está en una hoja llamada Productos.
Crear una tabla de búsqueda
Las fórmulas de búsqueda pueden funcionar verticalmente, buscando valores en una columna, o pueden funcionar horizontalmente, buscando valores en una fila . En este ejemplo, la información se almacenará verticalmente, con los valores en una columna, y luego usaremos una fórmula de VLookup para hacer una búsqueda vertical.
- Ingrese los encabezados en la primera fila
- La primera columna debe contener los valores clave únicos en los que basará la búsqueda. En este ejemplo, puede encontrar el precio para un código de producto específico.
- Si tiene otros datos en la hoja de trabajo, deje al menos una fila en blanco en la parte inferior de la tabla y una columna en blanco a la derecha de la tabla, para separar tabla de búsqueda de los otros datos.
Nota: Para facilitar la consulta de la tabla, puede nombrar el rango. Aquí hay instrucciones: Nombrar un rango ▲ ARRIBA
Argumentos de la función VLOOKUP
La función VLOOKUP tiene cuatro argumentos:
- lookup_value : ¿Qué valor desea buscar? En este ejemplo, el código de producto está en la celda A7 y desea encontrar su pr nombre del producto.
- table_array: ¿Dónde está la tabla de búsqueda? Si usa una referencia absoluta ($ A $ 2: $ C $ 5), en lugar de una referencia relativa (A2: C5), será más fácil copiar la fórmula a otras celdas. O bien, asigne un nombre a la tabla de búsqueda y consúltela por su nombre.
- col_index_num: ¿Qué columna tiene el valor que desea devolver? En este ejemplo, los nombres de los productos están en la segunda columna de la tabla de búsqueda.
- : ¿Quiere una coincidencia exacta? ¿Está bien una coincidencia aproximada?
Si utiliza TRUE como último argumento u omite el último argumento, se puede devolver una coincidencia aproximada. Este ejemplo tiene FALSE como último argumento, por lo que si no se encuentra el código de producto, el resultado será # N / A. (Nota: Excel es bastante indulgente y aceptará 0 en lugar de FALSE y 1 en lugar de TRUE). ▲ ARRIBA
Cree una fórmula de VLookup
Una vez que haya creado la tabla de búsqueda, puede crear otras fórmulas en el libro de trabajo y extraer información de la lista de productos. Por ejemplo, para preparar una factura, puede ingresar un código de producto, y las fórmulas obtendrán el nombre del producto o el precio de la tabla de productos.
En este ejemplo, la factura se crea en una hoja denominada Factura. La fórmula VLOOKUP debe encontrar una coincidencia exacta para el código del producto y devolver el nombre del producto.
Para crear la fórmula VLOOKUP que calcula el precio del producto, siga estos pasos:
- Seleccione la hoja de Factura
- Ingrese el código de producto A23 en la celda A7
- En la celda B7, inicie la fórmula VLOOKUP:
= VLOOKUP ( - Haga clic en celda A7 para ingresar su referencia.
- Escriba una coma, para finalizar el primer argumento
- Seleccione la hoja Productos
- Seleccione las celdas A2: C5 (la lista de productos )
- Presione la tecla F4 para cambiar las referencias de celda a absolutas. La fórmula ahora debería verse así:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5 - Escriba una coma para finalizar el segundo argumento.
- Escriba un 2, que es la columna de la tabla de búsqueda que contiene el nombre del producto.
- Escriba una coma para finalizar el tercer argumento.
- Escriba FALSE, para especificar que se encuentra una coincidencia exacta para el código del producto, y agregue el corchete de cierre.
- La fórmula ahora debe verse l como esto:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,2, FALSE) - Presione la tecla Enter para completar la fórmula. Se mostrará el nombre del producto.
Nota: Para devolver el precio del producto, cree una fórmula BUSCARV que se refiera a la columna 3 de la tabla de búsqueda. Por ejemplo, ingrese la siguiente fórmula en la celda C7:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,3, FALSE) ▲ ARRIBA
VLOOKUP Fórmula para rango de valores
En algunas situaciones, se prefiere una coincidencia aproximada, por lo que varios valores devolverán el mismo resultado. Por ejemplo, al calificar los trabajos de los estudiantes, todos los trabajos con una calificación de 85 o más deben recibir una calificación de A.
Para ver los pasos para crear esta fórmula, mire el video VLOOKUP que se muestra a continuación. Las instrucciones escritas están debajo del video.
En este ejemplo, la tabla de búsqueda se crea en una hoja llamada Calificaciones. Para crear la tabla de búsqueda, ingrese la calificación mínima para cada calificación en la columna A. Ingrese la calificación correspondiente en la columna B. Ordene las calificaciones en orden ascendente.
Las celdas A2: B6 se denominaron GradeList.
Las puntuaciones se ingresan en una hoja llamada Report Card, donde una fórmula VLOOKUP calcula la calificación.
En el captura de pantalla a continuación, la fórmula se copió en la fila 6 y puede ver la fórmula en la celda C6.
Combinar BUSCARV con COINCIDIR
En lugar de escribir el número de columna en una fórmula de BUSCARV, use la función COINCIDIR para encontrar la columna correcta en la tabla de búsqueda. Esto tiene un par de beneficios:
- Hace que la fórmula sea flexible, por lo que es más fácil copiar la fórmula en una hoja de trabajo.
- Puede evitar problemas si se agregan nuevas columnas en la tabla de búsqueda, o si las columnas de búsqueda están reorganizadas.
Este video muestra los pasos y hay instrucciones escritas para otro ejemplo, debajo del video.
Su navegador no puede mostrar este marco. Aquí hay un enlace a la página
VLOOKUP con COINCIDIR para detalles del pedido
En este ejemplo, una fórmula VLOOKUP devolverá los detalles del pedido de una tabla de búsqueda, según el número de identificación del pedido. . Aquí está la tabla de búsqueda, llamada tblOrders.
NOTA: Este ejemplo está en el Libro de trabajo de muestra # 1, en la hoja llamada OrdersMATCH.
Aquí está la hoja de trabajo con las fórmulas BUSCARV. Queremos la región, la fecha del pedido y el monto del pedido para cada pedido, por lo que se necesitan 3 fórmulas VLOOKUP.
Si los números de columna se escriben en la fórmula, se necesita una fórmula diferente en cada columna:
- Región: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
La función COINCIDIR
En lugar de escribir el número de columna en la fórmula BUSCARV, podemos usar la función COINCIDIR. La función COINCIDIR busca la posición de un elemento en una lista y devuelve el número de posición.
En la siguiente captura de pantalla, la fórmula COINCIDIR devuelve 2 como la posición de «Región», en las celdas de encabezado (A1: D1) de la tabla de búsqueda.
= COINCIDIR ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
NOTA: Para que esta técnica funcione correctamente, el Los títulos de la hoja BUSCARV deben coincidir exactamente con los títulos de la tabla de búsqueda. Para garantizar una coincidencia exacta, las celdas del encabezado BUSCARV están vinculadas a las celdas del encabezado de la tabla de búsqueda.
Agregar COINCIDIR a BUSCARV
Para agregar la función COINCIDIR a la fórmula BUSCARV, simplemente reemplace número de columna escrito
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
con la fórmula COINCIDIR:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Copie la fórmula VLOOKUP en
Ahora, en lugar de necesitar una fórmula diferente en cada columna, puede copiar la fórmula y usar la misma fórmula en todas las columnas. En cada columna, hará referencia a la celda de encabezado en esa columna y encontrará su posición en la tabla de búsqueda.
NOTA: Si está llenando la fórmula en columnas con diferentes formatos, siga estos pasos:
- Seleccione la celda con la fórmula que desea copiar
- Apunte al controlador de relleno en la celda seleccionada (pequeño cuadrado en la esquina inferior derecha)
- Presione el botón DERECHO del mouse y arrastre hasta la última celda que necesita la fórmula
- Suelte el botón del mouse y haga clic en Rellenar sin formato
Combinar IF y VLOOKUP
Puede usar una fórmula IF con una fórmula de VLookup, para devolver valores exactos si los encuentra, y una cadena vacía si no se encuentra.
Para ver los pasos para configurar la fórmula SI y BUSCARV, puede ver este breve video. Las instrucciones escritas están debajo del video.
Para ocultar errores al combinar SI con BUSCARV, siga estos pasos:
- En la hoja de Factura, en la celda A8, ingrese el producto código A28. Si la fórmula de VLookup en la celda B8 tiene FALSE como cuarto argumento, el resultado es # N / A, porque no hay una coincidencia exacta para el código de producto en la tabla de búsqueda.
- Envuelva la fórmula de VLookup con un Fórmula IF (en este ejemplo se ha nombrado la lista de productos), usando la función ISNA para verificar un error # N / A:
- Presione la tecla Enter y la celda aparecerá en blanco. Debido a que no se encontró una coincidencia exacta, la fórmula de VLookup devolvió un # N / A, por lo que el resultado de la función ISNA es VERDADERO. La fórmula SI lo convirtió en una cadena vacía.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), «», VLOOKUP (A8, ProductList, 2, FALSE))
Combinar IFERROR y VLOOKUP
Gracias a Chip Pearson por sugerir esta fórmula.
En Excel 2007, se introdujo una nueva función, IFERROR . Puede usar una fórmula IFERROR con VLookup para verificar varias tablas en busca de un valor y devolver la información relacionada cuando la encuentre. En este ejemplo, tres regiones, Oeste, Este y Central, tienen hojas de pedido. En cada hoja hay un rango con nombre: OrdersW, OrdersE y OrdersC.
En una hoja llamada Pedidos, puede ingresar un ID de pedido y luego usar una VLOOKUP con IFERROR para verificar cada rango con nombre y ver la información sobre el pedido seleccionado.
- En la hoja de Pedido, en la celda B6, ingrese un 4 como OrderID. Ese pedido se realizó en la región Central.
- Para simplemente verificar la tabla de la región Este, la fórmula VLOOKUP en la celda C6 sería:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Presione la tecla Enter, y la fórmula VLOOKUP devuelve un # N / A, porque el ID de pedido 4 no está en la tabla de pedidos de las regiones Este.
- Porque un pedido podría tener se ha colocado en cualquiera de las tres regiones, necesita una fórmula que verifique cada tabla.
Si el ID del pedido no se encuentra en la primera tabla, la fórmula debe verificar la segunda tabla. Si el ID del pedido no está en la segunda tabla, debe verificar la tercera tabla. Si el ID del pedido no está en la tercera tabla, entonces debería aparecer un mensaje «No encontrado» en la celda. - La fórmula IFERROR le permite verificar un valor, luego especifique qué hacer si se encuentra un error. Si usa IFERROR con la fórmula existente, puede mostrar «No encontrado», en lugar del error # N / A:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), «No encontrado») - Para verificar las tres tablas, puede seguir las fórmulas IFERROR y VLOOKUP:
Esto verifica la tabla OrdersE y si se encuentra un error, verifica la tabla OrdersW, luego OrdersC. Si OrderID no se encuentra en ninguna de las tres tablas, el mensaje No encontrado se muestra en la celda.
También puede verificar varias tablas en versiones anteriores de Excel, donde IFERROR no está disponible, usando un fórmula más larga:
VLOOKUP para valores combinados
En algunas tablas, puede que no haya valores únicos cualquier columna de la tabla de búsqueda. Por ejemplo, en la tabla que se muestra a continuación, Chaqueta se enumera dos veces en la columna A. Sin embargo, solo hay un registro para cada combinación de chaqueta y talla: Chaqueta mediana en la fila 4 y Chaqueta grande en la fila 5.
Si necesita encontrar el precio de una chaqueta grande, una VLOOKUP basada solo en la columna A devolvería el precio de la primera chaqueta indicada (Medio ). Estaría subvalorando la chaqueta, vendiéndola por 60,00, en lugar de 65,00.
Para crear valores de búsqueda únicos, puede insertar una nueva columna en el lado izquierdo de la tabla y usar una fórmula para combinar el producto y el tamaño. En la celda A2, la fórmula combina el valor en B2 y el carácter de barra vertical y el valor en C2.
= B2 & «|» & C2
Copie esa fórmula hasta la última fila de datos, de modo que cada fila tenga un valor único en la columna A.
Nota : En lugar del carácter de barra vertical, puede utilizar otro carácter que no esté incluido en sus datos.
Luego, en una fórmula VLOOKUP, combine el producto y el tamaño como Lookup_value. En la celda H1, la fórmula combina el valor en F1 y el carácter de barra vertical y el valor en G1.
= VLOOKUP (F1 &» | «& G1, $ A $ 2: $ D $ 5,4, FALSE)
Nota: El precio ahora está en columna 4, en lugar de la columna 3. ▲ ARRIBA
Solucionar problemas de la fórmula VLOOKUP
Su fórmula VLOOKUP puede devolver un # N / A, aunque el valor que está buscando parece estar en la tabla de búsqueda. Las causas comunes de esto son:
Texto versus número
Caracteres de espacio
Otros caracteres
Texto versus número
Una causa común de este error es que uno de los valores es un número y el otro es texto. Por ejemplo, la tabla de búsqueda puede contener «123 (texto), y el valor a buscar es 123 (un número).
- Si es posible, convierta el texto en números, usando uno de los métodos que se muestran aquí: Convertir texto en números
- Si no puede convertir los datos, puede convertir el valor de búsqueda dentro de la fórmula VLOOKUP, usando una de las siguientes soluciones:
- Valores de búsqueda son texto, la tabla tiene números
- Los valores de búsqueda son números, la tabla tiene texto
- Para averiguar qué valores son texto y cuáles son números, consulte el número detallado o los pasos de resolución de problemas de texto
1. Los valores de búsqueda son texto y la tabla contiene números
Si la tabla de búsqueda contiene números y el valor a buscar es texto, use una fórmula similar a la siguiente:
= VLOOKUP (–A7, Productos!$ A $ 2: $ C $ 5,3, FALSO)
El unario doble (-) convierte el texto en un número y funcionará correctamente incluso si los valores de búsqueda son números.
2. Los valores de búsqueda son números y la tabla contiene texto
Si la tabla de búsqueda contiene texto y el valor a buscar es numérico, use una fórmula similar a la siguiente:
= VLOOKUP (A7 & «»), Productos! $ A $ 2: $ C $ 5,3, FALSO)
O
= VLOOKUP (TEXT (A7, «00000») , Productos! $ A $ 2: $ C $ 5,3, FALSO)
La función TEXTO convierte un número en texto y funcionará correctamente incluso si los valores de búsqueda son texto. En el primer ejemplo, el operador & crea una cadena de texto a partir de un número sin formato. En el segundo ejemplo, un número formateado con ceros a la izquierda (por ejemplo, 00123) coincidiría con un «número» de texto con ceros a la izquierda.
Para ver los pasos para solucionar el problema BUSCARV cuando la tabla de búsqueda tiene valores de texto, mire este breve video tutorial.
Espacios en un valor, y no en el otro
Otra posible causa para que no se encuentre un valor coincidente es una diferencia en los espacios. Uno de los valores puede contener espacios iniciales (o finales o espacios incrustados), y el otro no. Para probar los valores, puede usar la función LEN para verificar la longitud de cada valor.
Por ejemplo: = LEN (A7) devolverá el número de caracteres en la celda A7. Debería ser igual al número de caracteres en la celda correspondiente en la tabla de búsqueda.
Si es posible, elimine los espacios y la fórmula BUSCARV debería funcionar correctamente. Si no puede eliminar los espacios, use la función RECORTAR en BUSCARV para eliminar los espacios iniciales, finales o duplicados. Por ejemplo:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Otros caracteres
Si la función TRIM por sí sola no resuelve el problema , puede probar una de las siguientes sugerencias:
- Función SUBSTITUTE
- Función CLEAN
- Macro para eliminar caracteres web
Función SUBSTITUTE
Utilice la función SUSTITUTE para eliminar caracteres no deseados. Hay un ejemplo en el blog de Contextures: Limpiar datos de Excel con TRIM y SUSTITUIR
Función CLEAN
Otra forma de solucionar los problemas de VLOOKUP es con la función CLEAN, que puede eliminar algunos caracteres no deseados del texto. Hay más información sobre la función CLEAN en esta publicación del blog de Contextures: 30 funciones de Excel en 30 días: 29 – LIMPIAR
Caracteres web en un valor, y no en el otro
Si copió datos de una página web, es posible que contenga espacio HTML que no se separa (& nbsp). David McRitchie ha escrito una macro para eliminarlos, junto con o otros espacios caracteres
Problemas al ordenar la fórmula BUSCARV
Una fórmula BUSCARV puede devolver los resultados correctos al principio, pero luego muestra resultados incorrectos si la lista de elementos está ordenada. Esto puede ocurrir si la referencia al valor de búsqueda incluye un nombre de hoja. Por ejemplo:
= BUSCARV («Formulario de pedido»! B5, Productos! $ B $ 2: $ C $ 6,2, FALSO)
NOTA: Este problema puede ocurrir con otras funciones también, como una fórmula de búsqueda INDEX / MATCH.
Mire este video para ver los pasos para solucionar el problema y descargue el archivo de muestra del problema VLOOKUP Sorting para seguirlo. Las instrucciones escritas están debajo del video.
Nombres de hojas en la referencia
Este tipo de referencia se crea si hace clic en otra hoja mientras construye la fórmula. Tan pronto como lo haga, Excel agregará el nombre de la hoja a cualquier referencia posterior en la fórmula.
En la captura de pantalla arriba, Dress está en la celda B9, y la celda C9 muestra el precio correcto de $ 30.
Sin embargo, después de ordenar los productos AZ, Dress sube a la celda B5, pero la fórmula en la celda C5 sigue haciendo referencia a la celda B9. Debido a los nombres de las hojas en las referencias, Excel conserva las referencias originales, en lugar de mantener una referencia a la fila actual. La celda C5 muestra el precio de un suéter, en lugar de un vestido. ▲ ARRIBA
Solucione el problema
Para solucionar el problema, elimine cualquier nombre de hoja innecesario del Referencias de celda VLOOKUP. Aquí está la fórmula revisada para la celda C5:
= VLOOKUP (B5, Products! $ B $ 2: $ C $ 6,2, FALSE)
Después de eliminar los nombres de hoja innecesarios, la lista se puede ordenar de forma segura y se mostrarán los resultados correctos para cada elemento.
Descargue los archivos de muestra
1. Obtenga el libro de trabajo de muestra BUSCARV (Excel 2007 y posterior). El archivo comprimido está en formato xlsx y no contiene macros.
2. Para Excel 2003, obtenga esta versión del archivo de muestra VLOOKUP. El archivo comprimido está en formato xls y no contiene macros.
3. Para ver el problema que puede ocurrir al ordenar con BUSCARV, obtenga este archivo de muestra del problema de ordenación de BUSCARV. El archivo comprimido está en formato xlsx y no contiene macros.
4. Obtenga el libro de trabajo Búsqueda de precios de productos, que se usa en el video tutorial.El archivo comprimido está en formato xlsx y no contiene macros. ▲ ARRIBA
Más tutoriales
VLOOKUP Número / Solución de problemas de texto
VLOOKUP de otro libro
HLOOKUP
ELEGIR Función
Búsqueda – 2 criterios
BÚSQUEDA
ÍNDICE / COINCIDIR
Comparar funciones de búsqueda
Lista de funciones