I need help from one of you talented programmers to developing a generic formula to calculate taxes (Cell H17) from a progressive tax table given the year (Cell H15) and adjusted gross income (Cell H16). I have included a sample spreadsheet along with some answers and specific formulas using a XLOOKUP function but would like to be able to just specify the year and adjusted gross income and have it go to the tax table and calculate the tax. The Excel file name is Calculated Tax.xlsm which I believe I uploaded correctly.
I'm trying to avoid a formula using nested "if conditions" as I will be projecting out for 20-30 years.
Any help would be very much appreciated. Thank you in advance.
Hello Kevin,
Unfortunatly, the file upload did not go through. Seems you might have forgot to click the start upload button after you added the file.
Br,
Abders
Apologize - I'm still learning how to upload files and use the forum.
Attached is the file called Calculated Tax.xlsx (I hope) that I was referring to in my original post.
Hello,
You can use XLOOKUP with logical criteria for this scenario, see attached file for an example.
=XLOOKUP(1,(lookup_value1=lookup_array1)*(lookup_value2>=lookup_array2),return_array,,0,-1)
The important part here in your example is to set match_mode to search from last to first.
Br,
Anders
You could also do it using Power Query. See attached.
