IQ Accounting Solutions

A client once told me about a process that consumed several hours of their week. It involved hand entering quantities on a spreadsheet, then looking up a unit price on another spreadsheet and entering it in the next column. Naturally, they wanted a better way to complete this task. While I couldn’t do anything about having to enter the quantities, I was able to save them a lot of time and reduce opportunity for errors by using Excel’s VLOOKUP function to automatically bring in the correct price from their pricing spreadsheet.

Let’s look at a simple example of how this works. The spreadsheet below has sales information in columns A – D and the price list is in columns F and G.  In a real life situation the price list would more likely be in a different workbook or at least on a different tab in the same workbook.  But the formula still works the same way.

You can type your vlookup formula in from scratch or use the function wizard button on the formula bar to guide you through it. Here is what each part of the formula represents:

• VLOOKUP – stands for vertical lookup.  It means Excel will search a column looking for a match of the data you specify.  When if finds a match, it will move over to the column you choose and use whatever it finds there as the answer to your formula.
• Lookup Value – This is what you want Excel to look for.  You could enter text (by enclosing it in quotation marks) or a number.  But usually this will be a reference to another cell.  In the first example below, we are telling Excel to look for a match to whatever is in cell A2.
• Table Array – is simply the location of the table of information Excel should look in for a match to the “lookup value” and the information you want it to retrieve.  It will always look for a match in the first column of the table array and pull the answer from one of the other columns.  In our formula the table array is F:G meaning that our table is all of columns F and G.  You could also use a specific range of cells instead of entire columns.  For example we could have entered F1:G5 to designate the area that starts in cell F1 and ends at G5. Although if not using entire columns for the table array, you’ll probably want to use an  absolute reference, as in $F$1:$G$5, so that it won’t change when you copy the formula to other cells.
• Column Index Number – tells Excel which column within the table array to look in for the answer to your formula.  Since we want the unit price from column G, and G is the second column in the table array, our column index number is 2.
• Range Lookup – You will enter False in this example. Entering “False” tells Excel to only return an answer if an exact match is found.  Entering “True” or omitting the range lookup will return an answer for the closest match if an exact match isn’t found.

Once you have entered the formula on the first line, you can copy it down the rest of the column.  So entering the formulas shown in column C here:

Will find the correct unit price for each item, as shown here:

VLOOKUP is a great way to combine two sets of data into one. Once you are comfortable with it, you will find many ways to make use of it.

By the way, in case you’re wondering, there’s also an HLOOKUP function that works similarly for horizontal look-ups.