Introducción a la función BUSCARV
La función BUSCARV en Excel es, sin duda, la función más utilizada y hablada. En cualquier entrevista de trabajo que requiera conocimientos de Excel, esta es la pregunta más frecuente si sabe cómo utilizar la función BUSCARV.
La función BUSCARV se incluye en la categoría de búsqueda de funciones de Excel. La V en VLOOKUP significa vertical. La función se utiliza para buscar datos estructurados verticalmente. Para la búsqueda horizontal, hay una función llamada BUSCARH.
La función BUSCARV de Excel busca la primera coincidencia de un valor dado llamado valor de búsqueda, en una tabla de datos y devuelve el valor del índice de columna dado. La coincidencia puede ser aproximada o exacta.
Aunque BUSCARV es fácil de usar, tiene sus propias limitaciones y contextos. Discutiremos todo, incluidas las fortalezas y debilidades de la función.
Sintaxis:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
¿Qué es el valor de búsqueda?
Valor de búsqueda: Es el valor que desea buscar en una matriz de tabla.
Por ejemplo, si está buscando rollo no. 110 en la mesa del estudiante, entonces su 110 es el valor de búsqueda.
Matriz de tabla: La tabla en la que desea buscar el valor de búsqueda.
Por ejemplo, si busca 110 en la matriz de la tabla B5: E17. Luego B5: E17 es su matriz de tabla.
col_index_number: El número de columna en Table Array desde el que desea obtener los resultados.
Por ejemplo, si en la matriz de la tabla B5: E17 quieres obtener valor de la columna D entonces su índice de columna será 3 (contando de B a D (B, C, D)).
[rango de búsqueda]: FALSO si desea buscar un valor exacto, VERDADERO si desea una coincidencia aproximada.
Cómo utilizar la función BUSCARV
Cuando tiene datos estructurados y desea buscar datos en esos datos, la función BUSCARV es la solución.
Modos de coincidencia de VLOOKUP
VLOOKUP tiene dos modos de coincidencia, coincidencia aproximada y coincidencia exacta. De forma predeterminada, BUSCARV realiza una coincidencia aproximada. Pero si desea forzar la función BUSCARV para hacer una coincidencia exacta, también puede hacerlo. La mayoría de las veces, yo y yo creemos firmemente en otros también, intentamos hacer una coincidencia exacta usando la función BUSCARV. No entiendo por qué los desarrolladores de Excel piensan que a los usuarios les gustaría utilizar principalmente la concordancia aproximada.
1. Coincidencia aproximada de BUSCARV
= BUSCARV (lookup_value, table_array, col_index_number, 1)
En la coincidencia aproximada de BUSCARV, si no se encuentra un valor, el El último valor que es menor que el valor de búsqueda coincide y se devuelve el valor del índice de columna dado.
VLOOKUP por defecto hace una coincidencia aproximada, si omitimos la variable de búsqueda de rango. Una coincidencia aproximada es útil cuando desea hacer una coincidencia aproximada y cuando tenga su matriz de tabla ordenada en orden ascendente.
Vlookup busca el valor y si no encuentra el valor en la matriz de la tabla, entonces coincide con el valor que es menor que ese valor en la matriz de la tabla. Entendamos por un ejemplo.
Ejemplo 1
Tenemos una lista de estudiantes aquí. Cada alumno ha obtenido algunas notas en una prueba. Ahora quiero darles nota de acuerdo a sus notas.
Cualquier estudiante que obtuvo menos de 40 debe ser marcado con una F, el estudiante que obtuvo entre 40 y 60 debe ser marcado con una C, y así sucesivamente como se muestra en la imagen de abajo.
Escribiríamos esta fórmula VLOOKUP en E2 y la arrastraríamos hacia abajo.
= BUSCARV (D2, $ H $ 2: $ I $ 6,2, VERDADERO)
¿Cómo funciona?
En el ejemplo anterior, nuestros valores de búsqueda están en la columna D, comenzando desde D2. La matriz de la tabla es H2: I6 (tenga en cuenta que está ordenada en orden ascendente y absoluto). La columna de la que obtenemos datos es 2. Y el tipo de concordancia que hemos definido aproximado al pasar VERDADERO (nada y 1 significarán lo mismo).
Así que examinemos el primer valor de búsqueda D2 que contiene 40.
- BUSCARV busca 40 en la primera columna (H) de la matriz de tabla H2: I6.
- Encuentra 40 en la segunda posición en la celda H3.
- Ahora se mueve a la segunda columna de H3 a I3 y devuelve D.
En este caso, vlookup encontró la coincidencia exacta. Veamos el siguiente caso.
La segunda búsqueda valora D3 que contiene 36.
- BUSCARV busca 36 en la primera columna de la matriz de tabla H2: I6.
- BUSCARV no pudo encontrar 36 en ninguna parte de la primera columna.
- Dado que BUSCARV no pudo encontrar 36, lo hace coincidir con el último valor encontrado que es menor que el valor de búsqueda.
- El primer valor que es menor que 36 es 0, por lo tanto, devuelve F que está relacionado con 0.
Esto sucede para cada caso aquí. Para 92, el último valor que es menor que 92 es 90. Por lo tanto, se devuelve A para Coincidencia aproximada.
2. VLOOKUP Exact Match
Esta forma más utilizada de BUSCARV y puede que también sea la más útil. Cuando desee buscar el valor exacto en la primera columna de la matriz de la tabla, use la coincidencia exacta. Por ejemplo, si está haciendo una búsqueda desde un ID, lo que más le gustaría sería una coincidencia exacta en lugar de cualquier valor que sea menor que este valor.
Para forzar VLOOKUP a una coincidencia exacta en Excel, pasamos 0 o FALSE como parámetro range_lookup.
= BUSCARV (lookup_value, table_array, col_index_number, 0)
Si el valor no se encuentra en la primera columna de la matriz de la tabla, la fórmula devolverá el error # N / A.
Veamos un ejemplo.
Ejemplo 2
En este ejemplo, quiero escribir solo el número de estudiante en A2 y en B2 quiero buscar el el nombre del estudiante y en C2 su Calificación. Sencillo. La matriz de la tabla es B5: D17. ¿Por qué?
Entonces, para hacer esto, escriba estas dos fórmulas en la celda de la celda B2 y C2 respectivamente.
= BUSCARV (A2, $ B $ 5: $ E $ 17,2,0)
= BUSCARV (A2, $ B $ 5: $ E $ 17,3,0)
Ahora, independientemente del ID que escriba en la celda A2, la función BUSCARV buscará una coincidencia exacta en la columna B y devolverá el valor de col_index dado.
¿Cómo funciona BUSCARV de coincidencia exacta?
La BUSCARV de coincidencia exacta en Excel es simple. Busca el valor de búsqueda en la primera columna de la matriz de tabla dada y devuelve el valor del valor del número de índice de columna dado. Si no se encuentra el valor, BUSCARV devuelve el error # N / A.
Mejores usos de la función VLOOKUP
Vlookup tiene muchos usos. Voy a discutir algunos de los casos de uso más útiles.
3. Use Excel VLOOKUP para calificar en lugar de la compleja función SI anidada
Utilice la coincidencia aproximada BUSCARV para reemplazar la función SI anidada. En el Ejemplo 1, vimos cómo podemos usar vlookup para calificar a los estudiantes de acuerdo con sus calificaciones.
La coincidencia aproximada de BUSCARV es más rápida que si se anida, ya que utiliza la búsqueda binaria internamente.
4. Utilice BUSCARV para recuperar datos
Esta es la tarea más común que se puede realizar fácilmente usando la función BUSCARV en Excel. En el ejemplo 2, hicimos esto para obtener los datos de los estudiantes usando su número de rollo.
5. Verifique si un valor está en un registro usando Vlookup.
VLOOKUP se puede usar para verificar si un valor está en alguna lista o no.
En el Ejemplo 2, cuando escribimos 152, devuelve el error # N / A. Si BUSCARV devuelve # N / A, significa que no encontró el valor dado en ningún lugar de los datos dados.
He preparado un breve artículo sobre esto. Puedes leerlo aquí.
Usar una fórmula VLOOKUP para verificar si existe un valor
Obtener índice de columna automáticamente
Pertenezco a ese grupo de personas que no quieren repetir la misma tarea una y otra vez. Entonces, cambiar el número de col_index una y otra vez me molesta y siempre lo evito. En realidad evito cualquier tipo de edición en mis fórmulas una vez que las he escrito, a menos que y hasta que no haya otra opción.
El índice de la columna se puede calcular automáticamente en Excel. Hay varias formas de hacerlo. Veamos algunos de ellos aquí.
6. Utilice la función BUSCARV con la función COINCIDIR
Entonces, como sabemos, la función COINCIDIR devuelve el índice encontrado del valor coincidente.
Entonces, si tenemos exactamente los mismos encabezados en la ubicación de búsqueda que los datos de origen, entonces podemos usarlos para obtener col_index. ¿Cómo? Dejanos ver…
En los datos a continuación, solo queremos escribir el número de rollo en G2 y queremos obtener el nombre del estudiante, las calificaciones y la calificación en H2, cualquiera que sea el encabezado que escribamos allí.
Escribe esta fórmula en la celda H2
= BUSCARV (G2, B2: E14, COINCIDIR (H1, B1: E1,0), 0)
Ahora, siempre que cambie el Encabezado en H1, ese valor en H2 se mostrará en esa columna.
Puede utilizar un menú desplegable utilizando la validación de datos que tiene todos los encabezados de la tabla, para que sea más fácil de usar.
Aquí BUSCARV hace una coincidencia exacta. Ahora BUSCARV está haciendo su tarea habitual de extraer datos. La magia se realiza mediante la función COINCIDIR en la posición col_index.
Aquí BUSCARV ve cualquier valor en G2 en la primera columna de la matriz de la tabla B2: E14. Ahora en el lugar de col_index tenemos COINCIDIR (H1, B1: E1,0).
La función COINCIDIR busca cualquier valor en H1 en el rango B1: E1 y devuelve el índice del valor de coincidencia.
Por ejemplo, cuando escribimos estudiante en H1 lo busca en rango B1: E1. Se encuentra en C2. por lo tanto, devuelve 2. Si escribimos Calificación devolverá la calificación del estudiante.
Tenga en cuenta que cuando escribimos Región, devuelve # N / A. Porque está fuera del alcance de la tabla. Hablaremos de esto último en el artículo.
Esta es la mejor manera de hacer que BUSCARV sea automático. Hay otros métodos para hacerlo, pero no son tan flexibles como este.
7. Utilice la función BUSCARV con la función COLUMNA
La función de columna devuelve el número de columna de la referencia proporcionada. ¿Y qué necesitamos para obtener datos usando BUSCARV? Col_index. Entonces, usando la función COLUMNA, por supuesto, podemos obtener datos de cualquier conjunto de datos.
Tomemos el ejemplo anterior. Pero ahora necesitamos recuperar datos completos. No solo una columna.
Escriba esta fórmula en H2 y cópiela en I2 y J2.
= BUSCARV ($ G2, $ B $ 2: $ E $ 14, COLUMNA (B1), 0)
Esto le dará un resultado dinámico. ¿Cómo? Vamos a ver.
VLOOKUP funciona como de costumbre. En column_index hemos escrito COLUMNA (B1), que se traducirá a 2, ya que tenemos el nombre del estudiante en la columna 2 de la columna B, devuelve el nombre del estudiante.
Importante: Esto funciona porque tenemos nuestra tabla a partir de la columna B. Si su tabla está en el medio de la hoja, deberá restar o sumar algunos números para que coincida con su requisito.
Por ejemplo, si la tabla anterior comenzara desde C1 en la misma estructura, todas las fórmulas siguientes funcionarían bien.
= BUSCARV ($ G2, $ C $ 2: $ F $ 14, COLUMNA (B1), 0)
= BUSCARV ($ G2, $ C $ 2: $ F $ 14, COLUMNA (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (A1) +1,0)
8. VLOOKUP para fusionar datos
En la mayoría de las bases de datos hay una consulta que se fusiona con tablas, en su mayoría llamada consulta de combinación, pero MS Excel no está en ninguna base de datos, por lo que no tiene consulta de combinación. Pero esto no significa que no podamos unir dos tablas si tenemos algunas columnas comunes en dos tablas.
Entonces, cuando obtenga algunos datos de dos departamentos de la misma empresa, querrá fusionarlos para analizar esos datos.
Por ejemplo, cuando obtiene las calificaciones de los estudiantes de los maestros y luego el registro de asistencia del departamento administrativo, querrá ver qué estudiante obtuvo qué calificación y cuál es su porcentaje de asistencia, en un solo lugar.
Necesitamos fusionarlos en la siguiente tabla.
Calificaciones, debemos obtenerlas de la Tabla de calificaciones y la Asistencia de los datos de asistencia.
Escriba las fórmulas a continuación en la celda D19 para obtener las marcas y arrastre hacia abajo.
= BUSCARV (B19, $ B $ 2: $ D $ 15,3,0)
Escriba la fórmula siguiente en la celda E19
= BUSCARV (B19, $ G $ 3: $ I $ 15,3,0)
Y hemos fusionado dos tablas en una sola. Por supuesto, puede agregar más de dos tablas. Solo necesita obtener datos de todas las tablas que desee en un solo lugar usando BUSCARV.
Mejores prácticas de BUSCARV
Entonces, todos los ejemplos anteriores solo usamos vlookup con datos sin procesar. En todos los ejemplos anteriores, tuvimos cuidado con los datos que estábamos usando. Debíamos tener cuidado al dar referencias de matrices de tablas. Pero hay ciertas formas que pueden reducir este esfuerzo en gran medida.
9. Utilice referencias absolutas con BUSCARV
Es posible que haya notado que en algunos artículos he usado rangos absolutos (rango $ signo). Bueno, las rabias absolutas se utilizan cuando no queremos que se cambie el rango al copiar la celda de fórmula a otras celdas.
Por ejemplo, si tengo = BUSCARV (G2,B2: E14, 2,0), 0) en la celda H2, y si lo copio o arrastro hacia abajo en la celda H3, la fórmula cambiará a = BUSCARV (G3,B3: E15, 2,0). Todas las referencias se cambian relativamente. Aquí es posible que deseemos cambiar la referencia del valor de búsqueda pero no la matriz de la tabla. Porque B2 sale del rango y es posible que queramos tenerlo siempre en nuestras matrices de tablas.
Entonces, para evitar eso usamos referencias absolutas. Hacemos que esos rangos sean absolutos que no queremos cambiar, como table_array en H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Cuando copie H2 en H3, la fórmula será = BUSCARV (G3,$ B $ 2: $ E $ 14, 2,0), 0). Tenga en cuenta que el valor de búsqueda se cambia pero no table_array.
10. Utilice rangos con nombre con BUSCARV
En el ejemplo anterior, usamos referencias absolutas para arreglar table_array. Parece bastante borroso y difícil de leer. ¿Qué tal si solo puedes escribir? 'datos' en lugar de $ B $ 2: $ E $ 14 y haría referencia a $ B $ 2: $ E $ 14 siempre. Facilitará aún más la lectura y la escritura de la fórmula.
Así que seleccione el rango y asígnele el nombre "datos".
Para nombrar un rango, seleccione el rango. Haga clic derecho, haga clic en Definir nombre y nombre el rango que desee. Ahora solo = BUSCARV (G3,datos,2,0), 0) la fórmula funcionará exactamente igual que antes.
Puede leer más sobre rangos con nombre en Excel. He explicado todos y cada uno de los aspectos del rango con nombre aquí.
11. Comodines para coincidencias parciales mediante BUSCARV
VLOOKUP permite la coincidencia parcial. Sí, mientras realiza una coincidencia exacta de VLOOKUP, puede usar operadores comodín para hacer una coincidencia parcial. Por ejemplo, si desea buscar un valor que comience con "Gil", puede utilizar el operador comodín * con "Gil" como "Gil *". Veamos un ejemplo.
Aquí quiero sacar notas del estudiante cuyo nombre comienza con Gil. Para conseguirlo, escribiré a continuación la fórmula.
= BUSCARV ("* Gill", C2: D14,2,0)
Hay dos comodines disponibles en Excel para usar con la función BUSCARV.
Asterisco (*) comodín. Se utiliza cuando el usuario no conoce el número de caracteres que hay y solo conoce algunos caracteres que coinciden. Por ejemplo, si el usuario conoce un nombre que comienza con "Sm", entonces escribirá "Sm *". Si el usuario sabe que un valor de búsqueda termina con "123", entonces escribirá "* 123". (el comodín solo funciona con textos). Y si sabe que "se" viene dentro de un texto, entonces escribirá "* se *".
Signo de interrogación ("?"). Se utiliza cuando el usuario conoce el número de caracteres que faltan. Por ejemplo, si quiero buscar un valor que tiene 4 caracteres pero no sé cuáles son, simplemente escribiré "????" en el lugar del valor de búsqueda.
Vlookup devolverá el valor respectivo del primer valor encontrado que tiene exactamente cuatro caracteres.
Importante: El comodín funciona solo con valores de texto. Convierta números en texto si desea usar comodines con ellos.
Las debilidades de la función BUSCARV
VLOOKUP de hecho es una función poderosa y fácil para buscar valores, pero hay muchas áreas donde VLOOKUP simplemente no es tan útil. Si está trabajando en Excel, también debe conocerlos y saber cómo realizar tareas que BUSCARV no puede hacer.
12. No se puede recuperar el valor de la izquierda de Table_Array
El mayor defecto de la función BUSCARV es que no puede recuperar el valor de la izquierda de la matriz de la tabla. VLOOKUP solo busca a la derecha del valor de búsqueda.
Esto se debe a que BUSCARV busca datos valor de búsqueda en la primera columna de dado matriz de tabla. Nunca devolverá un valor desde fuera de la tabla. Y si intenta mantener la columna izquierda en la matriz de la tabla, se convierte en la primera columna de la matriz de la tabla, por lo tanto, el valor de búsqueda se buscará en ese rango. Lo que probablemente generará un error.
Para buscar valores desde la izquierda de un valor de búsqueda, usamos INDEX-MATCH en su lugar. La función INDEX-MATCH puede buscar valores de cualquier columna en la hoja. En realidad, el rango de búsqueda y el rango desde el que desea buscar valores son totalmente independientes, lo que hace que la coincidencia de índices sea altamente personalizable.
BUSCARV devuelve # N / A incluso cuando existe un valor de búsqueda.
# N / A la función BUSCARV devuelve un error cuando no se encuentra el valor de búsqueda. Puede resultarle molesto cuando existe un valor en el rango, pero BUSCARV devuelve el error # N / A.
14. Cuando los números se formatean como texto
Esto ocurre principalmente cuando los números se formatean como texto. Cuando encuentre usando el comando CTRL + F, Excel lo encontrará, pero cuando intente usar VLOOKUP, devuelve # N / A. Esta tarea se da en la mayoría de las entrevistas como preguntas capciosas.
En la imagen de arriba, puede ver que 101 está ahí, pero la fórmula devuelve un error. ¿Cómo lo manejas? Bueno, hay dos métodos.
1.Convertir texto a número en datos
Puede ver esa etiqueta verde en la esquina superior izquierda en la columna rollno. Indican que algo es inusual en el valor de la celda. Cuando seleccione la celda, mostrará que el número está formateado como texto.
Al hacer clic en el signo de exclamación (!), Se le mostrará la opción Convertir en número. Seleccione todas las celdas y haga clic en esta opción. Todos los valores se convertirán a texto.
2. Convierta su número de búsqueda en texto en la fórmula
En el ejemplo anterior, mutamos los datos originales. Es posible que no desee cambiar nada en los datos originales. Entonces, querrás hacer esto en la fórmula. Para cambiar el número en texto en la fórmula BUSCARV, escriba esto.
= BUSCARV (TEXTO (G2, "0"), B2: D14,2,0)
Aquí la fórmula cambia dinámicamente el número en texto y luego lo empareja con el rango dado.
15. Texto con espacios iniciales y finales
Algunos datos proporcionados por los operadores de entrada de datos y los datos de Internet no están limpios. Pueden tener espacios finales o algunos caracteres no imprimibles. Mientras realiza una búsqueda con estos valores, es posible que vea algún error # N / A. Para evitar esto, primero limpie sus datos. Para eliminar los espacios iniciales, utilice la función TRIM. TRIM elimina cualquier número de espacios iniciales o finales del texto.
Por lo tanto, le sugiero que agregue una nueva columna y obtenga sus datos limpios allí. Luego valora pegarlo. Ahora, si lo desea, puede deshacerse de la columna original.
= RECORTAR (LIMPIAR (texto))
Manejo de errores de BUSCARV
Como sabe, BUSCARV no encuentra un valor, devuelve un error # N / A. Bueno, está bien obtener el error # N / A, tal vez a veces tenga la intención de obtenerlo, como cuando desea verificar si el valor a ya existe en la lista o no. Allí # N / A significa que el valor no está allí.
Pero # N / A se ve feo. ¿Qué tal obtener un resultado fácil de usar, como "No se ha encontrado" o "No se ha encontrado al cliente"? Podemos usar IFERROR con la función BUSCARV para evitar # N / A.
Utilice la siguiente fórmula para atrapar el error en BUSCARV.
= SI.ERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP con MÚLTIPLES criterios
VLOOKUP no puede funcionar con varios criterios.Sí, solo puede tener un criterio para buscar valores usando la fórmula BUSCARV.
Por ejemplo, si tiene el nombre y apellido en dos columnas separadas.
Y ahora, si desea buscar un valor cuyo nombre es John y Apellido Dave, entonces no puede hacerlo normalmente.
Pero hay una solución para esto. Puede crear una columna separada concatenando el nombre y el apellido y luego buscar esa columna de nombre y apellido.
Hay un Alternativa de INDEX-MATCH que puede buscar múltiples criterios sin ninguna columna de ayuda.
17. VLOOKUP recupera solo el primer valor encontrado
Imagina que tienes algunos datos en la región central. Ahora desea obtener los primeros 5 registros de datos de la región central. VLOOKUP solo devolverá el primer valor central en los cinco registros. Este es un telón de fondo importante.
Pero no significa que no podamos lograrlo. Podemos usar una fórmula de matrices complejas como se muestra a continuación.
= ÍNDICE ($ C $ 2: $ C $ 14, PEQUEÑO (SI (G2 = $ A $ 2: $ A $ 14, FILA ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), FILA (1: 1)))
Escriba esta fórmula y presione CTRL + MAYÚS + ENTRAR.
Y esta hecho.
Lo he explicado en el artículo cómo BUSCAR múltiples valores en Excel.
Así que sí, chicos, en este artículo he cubierto todos los aspectos posibles de la función BUSCARV según mi conocimiento. Si cree que me he perdido algo, hágamelo saber en la sección de comentarios a continuación.
Vlookup Top 5 valores con valores duplicados usando INDEX-MATCH en Excel
VLOOKUP Varios valores
VLOOKUP con Dynamic Col Index
Coincidencia parcial con la función BUSCARV
Utilice BUSCARV de dos o más tablas de búsqueda
Vlookup por fecha en Excel
Usar una fórmula VLOOKUP para verificar si existe un valor
Cómo BUSCARV desde diferentes hojas de Excel
BUSCARV con números y texto
Función IF, ISNA y VLOOKUP
Función ISNA y VLOOKUP
Función IFERROR y VLOOKUP
