BUSCARV: cambia el número de columna automáticamente

Desafortunadamente, BUSCARV, aunque potente, simple y fácil de usar, es una de las funciones menos flexibles de Excel. Imagínense por un momento que hemos ingresado la siguiente fórmula en la celda E6 (ver captura de pantalla a continuación).

=BUSCARV(E4,A2:B10,2,0)

Esta fórmula devuelve el valor de la segunda columna (Columna B): resultado 21.

Pero, ¿qué sucede cuando se inserta una columna entre la Columna A y la Columna B? El rango A2:B10 se actualizará automáticamente para convertirse en A2:C10, pero el número de la columna de búsqueda seguirá siendo el mismo. El valor se seguirá devolviendo desde la segunda columna, aunque esa ya no sea la columna desde la que deseamos devolver el valor.

El resultado ahora ha cambiado a 1,85, en lugar de 21.

Suponiendo que nos hayamos dado cuenta, ahora tenemos que volver a revisar cada una de las fórmulas de BUSCARV para cambiar el 2 por el 3. ¡Doloroso!

Tendemos a incluir el número de columna como un número codificado. Pero si podemos crear el número de columna usando fórmulas, BUSCARV podrá actualizarse cuando se inserten columnas adicionales.

Usando la fórmula COLUMNA

La fórmula COLUMNA() devolverá el número de columna de la celda a la que hace referencia. Aquí hay unos ejemplos:

=COLUMNA(G2)

Esto devolverá 7, porque G es la séptima columna.

=COLUMNA(Z12)

Esto devolverá 26, porque Z es la columna 26.

COLUMNA solo calcula en función de la referencia de celda proporcionada. Al crear una fórmula usando dos funciones COLUMNA, podemos calcular el número de columna requerido. El formato de la fórmula es:

=COLUMNA([columna de retorno de búsqueda])-COLUMNA([primera columna])+1

Donde [primera columna] y [columna de retorno de búsqueda] son ​​las referencias de celda a cualquier celda de esa columna. Usando nuestro ejemplo inicial, sería el siguiente

=COLUMNA(B1)-COLUMNA(A1)+1

Pruébelo: esto nos da un resultado de 2. Ahora podemos reemplazar el número de columna en nuestra función BUSCARV original de la siguiente manera:

=BUSCARV(E4,A2:B10,COLUMNA(B1)-COLUMNA(A1)+1,0)

Si se inserta alguna columna entre la Columna A y la Columna B, toda nuestra fórmula se actualizará y aún devolverá los valores correctos.

=BUSCARV(E4,A2:C10,COLUMNA(C1)-COLUMNA(A1)+1,0)

Otras ventajas de usar COLUMNA en BUSCARV

Este método de utilizar la fórmula COLUMNA tiene otra gran ventaja; la fórmula ahora se puede arrastrar, copiar y mover.

=BUSCARV(E$4,$A$2:$B$10,COLUMNA(B$1)-COLUMNA($A$1)+1,0)

Al insertar los signos $ en los lugares correctos, el número de columna cambiará a medida que se copie la fórmula.

Descargue la hoja de referencia de BUSCARV avanzada

Descargue la hoja de referencia de BUSCARV avanzada. Incluye la mayoría de los consejos y trucos que hemos cubierto en esta serie, incluidos cálculos más rápidos, criterios múltiples, búsqueda por la izquierda y mucho más.

Descárgalo y fíjalo en el trabajo; incluso puedes reenviarlo a tus amigos y compañeros de trabajo.

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0166 BUSCARV Avanzada.pdf

Tener acceso

Otras publicaciones en la serie Mastering VLOOKUP

• Cómo utilizar BUSCARV
• BUSCARV: ¿Qué hace la declaración Verdadero/Falso?
• BUSCARV: Cómo calcular más rápido
• Cómo BUSCAR V a la izquierda
• BUSCARV: cambia el número de columna automáticamente
• BUSCARV con múltiples criterios
• Usando comodines con BUSCARV
• Cómo usar BUSCARV con columnas y filas
• Expandir automáticamente el rango de datos BUSCARV
• BUSCARV: busque el enésimo elemento (sin columnas auxiliares)
• BUSCARV: enumera todos los elementos coincidentes
• Hoja de referencia avanzada de BUSCARV