1

I am trying to get the Tax Rate from the Sheet 2 (Tax Rates table).

enter image description here

Based on the information of Country and Income from Sheet 1:

  • If it is employed = 0 or -1, then taxrate = 0.
  • Else, I will continue to find the tax rate.

Below is my code:

=IF(OR(G2=0,G2=-1),0,INDEX(INDEX(TaxRates!D:D,MATCH(A3,TaxRates!A:A,0)):INDEX(TaxRates!D:D,MATCH(A3,TaxRates!A:A,0)+COUNTIF(TaxRates!A:A,A3)-1),MATCH(J3,INDEX(TaxRates!B:B,MATCH(A3,TaxRates!A:A,0)):INDEX(TaxRates!B:B,MATCH(A3,TaxRates!A:A,0)+COUNTIF(TaxRates!A:A,A3)-1),1)))

enter image description here

I am not sure where this goes wrong.

Can anyone assist me with the correct solution?

Grant Miller
  • 27,532
  • 16
  • 147
  • 165
WantToLearnNewSkills
  • 51
  • 1
  • 1
  • 6

1 Answers1

2

I'd use sumifs rather than index/match here. This assumes that the tax rates are unique, but that seems reasonable in your example.

=SUMIFS(TaxRates!D:D,TaxRates!A:A,a2,TaxRates!B:B,"<="&J2,TaxRates!C:C,">"&J2)

Plus of course, your outer if function.

MattClarke
  • 1,647
  • 1
  • 11
  • 32