BUSCARV con Índice de Col dinámico
En link: / formulas-and-functions-introduction-of-vlookup-function [la función VLOOKUP], a menudo definimos col_index_no static. Lo codificamos dentro de la fórmula VLOOKUP, como VLOOKUP (id, data, 3,0). El problema surge cuando insertamos o eliminamos una columna dentro de los datos. Si eliminamos o agregamos una columna antes o después de la tercera columna, la tercera columna ya no hará referencia a la columna deseada. Este es un problema. Otro es cuando tiene varias columnas para buscar. Deberá editar el índice de columna en cada fórmula. El simple copiar y pegar no ayudará.
Pero, ¿qué tal si puede decirle a BUSCARV que mire los encabezados y devuelva solo el valor de los encabezados coincidentes? Esto se llama BUSCARV bidireccional.
Por ejemplo, si tengo una fórmula BUSCARV para la columna de marcas, BUSCARV debería buscar la columna de marcas en los datos y devolver el valor de esa columna. Esto solucionará nuestro problema.
Hmm … Está bien, entonces, ¿cómo hacemos eso? Utilizando`link: / lookup-formulas-excel-match-function [la función Match] dentro del`link: / formulas-and-functions-Introduction-of-vlookup-function [función VLOOKUP].
Fórmula genérica
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value: el valor de búsqueda en la primera columna de table_array.
Table_array: el rango en el que desea realizar una búsqueda. Por ejemplo, A2, D10.
Lookup_heading: el encabezado que desea buscar en los encabezados de table_array.
Table_headings: referencia de los encabezados en la matriz de la tabla. P.ej. si la tabla es A2, D10 y los encabezados en la parte superior de cada columna, entonces es A1: D1.
Entonces, ahora que sabemos lo que necesitamos para col_index dinámico, aclaremos todo con un ejemplo.
Ejemplo de BUSCARV dinámico Para este ejemplo, tenemos esta tabla que contiene datos de estudiantes en el rango A4: E16.
Usando rollo no y rumbo, quiero recuperar datos de esta tabla. Para este caso, en la celda H4, quiero obtener los datos del rollo no escrito en la celda G4 y del encabezado en H3. Si cambio el encabezado, los datos del rango respectivo deben recuperarse en la celda H4.
Escribe esta fórmula en la celda H4
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
Dado que nuestra matriz de tabla es B4: E16, nuestra matriz de encabezados se convierte en B3: E3.
Nota: Si sus datos están bien estructurados, los encabezados de columna tendrán el mismo número de columnas y será la primera fila de la tabla.
Cómo funciona:
Entonces, la parte principal es evaluar el número de índice de la columna automáticamente.
Para hacerlo, usamos la función COINCIDIR.
COINCIDIR (H3, B3: E3,0): Dado que H3 contiene «estudiante», COINCIDIR devolverá 2.
Si H3 lo tuviera «Grade», habría devuelto 4, y así sucesivamente. La fórmula VLOOKUP finalmente tendrá su col_index_num.
=VLOOKUP(G4,B4:E16,2,0)
Como sabemos, el`link: / lookup-formulas-excel-match-function [MATCH] `
La función devuelve el número de índice de un valor dado en el rango unidimensional proporcionado. Por lo tanto, link: / lookup-formulas-excel-match-function [MATCH] buscará cualquier valor escrito en H3 en el rango B3: E3 y devolverá su número de índice.
Ahora, siempre que cambie el encabezado en H3, si está en los encabezados, esta fórmula devolverá un valor de la columna respectiva. De lo contrario, tendrá un error # N / A.
BUSCARV en varias columnas rápidamente
En el ejemplo anterior, necesitábamos la respuesta de un valor de columna. Pero, ¿qué sucede si desea obtener varias columnas a la vez? Si copia la fórmula anterior, devolverá errores. Necesitamos hacer algunos cambios menores para que sea portátil.
Usando link: / excel-range-name-absolute-reference-in-excel [Absolute References] con BUSCARV Escriba la fórmula a continuación en la celda H2.
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Ahora copie H2 en todas las celdas en el rango H2: J6 para llenarlo con datos.
Cómo funciona:
Aquí he dado enlace: / excel-rango-nombre-referencia-absoluta-en-excel [referencia absoluta] de cada rango excepto la fila en el valor de búsqueda para BUSCARV ($ G2)
y columna en lookup_value para MATCH (H $ 1).
$ G2: Esto permitirá que la fila cambie para el valor de búsqueda para la función BUSCARV mientras se copia hacia abajo, pero restringirá la columna para cambiar cuando se copie a la derecha. Lo que hará que BUSCARV busque Id de la columna G solo con la fila relativa.
De manera similar, H $ 1 permitirá que la columna cambie cuando se copie horizontalmente y restringirá la fila cuando se copie hacia abajo.
Uso de rangos con nombre
El ejemplo anterior funciona bien, pero resulta difícil leer y escribir esta fórmula. Y esto no es portátil en absoluto. Esto se puede simplificar utilizando rangos con nombre.
Primero haremos algunos nombres aquí. Para este ejemplo, nombré $ B $ 2: $ E $ 14: como Datos $ B $ 1: $ E $ 1: como Encabezados H $ 1: Nómbrelo como Encabezado. Haz que las columnas sean relativas. Para hacerlo, seleccione H1. Presione CTRL + F3, haga clic en nuevo, en Se refiere a la sección eliminar ‘$’ del frente de H.
$ G2: Del mismo modo, asígnele el nombre RollNo. Esta vez hace que la fila sea relativa eliminando ‘$’ del frente de 2.
Ahora, cuando tenga todos los nombres en la hoja, escriba esta fórmula en cualquier lugar del archivo de Excel. Siempre obtendrá la respuesta correcta.
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
Mira, cualquiera puede leer esto y entenderlo.
Entonces, usando estos métodos, puede hacer que col_index_num sea dinámico. Hágame saber si esto fue útil en la sección de comentarios a continuación.
Artículos relacionados:
enlace: / fórmulas-y-funciones-introducción-de-vlookup-function [Cómo utilizar la función VLOOKUP en Excel] enlace:% 20 y% 20Absolute% 20Reference% 20in% 20Excel [Referencia relativa y absoluta en Excel]
link: / excel-range-name-all-about-named-ranges-in-excel [Rangos nombrados en Excel]
link: / lookup-formulas-how-to-vlookup-from-different-excel-sheet [Cómo BUSCARV desde una hoja de Excel diferente]
enlace: / lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
Artículos populares
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-su-productividad [50 atajos de Excel para aumentar su productividad]: Acelere su tarea. Estos 50 atajos le permitirán trabajar aún más rápido en Excel.
link: / formulas-and-functions-introduction-of-vlookup-function [Cómo usar la función VLOOKUP en Excel]: Esta es una de las funciones más utilizadas y populares de Excel que se usa para buscar valores de diferentes rangos y sábanas.
link: / tips-countif-in-microsoft-excel [Cómo usar la función CONTAR.SI en Excel]: Cuente valores con condiciones usando esta función asombrosa. No necesitas filtrar tus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
link: / excel-formula-and-function-excel-sumif-function [Cómo usar la función SUMIF en Excel]: Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.