Manual lookup of these prices every time there is preparation of an estimate, purchase order, invoice or management report is a time consuming and error prone activity. So once the april.pricetable tab is entered, 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. Rather than type-in all of the information needed in an 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 which is 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.

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.