Forum Discussion

rydajo96's avatar
rydajo96
Copper Contributor
May 06, 2023

Lookup Formula (Any type of lookup) for repeated values

Hi I have the following table, 

 

Chargeable IncomeRate ( % )Gross Tax Payable ($)Cumulative income
On the first 20,0000.0%020000
On the next 10,0002.0%20030000
On the first 30,000-20030000
On the next 10,0003.5%35040000
On the first 40,000-55040000
On the next 40,0007.0%280080000
On the first 80,000-335080000

 

Based don't the table i need to calculate the tax. However, when the tax is at 80000 it is returning 3150 formula instead of 3350 as it used the text highlighted in red. Due to the formula when calculating tax for $42000 returns $490 instead of $690. Could someone assist please 😞

 

This is the formula I am using now. 

=IF(F3<=20000,0,
IF(F3<=30000,(F3-20000)*VLOOKUP(F3,$B$2:$D$4,1,1),
IF(F3<=40000,XLOOKUP(30000,$D$2:$D$6,$C$2:$C$6,0,1)+((F3-30000)*VLOOKUP(F3,$B$5:$D$6,1,1)),IF(F3<=80000,XLOOKUP(40000,$D$2:$D$8,$C$2:$C$8,0,1)+((F3-40000)*VLOOKUP(F3,$B$7:$D$8,1,1))))))

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 06, 2023

    rydajo96 

    =IF(AND($C$8>SUM($C$1:C1),$C$8>SUM($C$2:C2)),C2,$C$8-SUM($D$1:D1))

    An alternative could be a helper column with this formula in cell D2 and filled down.

    =SUMPRODUCT($D$2:$D$5,$E$2:$E$5)

    Then SUMPRODUCT calculates the tax. The taxable amount is in cell C8.

  • mathetes's avatar
    mathetes
    Silver Contributor
    May 06, 2023

    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.

Resources