QuickBooks and MS Excel

QuickBooks Desktop versions and QB Online are remarkably robust products for small business accounting and financial reporting.  Most firms need nothing more.  However just about all operations that I have seen over the years employ many MS Excel spreadsheets to assist in calculations or database functions beyond the reach of QB.  MS Excel is a great product that I have used just as much as QB over the years, and I could talk for days about features such as functions, pivot tables, macros and more – but this article is about a powerful database feature called VLOOKUP and HLOOKUP.

=VLOOKUP and =HLOOKUP

VLOOKUP and its sister function HLOOKUP are the closest Excel comes to a database lookup, which is often desired when spreadsheets need to refer to tables of changing information.  VLOOKUP returns cell data from tables arranged vertically (see example below), while HLOOKUP returns cell data from tables arranged in a horizontal fashion.

The good news is that this function can save huge amounts of time in repetitive and error-prone tasks.  But watch out: errors aren’t easy to spot and values can change without warning.

For example, we recently completed a project for a large food products company, where they wanted to import an IIF journal entry into QuickBooks from a production system, however the chart of accounts in the production system was similar to – but not the same as – the chart of accounts in QB.  The IIF will not work unless each line (of about 2,000) is an exact match, so we needed a massive VLOOKUP table every month to convert the production system account names to the ones used in QB.

The purpose of this article is to introduce the function and stimulate thinking about where it can be applied.  I encourage users to study this article and experiment carefully before using it for production purposes.

VLOOKUP is at its best when you must refer to table values that sometimes change.  Examples might include a table with hourly employee pay rates, or a product price chart.  Our article is limited to a simple example since this is an introductory discussion and to best demonstrate the range, not depth of the function.

Invoice Example Using VLOOKUP

Let’s get right to an example with a company that sells the items seen on the april.pricetable tab in the sample spreadsheet which is:

Just 4 products listed here to make things clear – but imagine thousand of products.

Manual lookup of these prices every time an estimate, purchase order, invoice or management report is prepared is a time consuming and error prone activity.  So once the april.pricetable tab is added into our spreadsheet we can employ VLOOKUP in other spreadsheet tabs, such as invoice60201, invoice60202, etc to return the information we need.

Let’s say that a customer orders 11 TS118300’s and 14 TS118400’s, as shown on the following invoice:

Rather than type-in all of the information needed in this invoice we can use VLOOKUP to help.

The format for VLOOKUP is:

VLOOKUP(Value, Table, Column Number, Exact match True or False)

Where:

Value – is a reference to a row where we want Excel to lookup and return additional information.  In this case we use the Model Number to lookup the Description and Sell Price in other parts of the row.

Table – is simply the table where the data resides.  It is important to note that the “Value” (above) must be in Column 1 of the Table and nowhere else.  Further be sure the Table is sorted in ascending order or unexpected results may occur.  In the table above, Column A is sorted in ascending order and Excel respects this even though we are using string variables.

Column Number – tells Excel which column number to return as the result.  For example, when we want the Sell Price we ask for the fourth Column, or Column D.

Exact Match (True or False) – This tells Excel if we demand an exact match to our lookup request, or if otherwise the closest value is OK.  The default value is False, meaning that Excel will return a value even if no exact match is found.  Be careful since this can cause problems.  In our example, like most accounting work, we will demand an exact match; close matches simply won’t do.

In the invoice tabs (such as invoice60201) cell B10 is the item description, and VLOOKUP returns the information we need with the formula

=VLOOKUP(A10,april.pricetable!$A$3:$D$7,2,TRUE)

This simply says to take the Model Number in cell A10, go to the price Table, look up the row for Model TS118300, and return the value in Column 2, which is the Description for a model TS118300 – a “300GB SCSI DRIVE.”

Moving right to cell C10, we need the Sell Price for this same model number, which VLOOKUP provides when we enter the formula  =VLOOKUP(A10,april.pricetable!$A$3:$D$7,4,TRUE).  This is the same as the previous formula, except now we want the value from Column 4, not Column 2.  Again, in both cases the TRUE switch is used since we can’t tolerate a close match; it must be exact.

We must still enter the Quantity of Units in Column D of the invoice, but then everything else is automatic.  So of the 5 columns in this simple invoice, we only need to enter the Model Number and Quantity.

In the next row, row 11 of the invoice60201 tab, we are selling Model TS118400, so the VLOOKUP formulas are the same except we are now concerned with row 11 and not row 10.  So the formulas in row 11 change to:

=VLOOKUP(A11,april.pricetable!$A$3:$D$7,2,TRUE) and

=VLOOKUP(A11,april.pricetable!$A$3:$D$7,4,TRUE).

You can see that these formulas are now easy to copy and adjust and we are getting some real utility in this simple example already.

CAUTION USING VLOOKUP and HLOOKUP

But caution is needed.  The most likely cause of problems would be if the new prices for May are entered on top of the old April values.  In this case, all of the underlying invoices would be immediately and without warning repriced at the May rates.  Perhaps the best way to avoid this problem is to lock and protect the april.prices tab, and to spawn new spreadsheets every month when the adjusted prices come out.  Other difficult to predict problems may occur if the index column – column A in the table – is not entered in ascending order.  Avoid this problem by doing a Data > Sort of the table before using VLOOKUP.  And avoid allowing Excel to use an approximate match by always using the TRUE switch at the end of the formula.

Of course, nothing beats experimenting with VLOOKUP to see how it may be utilized with your particular situation so give it a try.

With these basics, use VLOOKUP and HLOOKUP when reference to changing table values is needed.  These functions may provide faster results with fewer errors than manual lookup and entry of this same information.

Joe Kennedy is the principal of QB-LA QuickBooks Los Angeles, at www.QB-LA.com.