Truco BUSCARV n.° 2: búsquedas de rango

En la primera publicación de VLOOKUP Hacks, hablamos sobre cómo el cuarto argumento afecta el orden de clasificación. Pero hay más que descubrir sobre este cuarto argumento. Entonces, retomemos justo donde lo dejamos.

Hasta ahora, entendemos que el cuarto argumento le dice a Excel si estamos buscando un valor entre un rango de valores o un valor coincidente exacto. Su nombre oficial es range_lookup y ahora es el momento de profundizar en lo que realmente significa.

He preparado una demostración en video, así como una narrativa detallada a continuación como referencia.

Demostración en vídeo

Narrativa detallada

Cuando el cuarto argumento es VERDADERO o se omite, le indica a Excel que realice una búsqueda de rango. ¿Qué es exactamente una búsqueda de rango? Significa que está buscando un valor entre un rango de valores. La forma más rápida de explicar esto es con un ejemplo y una imagen.

Supongamos que el gerente de ventas creó un programa de incentivos de ventas para el mes. Le gustaría que usted pagara una bonificación a los representantes de ventas en función de sus ventas del mes. Luego le proporciona la siguiente tabla:

Si un vendedor tuvo ventas de 1200 en el mes, encontrará fácilmente el monto del bono de 50. Si un vendedor tuvo ventas de 12,345, podrá determinar que el monto del bono es 500.

Cuando hace esto manualmente, no busca un monto de ventas que coincida exactamente. Está buscando un monto de ventas que se encuentre entre un punto inicial y final.

¡Eso es EXACTAMENTE lo que significa el cuarto argumento! Esa es una “búsqueda de rango”.

Entonces, cuando el cuarto argumento es VERDADERO, le está indicando a Excel que realice una búsqueda de rango. Cuando el cuarto argumento es FALSO, le está indicando a Excel que busque un valor coincidente exacto.

Nota: Es posible que vea 0 usado en lugar de FALSO en el cuarto argumento. Excel evalúa 0 como FALSO y cualquier número distinto de cero como VERDADERO.

Ahora que tenemos el concepto general, profundicemos en los detalles de Excel.

Cortar a tajos

Cuando los humanos realizamos una búsqueda de rango, nos encanta ver tanto el punto inicial como el final. Por ejemplo, en la ilustración anterior del bono de ventas, hay columnas Desde y Hasta. Poder ver ambos lados del rango nos hace sentir cálidos y confusos. Contenido. Cómodo.

Pero aquí está el truco: ¡BUSCARV solo necesita la columna De!

Las implicaciones de esto son importantes. Entonces, analicémoslos. Primero, aquí hay una tabla actualizada que funcionaría perfectamente con BUSCARV:

Así es como me gusta pensar en BUSCARV. Me gusta pensar que funciona en dos etapas. En la etapa uno, se ve SÓLO en la primera columna. Comienza en la parte superior y desciende una fila a la vez buscando su valor coincidente. Una vez que encuentra una coincidencia, ingresa a la etapa dos, donde dispara hacia la derecha para recuperar el valor relacionado.

Entonces, cuando el cuarto argumento es VERDADERO (u se omite), buscará en la columna Ventas hasta encontrar su fila. Cualquier monto de ventas que sea = 0 y 1000 devolverá un monto de bonificación de 0 . Y las ventas = 1000 y 2500 devolverán 50 . Etcétera.

Ahora que vemos cómo funciona esto, es fácil entender por qué los datos deben ordenarse en orden ascendente cuando el cuarto argumento es VERDADERO. Para que BUSCARV devuelva un resultado preciso al realizar una búsqueda de rango, la tabla debe ordenarse en orden ascendente según la columna de búsqueda. Con suerte, esto ayudará a aclarar el problema del orden de clasificación, que analizamos detalladamente en la primera publicación sobre cómo hackear BUSCARV .

Exploremos esta capacidad con algunos ejemplos.

Ejemplo 1: bonificación

Si quisiéramos que Excel determinara el monto del bono en función de las ventas, escribiríamos la siguiente fórmula en la celda C7 para recuperar el monto del bono de la Tabla 1:

=BUSCARV(B7,Tabla1,2,VERDADERO)

Cuando el monto de las ventas es 12,345, BUSCARV devuelve el monto de bonificación esperado de 500, como se muestra a continuación.

Esa es la operación básica de las búsquedas de rango, pero podemos aplicarla de muchas maneras diferentes. Por ejemplo, podemos hacer una búsqueda de valores de fecha.

Ejemplo 2: Períodos Fiscales

Períodos fiscales… ¿te refieres a fechas? Sí… ¡BUSCARV puede incluso funcionar con fechas! Por ejemplo, digamos que necesitamos crear períodos fiscales basados ​​en las fechas de las transacciones. Podríamos configurar una tabla de período fiscal (Tabla 3), como esta:

Entonces, sería fácil hacer que BUSCARV recupere la etiqueta del trimestre correspondiente para un conjunto de transacciones. Por ejemplo, podríamos usar BUSCARV para completar la columna D que se muestra a continuación.

La fórmula escrita en D15 y luego completada es:

=BUSCARV(B15,Tabla3,2,VERDADERO)

Ejemplo 3: columna única

Incluso podemos realizar una búsqueda de rango en una tabla de búsqueda de una sola columna. Esta técnica proporciona una manera sencilla de devolver el punto inicial de un rango. Por ejemplo, si necesitamos encontrar la fecha de inicio del período de pago, podríamos crear una tabla de fechas de inicio del período de pago, como esta:

Luego, podemos usar BUSCARV para devolver el valor de la primera columna de la tabla (Tabla4), así:

=BUSCARV(B18,Tabla4,1,VERDADERO)

Podríamos escribir la fórmula en D18 y completarla, como se muestra a continuación:

Entonces, eso es lo que significa realizar una búsqueda de rango. En la próxima publicación, hablaremos más sobre las implicaciones del cuarto argumento y cómo podemos usarlo para ayudarnos a realizar comparaciones de listas, también conocidas como conciliaciones.

• Archivo de Excel de muestra