Forum Discussion
Referencing
In essence, a well designed table, combined with XLOOKUP or one of the others, is functionally very much the same as a nested IF. You are looking up a reference (akin to the first element in an IF), finding it on a table, and then using the different columns in that table, employing different consequences.
I'm attaching a spreadsheet I created just for fun--to do income tax estimating. It does use a very new function, called LET-so if you don't have the most recent Excel it may not work for you--but nested in that is an example of how VLOOKUP could work with the Federal Income Tax Tables. In fact, here's that portion of the formula just in case the more extended doesn't work:
=VLOOKUP(A6,TTbl,4,1)+(VLOOKUP(A6,TTbl,6,1)*(A6-(VLOOKUP(A6,TTbl,8,1))))
Where A6 is the income
TTBl is the tax table (one of the following, depending on filling status)
Note; the tables are laid out for legibility. The really functioning portions are column A (the low end of each tax bracket level) and then column D (offset of 4); Column F (offset of 6); column H (offset of 😎
With that by way of background, what the formula does (it's on a separate sheet in the workbook) is lookup the first row in the table where the income in A6 is exceeded, falls back one row, and then, working across, says
- take the number in the fourth column,
- then the percentage in the sixth column and multiply that by the excess of income (A6) over the number in the 8th column
- add them all up
So for a filing status of Married filing Jointly and an income of $50,000, the relevant table is the one in the middle, rows 14 through 20.
- The $50,000 income goes to row 16 (which exceeds it) and falls back to row 15
- goes over to the fourth column to get 1,990
- multiplies the excess of 50,000 over 19,900 (30100) times 12% to get 3,612
- adds that to 1,990 to get 5,602
And that's the Federal Income tax owed on a Married Filing Jointly income of 50K.
I don't know what all the tax table data is that you need for your business situation, but if they vary as well they might by location (State)...that could be part of your table. I hope my example, though, illustrates how VLOOKUP and a clean table could help.
Thank you for your response and for the compliment on the nested IF. I have seen the problems that come from complex formulas being shared. I do have a couple of things that make me feel a bit more comfortable about using it such as I am the only one who will have access to the formulas and their are only select few refineries that we could possible pull from. Not that that is a perfect answer but I believe it will work until I can build something better.
I have messed with Lookup functions in the past but very little, I was sure that was the answer but couldn't seem to make it work. I will check out your link and see what I can learn there.
Do you have a suggestion on how to re-design it without the nested IF?
In essence, a well designed table, combined with XLOOKUP or one of the others, is functionally very much the same as a nested IF. You are looking up a reference (akin to the first element in an IF), finding it on a table, and then using the different columns in that table, employing different consequences.
I'm attaching a spreadsheet I created just for fun--to do income tax estimating. It does use a very new function, called LET-so if you don't have the most recent Excel it may not work for you--but nested in that is an example of how VLOOKUP could work with the Federal Income Tax Tables. In fact, here's that portion of the formula just in case the more extended doesn't work:
=VLOOKUP(A6,TTbl,4,1)+(VLOOKUP(A6,TTbl,6,1)*(A6-(VLOOKUP(A6,TTbl,8,1))))
Where A6 is the income
TTBl is the tax table (one of the following, depending on filling status)
Note; the tables are laid out for legibility. The really functioning portions are column A (the low end of each tax bracket level) and then column D (offset of 4); Column F (offset of 6); column H (offset of 😎
With that by way of background, what the formula does (it's on a separate sheet in the workbook) is lookup the first row in the table where the income in A6 is exceeded, falls back one row, and then, working across, says
- take the number in the fourth column,
- then the percentage in the sixth column and multiply that by the excess of income (A6) over the number in the 8th column
- add them all up
So for a filing status of Married filing Jointly and an income of $50,000, the relevant table is the one in the middle, rows 14 through 20.
- The $50,000 income goes to row 16 (which exceeds it) and falls back to row 15
- goes over to the fourth column to get 1,990
- multiplies the excess of 50,000 over 19,900 (30100) times 12% to get 3,612
- adds that to 1,990 to get 5,602
And that's the Federal Income tax owed on a Married Filing Jointly income of 50K.
I don't know what all the tax table data is that you need for your business situation, but if they vary as well they might by location (State)...that could be part of your table. I hope my example, though, illustrates how VLOOKUP and a clean table could help.
- Thank you for the information. That helps greatly! I will look into applying that into what I have. What I am trying to do is calculate fuel taxes owed to our state, because we picked up the fuel from a refinery in another state. The tax rate only changes at most once per year and we only bring it from one state into one state.