When I got started originally with spreadsheet software, I was using an Apple //e computer and Apple’s AppleWorks software. For those not familiar with the software, it was a combination word processor, spreadsheet, and database. This was back in 1975.
I knew that, one day, I would have an IBM PC at work and I wanted to understand how the software worked, having learned the basic concepts, so that I could hit the ground running.
In using the spreadsheet, I could easily do the cell calculations for plus, minus, division, group calculations by parentheses, etc. But, I wanted to learn more about the capablities.
I decided that, in order to really learn to use a spreadsheet, I needed a real task to do with it. So, I decided to duplicate the U.S. 1040 income tax form. Once I finished the relatively simple form, I decided to expand it by looking up the tax rates to use in the 1040, based on the calculations just made in the 1040.
That led me to Apple’s implementation of the VLOOKUP function, which turned out to be quite similar to the same function in Lotus 1-2-3 on the PC (later), in Quattro Pro for the PC (even later), and in Excel. The basic structure hasn’t changed in all this time…
The VLOOKUP function allows you to, based on the value in one cell, select a value from a specified range of cells. In the case of simulating the 1040 tax form, this was taking the adjusted gross income and using it to find the base tax plus the percentage to apply to income above the base, e.g., $60,000 income might have a base tax of $7000 for the first $50,000 and then have a 20% tax rate for the above the $50,0010 base.
Don’t bother to check the actual tax tables. This is an example and I made up these income brackets for the example.
Of course, there are also the cases of filing Single, Married Filing Jointly, Married Filing Separately, and Head of Household. But, for simiplicity sake, let’s stick with one table out of the four.
The VLOOKUP function has four parameters that you can feed into it. The first three are mandatory, while the last is optional. The basic VLOOKUP forumula looks like this:
The final parameter (True or False, missing is equal to True) tells Excel to look for an exact match when it’s looking for numbers. If the exact value is not found, the result of the lookup is False.
In this case, by not specifying False, the value $60,000 is found. However, if the lookup had specified FALSE (case is not significant, so False, false, and fAlsE are all equivalent), the lookup table would not have found the $60,000 value, so the vlookup result would have been false, and the cell value set to #N/A.
If you’re looking up text, the test is always equivalent to having the FALSE parameter set.
The challenge is with numbers. I often get extracts provided by others from databases. Sometimes the numbers are numbers, other times they are actually text characters. That is, 123 may be one hundred twenty three or it may be one, two and three. My solution is usually to add a column where I convert to text characters and lookup in a table that has the indices as text characters.
The tax formula, in this example, looks like this:
I could have left the word TRUE out of the formulas, but I like to remind myself of that fourth parameter by always specifying either TRUE or FALSE. That saves trouble in the end…
Notice that I’ve broken the three pieces of the formula into different lines. Although I did that for explanatory purposes, Excel will actually use that formatting in its calculations, as you can see in the first example.
Breaking a formula into multiple lines makes it much easier to debug when it’s not calculating correctly because of a misplaced comma or parenthesis.
While I haven’t figured out how to make Excel break the formula into multiple lines, it’s easy to do outside of Excel and then just past the formula into Excel.
We’ll see how to do that in the next article Make Excel Easier by Using Notepad++