Having trouble adapting Index Match to a lookup.

[tethlah]

Taxes.xlsx

You guys helped me fix an issue earlier where I was trying to replace a vlookup with index match but I was using a ridiculous amount of IF statements lol. But I'm back at it, and at the final part of the timesheet where I calculate taxes. In the attached sheet you can see where I'm going with it. The equation is to take the paycheck amount, and find the appropriate row to use for calculating your taxes. Basically, your paycheck should be over the value in Column B but under the value in Column C. Then you subtract Column B from the paycheck, and multiply that by Column F, then add the result to Column D to get the amount of taxes that will be taken out of your paycheck.

I tried adapting an index match from earlier, but I'm just going in circles and can't seem to solve it. Any help would be greatly appreciated.

[Gatti]

So: take the % of taxes based on Paycheck value, then multiply the % found by the paycheck value, right?

Put this formula on K6 cell:

Please Login or Register  to view this content.

Is that all?

[humdingaling]

Replicating your equation just broke it down into segments

Please Login or Register  to view this content.

attached worksheet with "helper" method that makes it cleaner

i also showed seperatedly how ColB,D,F are worked out

[tethlah]

Well, that's half the equation. You have to Add the appropriate amount from Column D too. All this does is find the percentage and multiply it by that.
So you have Paycheck*ColF but what I need is ((Paycheck-ColB)*ColF)+ColD

[tethlah]

Replicating your equation just broke it down into segments

Please Login or Register  to view this content.

That's giving the results I'm expecting, thanks!

[humdingaling]

Thanks for the rep

i included file for you with some additional information you might find helpful

[tethlah]

Thanks for the rep

i included file for you with some additional information you might find helpful

Thats awesome, thank you. I've been having issues with following index match, but looking at this example completely demystifies it, I completely get it now. Thanks!