Lookup Formula (Any type of lookup) for repeated values

rydajo96 

Instead of working with your formula and table, let me just attach two spreadsheets I've created, partly for my own estimating purposes, partly to demonstrate how one could create dynamic lookup references. And these are both based on the US tax tables.

The "most advanced" is the one called "Federal Tax Calculator," for it actually modifies the table used for lookup, based on the tax filing status. I'll let you try to figure out how that works; suffice it to say for now that it makes use of named ranges to know which table belongs to which filing status.

The slight less advanced is "Tax Tables 2021" which does a little bit of the same, but the separate tables are more clearly in evidence.

All that aside, here's the heart of the formula in Tax Tables 2021 which does lookup each of the columns in the tax tables and do multiplication and addition as needed.

=VLOOKUP(A6,TTbl,4,1)+(VLOOKUP(A6,TTbl,6,1)*(A6-(VLOOKUP(A6,TTbl,8,1)

No IFs at all.

I say that's the heart of the formula; as you'll see, it's embedded in a LET function, which is what makes it possible to determine once only what table to use. Here's the entire formula:

=LET(
TTbl,INDIRECT(VLOOKUP(B6,FilingStatus,2,0)),
VLOOKUP(A6,TTbl,4,1)+(VLOOKUP(A6,TTbl,6,1)*(A6-(VLOOKUP(A6,TTbl,8,1))))

)

And here's a reference that can introduce you to LET, if it's unfamiliar to you. Note: it does require Excel 2021 or newer.