Forum Discussion
Referencing
I am trying to automate referencing from one sheet where I input data to a second sheet that pulls said data and does some calculations on it. The problem I am facing is that the input data has large blank spaces whereas the calculated sheet needs to have no blanks.
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.
4 Replies
Well, Mark...just over 40 people have looked at your post. I'm the first to answer. My guess is that others just didn't want to tackle it. The truth is you should probably start over. I'm impressed by the fantastic nested IF function that you've written--most people could not make something with that many levels of nested IFs into a functional formula. On the other hand, it's a case-study in formulas that would be next to impossible to maintain when conditions change. Excel textbooks actually warn against such formulas for precisely those reasons--who can understand them, let alone fix when conditions change??!!
What you're trying to do, yes, requires data sources that don't have blanks. But you'd be MUCH better served by learning to use one of the many LOOKUP functions. Tax Tables almost always work better when accessed via VLOOKUP (the old standard) or INDEX in conjunction with MATCH (a more flexible old standard), and, most recently and most versatile, the XLOOKUP function. Let me refer you to this website where you can. research those functions. https://exceljet.net
In essence, I'm recommending a basic re-design, which may take some work, but you will be rewarded by the end result being both more intelligible and more robust, far less error prone.
- Mathetes,
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.
