Return Marginal Tax Rate

[2709236]

I'm attaching an example.

I need to choose a marginal tax rate based on the YTD earnings in column B and the table in columns E and F. The values in E are the highest amount allowed for that tax bracked. I'd like the appropriate marginal rate to be returned in column C. Is this possible? The only formulas I can think of are a big string of IF statements.

I know you guys have an easier way!

[Cutter]

Here you go. I adjusted your table to reflect the one you refer to at TaxTips.ca and placed VLOOKUP() formulas in Col C that refer to your table.

[2709236]

Unfortunately this isn't helping as the vlookup only returns the next greatest value which is LESS than the lookup value. I need it to bump up to the next marginal bracket.

Does anyone know how to do this? Even my IF nesting strategy isn't working. Help!

[daddylonglegs]

Try changing E12 to a value larger than any in column B, e.g. 1,000,000 then use this formula in C2

=IF(B2="","",MIN(IF(B2<=E$5:E$12,F$5:F$12)))

It's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER

copy down column, see attached

[Cutter]

I son't see the problem you are seeing.

In the sample I provided it is returning the correct value

EDIT: You may not have noticed that I added a penny to the amounts in Col E of the table

[2709236]

Thank you both for your help. I ended up finding the problem in my monsterous IF-nesting formula.

The vlookup was returning the correct values on most, but not all of the tax brackets. There were a few that my corporate team pointed out to me.

I will try the array strategy and let you know. Thank you very much!

[Cutter]

Could you tell me which ones were not returning the right value?

[2709236]

I'm reviewing the vlookup again and I can't see where the error is. It could be that I fat-fingered it somewhere.

[Cutter]

I've been over it again and I can not see any values giving wrong results.

Here is another copy with changes I made highlighted and noted.