Truco BUSCARV n.° 5: tablas diferentes

Estamos justo en medio de una serie de blogs llamada VLOOKUP Hacks. Comenzamos explorando el cuarto argumento BUSCARV. Luego pirateamos el tercer argumento. Ahora, como habrás imaginado, es hora de hackear el segundo argumento. En esta publicación, permitiremos al usuario recuperar valores de diferentes tablas de búsqueda.

Objetivo

Antes de llegar demasiado lejos, dejemos claro cuál es el objetivo. El objetivo es escribir una función BUSCARV que recupere un valor de una tabla que especifica el usuario. Es decir, hay un montón de tablas de búsqueda diferentes y queremos que BUSCARV recupere un valor de cualquier tabla que identifique el usuario.He creado una breve demostración en video, así como una narrativa escrita como referencia.

Demostración en vídeo

Narrativo

Nos gustaría poder ingresar un monto de ingreso imponible en una celda y hacer que Excel calcule el monto del impuesto correcto. Para calcular el monto del impuesto, necesitamos saber algunas cosas. Para empezar, necesitamos conocer la renta imponible. Entonces, creamos una pequeña celda de entrada ( D7 ) para almacenar la renta imponible, como se muestra a continuación.

Entonces, necesitamos conocer las tasas impositivas. Obtenemos las tasas impositivas del IRS y las ingresamos en una tabla (llamada Single ), como se muestra a continuación.

Necesitaremos recuperar varios valores de la tabla para calcular el monto del impuesto correcto. Comencemos por recuperar la tasa impositiva marginal. La tasa impositiva se encuentra en la segunda columna de la tabla (llamada Única ), por lo que escribimos la siguiente fórmula.

=BUSCARV(D7,Único,2,VERDADERO)

Cuando inspeccionamos el resultado de la fórmula en D8 , confirmamos que devuelve el 25 % cuando la renta imponible es 50 000, por lo que hemos tenido un gran comienzo, como se muestra a continuación.

La función BUSCARV recupera la tarifa de la tabla única. Pero sabemos que el IRS crea tablas de impuestos separadas para cada estado civil. Entonces, creamos una tabla para cada estado civil, como se muestra a continuación.

Estas tablas se denominan Single, MFJ, HH y MFS. Ahora, solo necesitamos permitir que el usuario identifique la tabla de estado civil correcta. Entonces, actualizamos rápidamente nuestro libro de trabajo y agregamos una celda de entrada D6 para permitir al usuario ingresar el nombre de la tabla deseada, como se muestra a continuación.

Ahora, solo necesitamos actualizar nuestra fórmula para hacer referencia a la celda de entrada D6 de la tabla de impuestos en lugar del nombre de la tabla. Entonces, simplemente actualizamos el segundo argumento en consecuencia:

=BUSCARV(D7, D6,2 ,VERDADERO)

Pero ¡maldita sea! Recibimos un error, como se muestra a continuación.

Entonces, aparentemente, BUSCARV no nos permite identificar el nombre de la tabla. ¿O… no?

Eso nos lleva al truco.

Cortar a tajos

Podemos especificar la tabla de búsqueda, pero necesitamos la ayuda de otra función. Entonces, aquí está el truco.

Truco: utilice INDIRECTO como segundo argumento

La función INDIRECTA convierte un valor de texto, como “Único” en D6, en una referencia válida de Excel. Es decir, convertirá la cadena de texto “Single” en el nombre de la tabla correspondiente Single. Actualizamos nuestra fórmula envolviendo la función INDIRECTA alrededor de la referencia de la celda de entrada D6, como se muestra a continuación:

=BUSCARV(D7, INDIRECTA(D6) ,2,VERDADERO)

Y sí… ¡funciona!

Ahora que tenemos esa parte funcionando, podemos finalizar el cálculo de impuestos de la siguiente manera.

El siguiente paso es calcular el ingreso marginal que se gravará a la tasa marginal. En teoría, esto se hace tomando el monto de la renta imponible (por ejemplo, 50,000) y luego restando el monto inicial del tramo impositivo correspondiente (por ejemplo, 37,950).

Afortunadamente, discutimos cómo recuperar el punto inicial de un rango en una publicación anterior usando 1 como tercer argumento. Entonces, la fórmula sería algo como esto:

=D7-BUSCARV(D7,INDIRECTA(D6),1,VERDADERO)

Los resultados se muestran en D9 a continuación.

Luego podemos calcular el impuesto marginal multiplicando el ingreso marginal por la tasa marginal. Y finalmente, necesitamos recuperar el monto de la columna Más (para que podamos agregarlo al monto del impuesto marginal) con la siguiente fórmula:

=BUSCARV(D7,INDIRECTA(D6),3,VERDADERO)

Agregamos el monto recuperado al impuesto marginal y tenemos el monto total del impuesto, como se muestra a continuación.

Con las fórmulas completas, ahora podemos ingresar fácilmente la tabla de ingresos e impuestos sujetos a impuestos en las celdas de entrada, y Excel calcula el impuesto correspondiente. ¡Lindo!

Por supuesto, podríamos combinar todas estas partes en una sola fórmula y proporcionar un menú desplegable en la celda con validación de datos para que el usuario seleccione la tabla. El archivo de muestra incluye estas mejoras en caso de que desee comprobarlas.

Si tiene otros trucos de BUSCARV, compártalos publicando un comentario a continuación.

• BUSCARV Hack 5