Excel. Use information from two different sources

0
votes

I am trying to find the assigned tax rate for each individual based on the country and annual income from picture below by referring to 2.

Thank you!

enter image description here

=IF(OR(G6=0,G6=-1),0,INDEX(INDEX(TaxRates!D:D,MATCH(A7,TaxRates!A:A,0)):INDEX(TaxRates!D:D,MATCH(A7,TaxRates!A:A,0)+COUNTIF(TaxRates!A:A,A7)-1),MATCH(J7,INDEX(TaxRates!B:B,MATCH(A7,TaxRates!A:A,0)):INDEX(TaxRates!B:B,MATCH(A7,TaxRates!A:A,0)+COUNTIF(TaxRates!A:A,A7)-1),1)))

1
excel
I really hope that's dummy data - if not, take a moment to go delete it and put up dummy data instead. – user1274820
@user1274820 hi, what is dummy data? – WantToLearnNewSkills
@user1274820 hi, actually this is a fake name and is from some exercise, do you know how to solve the question? thx! – WantToLearnNewSkills

1 Answers

0
votes

Assuming that

  • both worksheets start in column A
  • row 1 has column headings
  • formula starts in row 2 on People sheet
  • data in the TaxRates sheet is sorted ascending by country and then ascending by lower income
  • you may want to add a row for 0 to first lower bound for each country.

Read the formula from inside out. It first establishes a lookup range that goes from the first occurrence of the given country to the last row with the given country, then does an approximate match on the lower bound income for these rows.

=INDEX(INDEX(TaxRates!D:D,MATCH(A2,TaxRates!A:A,0)):INDEX(TaxRates!D:D,MATCH(A2,TaxRates!A:A,0)+COUNTIF(TaxRates!A:A,A2)-1),MATCH(I2,INDEX(TaxRates!B:B,MATCH(A2,TaxRates!A:A,0)):INDEX(TaxRates!B:B,MATCH(A2,TaxRates!A:A,0)+COUNTIF(TaxRates!A:A,A2)-1),1))