Ivor O’Connor

February 21, 2009

Tutorial / Example on Google vlookup Spreadsheet Doc

Google spreadsheet information is hard to find. It’s not nearly as documented as Microsoft Office or Open Office. Subscribing to their official blog at http://googledocs.blogspot.com/ is helpful. They replaced the usenet group with a forum at http://www.google.com/support/forum/p/Google+Docs?hl=en which is under the general category of google docs support at http://docs.google.com/support/. Here you’ll find descriptions like the following for vlookup:

VLOOKUP(search_criterion, array, index, sort_order)
Searches vertically with reference to adjacent cells to the right. If a specific value is contained in the first column of an array, returns the value to the same line of a specific array column named by index. Search_criterion is the exact value searched for in the first column of the array. Array is the reference, which must include at least two columns. Index is the number of the column in the array that contains the value to be returned. The first column has the number 1. Sort_order (optional) indicates whether the first column in the array is sorted in ascending order.
We’d love to learn how you’re using the VLOOKUP function. You can now show off your finished spreadsheet by editing our knol at [link] (a knol is an authoritative article about a specific topic).

Calculating federal tax withholdings is a good way of demonstrating vlookup. The documentation on federal tax for 2009 can be found at www.irs.gov/pub/irs-pdf/p15.pdf. The percentage method is described on pages 36 through 39. It’s a simple three step process. 1) Calculate withholdings by multiplying the number of them by $70.19. 2) Subtract this from the gross pay. 3) Use this number to access the appropriate table on page 38 and 39 for the federal tax. To make life simpler only the first table was used. (The table for people being paid on a weekly basis.)

Two sheets were used. The first sheet is for the employee. The second sheet contains the tables. The first sheet for completeness sake has columns “pp” for pay period and such. Needed input are Status, number of deductions, and gross pay. Under status there is ‘S’ for single, ‘M’ for married, and ‘H’ for head of household. The unnamed column to the right of these yellow input columns is for validation. If the input validates then it is assigned a ‘P’ and the calculations to the right can proceed. The table sheet is basically a copy of the information from page 38 left in basically the same format making it easy to copy.

The validation column is interesting.

=if(and(or(“s”=E11,”M”=E11,”H”=E11),and(0=F11),and(0G11)),”P”,)

Apparently case is not important in comparisons. I put literals to the left for safety so no accidental assignments could happen. The logic operators remind me of reverse polish notation on HP calculators for some reason. If implicit logic couldn’t have been used there are true() and false() functions to fall back on. If it doesn’t validate then nothing is inserted keeping the sheet uncluttered.

The gross minus deduction is simple.

=if(“P”H11,,if(0>G11-F11*70.19,0,G11-F11*70.19))

In English it reads if the input passes and the deductions are less than the gross then subtract them. Otherwise there is no Federal Tax.

Unfortunately the table lookup with vlookup is not so uncluttered. It seems either a spreadsheet looks cluttered yet full of simple logic or looks uncluttered but with cells full of pages of logic. Or maybe I don’t know the right tricks.

=if(“P”H11,,if(0>if(“M”=E11, (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,3)), (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,3))),,if(“M”=E11, (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,3)), (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,3)))))

In English it starts off verifying the input passed. Then a check is made to make sure the result does not go negative. Unfortunately this duplicates a lot of code. Once this is done the employee’s status, married or not, is checked to determine which of the two weekly tables should be used. Finally the vlookups comes in to play each one looking something like this:

vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,3

The important thing to notice is the vlookup took only 3 arguments. The first argument is the argument to be searched for. The second describes a rectangle to be looked at. The third is the index of the column to be returned. The index starts at 1 not 0. Now you’ll notice this does not work like similarly named functions in open office and M$ office. And the documentation is wrong. An exact match is not needed.

Anyways, you can see vlookup’s usage and play with it in the google spreadsheet here:
http://spreadsheets.google.com/ccc?key=petVh_GdUZbASvuKR4dMOPA&hl=en

You’ll need to login by making yourself an account and then copy the spreadsheet to play with it in any detail. Also some of the code in this post is wrong because wordpress garbled it. See the spreadsheet to see the correct non-garbled code.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: