Completing the Invoice Template
We’ve learned a lot about VLOOKUP so far. In fact, we’ve learned all we’re going to learn in this article. It’s important to note that VLOOKUP can be used in other circumstances besides databases. This is less common, and may be covered in future How-To Geek articles.
Our invoice template is not yet complete. In order to complete it, we would do the following:
- We would remove the sample item code from cell A11 and the “2” from cell D11. This will cause our newly created VLOOKUP formulae to display error messages:We can remedy this by judicious use of Excel’s IF() and ISBLANK() functions. We change our formula from this… =VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)…to this…=IF(ISBLANK(A11),””,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
- We would copy the formulas in cells B11, E11 and F11 down to the remainder of the item rows of the invoice. Note that if we do this, the resulting formulas will no longer correctly refer to the database table. We could fix this by changing the cell references for the database to absolute cell references. Alternatively – and even better – we could create a range name for the entire product database (such as “Products”), and use this range name instead of the cell references. The formula would change from this… =IF(ISBLANK(A11),””,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))…to this… =IF(ISBLANK(A11),””,VLOOKUP(A11,Products,2,FALSE))…and then copy the formulas down to the rest of the invoice item rows.
- We would probably “lock” the cells that contain our formulae (or rather unlock the other cells), and then protect the worksheet, in order to ensure that our carefully constructed formulae are not accidentally overwritten when someone comes to fill in the invoice.
- We would save the file as a template, so that it could be reused by everyone in our company
If we were feeling really clever, we would create a database of all our customers in another worksheet, and then use the customer ID entered in cell F5 to automatically fill in the customer’s name and address in cells B6, B7 and B8.
If you would like to practice with VLOOKUP, or simply see our resulting Invoice Template, it can be downloaded from here.