Rango dinámico para BUSCARV y BUSCARX sin usar Tablas en Excel

Justo ayer me hicieron una pregunta interesante con respecto al uso de BUSCARV y BUSCARX.

Desde hace tiempo promuevo el uso de Tablas en Excel, sobre todo cuando queremos hacer búsquedas en rangos dinámicos, y que siempre se tomen en cuenta los nuevos valores ingresados en las Tablas.

La preguntas que me hicieron fueron:

¿Qué pasa si quiero tener un rango dinámico, pero no quiero usar Tablas?

¿Habrá alguna opción con fórmulas donde se tomen en cuenta los nuevos valores de mi rango?

Ver video Rango dinámico para usar con BUSCARV y BUSCARX

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Introducción

Cuando usaba Excel 2003, antes de que se implementaran las Tablas en Excel (Excel 2007), usaba una fórmula para tener un Rango dinámico y usarlo en las Tablas dinámicas.

Con esta preguntas, la fórmula resurge y ahora la adecúo al uso de las funciones BUSCARV y BUSCARX, y de paso lo vemos aplicado con el nuevo Rango de desbordamiento.

Crear rangos dinámicos usando la función DESREF

Tenemos el siguiente Rango de celdas y deseamos tomar todos los valores, incluso los valores nuevos que se agreguen en las últimas filas.

Figura 1. Rango de celdas en Excel.

Usando la función DESREF vamos a tomar todas las filas y columnas del rango para crear un rango dinámico que luego asignaremos a un nombre definido.

Sintaxis de la función DESREF

Esta función tiene los siguientes argumentos:

• Referencia. Obligatorio. La referencia en la que desea basar el desplazamiento. Referencia debe referirse a una celda o rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR! Valor de error
• Filas. Obligatorio. Es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior izquierda de la referencia pasa a estar cinco filas más abajo que la referencia. Filas puede ser positivo (lo que significa que está por debajo de la referencia de inicio) o negativo (por encima).
• Columnas. Obligatorio. Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda superior izquierda del resultado. Si el argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).
• Alto. Opcional. Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo.
• Ancho. Opcional. Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.

Fórmula con DESREF

Con la siguiente fórmula vamos a indicar que desde A1, deseo devolver todas las filas del rango y todas las columnas.

Usando la función CONTARA vamos a definir el el número de filas que se tomarán y con eso vamos a considerar los valores existentes y los nuevos.

=DESREF(Hoja2!$A$1,0,0,CONTARA(Hoja2!$A:$A),CONTARA(Hoja2!$1:$1))

En la siguiente imagen vemos cómo se devuelve un rango de desbordamiento con todos los datos del rango.

Toma en cuenta que estoy usando Excel 2021 y Office 365.

Para versiones anteriores debes presionar Control + Shift + Enter para ingresar esta fórmula.

Figura 2. Fórmula con DESREF para devolver todos los valores de un rango en Excel.

Devolver columnas para usar con BUSCARX

Tomando en cuenta que con BUSCARX no necesitamos el rango completo, sino columnas completas, con la siguiente fórmula, vamos a tomar sólo una columnas completa.

=DESREF(Hoja2!$A$1,0,0,CONTARA(Hoja2!$A:$A),1)

Puedes replicar la misma fórmula para traer otras columnas.

Figura 3. Fórmulas con DESREF en Excel.

Guardar las fórmulas como Nombres definidos

Una vez que tenemos nuestras fórmulas, vamos a guardarlas en Nombres definidos, para luego llamarlas desde BUSCARV y BUSCARX.

Sigue los siguientes pasos:

• Copia la fórmula desde el signo de =.
• Ve a la pestaña Fórmulas > Administrador de nombres.
• Presiona el botón Nuevo.
• Define el nombre como RANGO.
• Y en Se refiere a, ingresa =DESREF(Hoja2!$A$1,0,0,CONTARA(Hoja2!$A:$A),CONTARA(Hoja2!$1:$1))

Figura 4. Nombres definidos para almacenar fórmulas en Excel.

Usar el nombre definido para búsquedas en Excel

Una vez que guardaste el nombre del rango, ya lo podrás usar como parámetro matriz_tablas en la función BUSCARV.

=BUSCARV(D5,RANGO,2,0)

También puedes usar los otros nombres que hayas creado para los parámetros matriz_buscada y matriz_devuelta en BUSCAX.

=BUSCARX(D13,ID,TITULO,”No existe”)

Descargar el archivo de ejemplo

Rango de búsqueda dinámico para BUSCARV o BUSCARX sin usar Tablas – EXCELeINFO.zip

Si te gustó este tutorial, por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.