Match Index Function

[MarindaS]

Good day

I'm facing a dilemma with my Excel spreadsheet, and I hope you can help. I've attached a sample spreadsheet for reference.

The key sheets are:

• 2024_2025 Tax Table: This sheet contains a screenshot of the tax rates. I've manually transcribed the data to use in formulas.
• Workings: Here, I track when an employee received an increase, noting both the increase as a flat rate and as a percentage. In cell E12, I've implemented a formula to adjust the basic salary based on either a flat rate or percentage, depending on the manager's choice.

What I wish to accomplish

I need to calculate the marginal tax rate using the tax tables and the basic salary, with the increase implemented as indicated above, to determine the employee's Net Pay. In cell J10, I've tried using an INDEX MATCH function, but it's returning incorrect values.

Your input and advice would be greatly appreciated.

Kind regards

Marinda

[#2]

@MarindaS 

Try this:

=XLOOKUP(H10,'2024_2025 Tax Table'!$A$3:$A$9,'2024_2025 Tax Table'!$C$3:$C$9,,1,1)

[#3]

Good day

I just need to adjust the requirements.

The marginal tax rate will be used to indicate in which bracket the employee falls. Then I would need to calculate the average tax rate to determine the Net Pay

Here is an explanation from ChatGPT, but struggle to translate this into a formula:

Given:
• Income: R500,000

Tax brackets and rates in South Africa for the tax year 2023/2024:
• Income up to R205,900: 18% tax rate
• Income between R205,901 and R321,600: 26% tax rate
• Income between R321,601 and R445,100: 31% tax rate
• Income between R445,101 and R584,200: 36% tax rate
• Income between R584,201 and R744,800: 39% tax rate
• Income above R744,800: 41% tax rate

Calculation:
• Tax on income up to R205,900 = R205,900 * 0.18 = R37,062
• Tax on income between R205,901 and R321,600 = (R321,600 - R205,900) * 0.26 = R33,324
• Tax on income between R321,601 and R445,100 = (R445,100 - R321,600) * 0.31 = R40,840
• Tax on income between R445,101 and R500,000 = (R500,000 - R445,100) * 0.36 = R19,260
• Total tax paid = R37,062 + R33,324 + R40,840 + R19,260 = R130,486

Now, let's calculate the marginal tax rate:
Since the income of R500,000 falls into the fourth tax bracket, the marginal tax rate is 36%.

Finally, let's calculate the average tax rate:
Average tax rate = Total tax paid / Total income
= R130,486 / R500,000
≈ 0.26097 or 26.097%

So, for an individual with an income of R500,000 in South Africa for the tax year 2023/2024:

• Marginal tax rate: 36%
• Average tax rate: Approximately 26.097%

With this information can determine that the Net Pay is R500 000 * (1-0.26097) = R369 515

Your assistance would be greatly appreciated.

[#4]

@MarindaS 

Calculation:
• Tax on income up to R205,900 = R205,900 * 0.18 = R37,062
• Tax on income between R205,901 and R321,600 = (R321,600 - R205,900) * 0.26 = R33,324
• Tax on income between R321,601 and R445,100 = (R445,100 - R321,600) * 0.31 = R40,840
• Tax on income between R445,101 and R500,000 = (R500,000 - R445,100) * 0.36 = R19,260
• Total tax paid = R37,062 + R33,324 + R40,840 + R19,260 = R130,486

---

With the exception of the first calculation I get different results. Please check.

[#5]

@MarindaS 

Change

=INDEX('2024_2025 Tax Table'!$C$3:$C$9, MATCH(H10, '2024_2025 Tax Table'!$B$3:$B$9, 1))

to

=INDEX('2024_2025 Tax Table'!$C$3:$C$9, MATCH(H10, '2024_2025 Tax Table'!$A$3:$A$9, 1))

[#6]

Thank you @HansVogelaar. That worked. Thank you

[#7]

@Detlef_Lewin 

Currently, I designed the calculations separately as I don't think Excel might be able to look at the variables without involving VBA for example. 

[#8 · Accepted solution]

@MarindaS 

See the attached demo workbook. It uses formulas only.

[#9]

This worked perfectly. Thank you