Forum Discussion
Solved
Calculating taxes backwards
Dear community, here's one for you math lovers! Basically, what I want to do is calculate backwards how much tax I have to pay to acquire a set amount after tax. See attached file for table. ...
See the attached version; it uses XLOOKUP.
QWeelon This can be done using a VLOOKUP() or INDEX(MATCH()) after subtracting the tax off the range (which could have been a helper column but I put into the formula). I converted the data into a table to make the data range reference easier/better but you could use regular cell ranges too. I also noticed you have multiple tax years on the sheet so I added the ability to select which year and show both the low and high of the range.
=INDEX(Table1[[Low]:[High]],MATCH(P8,Table1[Low]-INDEX(Table1,,MATCH(TEXT(P7,"0"),Table1[#Headers],0)),1),{1;2})If you have an older version of excel you might need this version and you could also add an identical one but with [High] if you wanted both values:
=INDEX(Table1[Low],MATCH(P8,Table1[Low]-INDEX(Table1,,MATCH(TEXT(P7,"0"),Table1[#Headers],0)),1))