Excel Intermedio

Un blog para compartir los tips de Excel Intermedio que todos necesitamos!

excel buscarv

BUSCARV con rango dinámico


Para poder trabajar en excel con rangos dinámicos, primero tenemos que tener definido algunas funciones que nos ayudaran a trabajar el contenido, estas son: BUSCARV, CONTARA y DESREF.

FUNCIÓN BUSCARV

BUSCARV con rango dinámico, buscarv excel 300x194
Esta función nos ayuda a, como su mismo nombre lo dice, buscar determinados elementos dentro de una tabla o rango determinado. Por ejemplo, en la imagen, nos ayuda a buscar el nombre de las personas, según su código.
Fórmula: =BUSCARV(Valor que desea buscar, rango en el que quiere buscar el valor, el número de columna en el rango que contiene el valor devuelto, Coincidencia exacta o Coincidencia aproximada indicado como 0/FALSO o 1/VERDADERO).

FUNCIÓN CONTARA

BUSCARV con rango dinámico, funcion contara 300x192 1
Esta función lo que hace es contar cuantas celdas llenas (que no están vacías) hay en un determinado rango.
Por ejemplo, en la imagen, contamos todas las celdas llenas de la columna A. 
BUSCARV con rango dinámico, CONTARA NUEVO 300x208
Si agregamos un dato más en la columna A automáticamente el valor obtenido cambiará. De esta manera, podremos agregar todos los datos que queramos sin cambiar la fórmula.
Fórmula: =CONTARA(valor1; [valor2]; ...)

FUNCIÓN DESREF

Devuelve una referencia a un rango que es un número de filas y de columnas de una referencia dada.
Fórmula: =DESREF(ref, filas, columnas, [alto], [ancho]). En esta ocasión en fila y columna pondremos los valores de 0 y 0 para que comience a contar desde la celda A1, pero aparte tenemos un alto y chancho, el alto puede cambiar dependiendo del número de filas con el que se vaya ampliando la tabla, en este alto se empleará la función CONTARA. El alto va a estar sujeto al número de valores que encuentre en la columna A, el chancho se lo proporcionamos nosotros.
BUSCARV con rango dinámico, DESREF FORMULA 300x203 1
Como se puede observar nos arroja un resultado "#¡VALOR!", en realidad no hay un valor o error, ya que, básicamente, esta celda esta conteniendo todos los números en esta sola celda.
Función: =DESREF(ref; filas; columnas; [alto]; [ancho])

Problema

BUSCARV con rango dinámico, buscarv con na 300x206
Si utilizamos la función BUSCARV, al agregar un nuevo dato a la lista y al buscarlo utilizando la función, nos dará lo siguiente: "#N/A", no lo encontrará. Esto sucede porque nuestro rango, no incluyó a ese nuevo dato.

Solución

La idea es crear una fórmula que nos permita que los rangos puedan ser dinámicos, aquí combinaremos las siguientes funciones vistas anteriormente: DESREF y CONTARA con BUSCARV
BUSCARV con rango dinámico, buscarv con desref 300x207 1
La función anteriormente (DESREF) va a ser agregada, como valor de entrada de la tabla de la función BUSCARV.
Justo en el rango de la tabla, en vez de dejarlo de manera estática, pondremos la función DESREF, que esta combinada con la función CONTARA.

Si se agrega un nuevo dato, la función DESREF ampliará el rango de la tabla. Puede aplicarse de acuerdo a las condiciones de nuestros datos. Ahora que mi rango es dinámico, al agregar mas datos, automáticamente se tomara en cuenta en la función BUSCARV.

Excel utilizado para la demostración: Buscarv con rangos dinamicos

 

 

2 Comentarios

  1. Hola, estoy usando la fórmula BUSCARV y me encuentro con un problema en el tercer parámetro de la formula que es la columna de donde se obtiene el dato buscado. Solo funciona si la columna seleccionada no supera el numero 10. Ejemplo: Si la sintaxis usada es

    =SI(ESERROR(BUSCARV(A3;PROYECTOS!A:J;9;FALSO));"NO ENCONTRADO";BUSCARV(A3;PROYECTOS!A:J;9;FALSO))

    La búsqueda funciona bien, en cambio si la sintaxis es

    =SI(ESERROR(BUSCARV(A3;PROYECTOS!A:J;15;FALSO));"NO ENCONTRADO";BUSCARV(A3;PROYECTOS!A:J;15;FALSO))

    la búsqueda da error.

    Podían por favor indicarme por que se produce este error?
    Muchas gracias.

  2. ¿Podríais poner un ejemplo en los que el rango y la búsqueda no estén en la misma hoja?
    Me da fallo de referencia ese caso y no se qué hago mal.