VLOOKUP con Dynamic Col Index

Tabla de contenido


En la función BUSCARV, 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 VLOOKUP para elmarcas columna, entonces BUSCARV debería buscar marcas columna en datos y valor de retorno de esa columna. Esto solucionará nuestro problema.
Hmm … Está bien, entonces, ¿cómo hacemos eso? Utilizando la función Coincidir dentro de la función BUSCARV.

Fórmula genérica

=BUSCARV(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)

Valor de búsqueda: el valor de búsqueda en la primera columna de table_array.
Matriz de tabla: 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.
Encabezados de tabla: 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 su A1: D1.

Entonces, ahora que sabemos lo que necesitamos para col_index dinámico, aclaremos todo con un ejemplo.

Ejemplo de VLOOKUP 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 escritos 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

= BUSCARV (G4, B4: E16, COINCIDIR (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”, MATCH devolverá 2. Si H3 tuviera “Califíquelo”, habría devuelto 4, y así sucesivamente. La fórmula VLOOKUP finalmente tendrá su col_index_num.

= BUSCARV (G4, B4: E16,2,0)

Como sabemos, la función COINCIDIR devuelve el número de índice de un valor dado en el rango unidimensional proporcionado. Por lo tanto, 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.

VLOOKUP 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.

Usar referencias absolutas con BUSCARV

Escriba la siguiente fórmula en la celda H2.

= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, COINCIDIR (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í te he dado referencia absoluta de cada rango excepto la fila en el valor de búsqueda para BUSCARV ($ G2) y la 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.
Similar, H $ 1 permitirá que la columna cambie cuando se copie horizontalmente y restringirá la fila cuando se copie hacia abajo.

Usar 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 usando 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 (Encabezado, Encabezados, 0), 0)

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.

Cómo usar tLa función BUSCARV en Excel

Referencia relativa y absoluta en Excel

Rangos con nombre en Excel

Cómo BUSCARV desde diferentes hojas de Excel

VLOOKUP Varios valores

articulos populares

50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.

Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.

Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.

Cómo utilizar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.

Va a ayudar al desarrollo del sitio, compartir la página con sus amigos

wave wave wave wave wave