Create Invoice/Bill Using VLOOKUP (Step by Step Tutorial)
In this article I will provide a step by step tutorial to explain how you can create an Invoice using VLOOKUP. I’ve explained the basics of using VLOOKUP in VLOOKUP, Excel Functions so I’m assuming you are familiar with the function. You can also download the complete Invoice file here.
As I’ve explained in that article we are assuming that we have the following data in Sheet2:
In the figure above you can see a list of drinks with their associated prices and preparation times. Since each drink has a unique name, the drink name (Column A) will be the Key values. What we want to do is create an invoice on sheet1:
Step 1: Once the user writes a drink name in column A, column C should display the drink price based on the values in sheet2. As explained before in VLOOKUP, Excel Functions this can be achieved using the VLOOKUP formula. Write the following in cell C9:
To prevent this from happening you could easily drag or copy the two formulas down so all the cells in column C and D are covered:
This can be overcome by adding an “IF” function to the cell. Add the following formula to C9:
The “=IF” function has 3 parts:
The first part is the logical test. ISNA checks if the value resulting from the input expression is #N/A or not. If its #N/A, True is returned, otherwise False is returned.
The second part of the =IF function states that if the ISNA() function returned True (meaning that the VLOOKUP function is returning #N/A) then nothing,”” will be printed in the cell.
The third part of the =IF function states that if the ISNA() function returned False (meaning that the VLOOKUP is not returning #N/A) then the results of the VLOOKUP function will be printed in cell C9. The result of applying this =IF function can be seen below:
This =IF formula checks if the value in column C is empty or not. If the value in column C is empty then the value in column D is also changed to nothing, “”. If there is a number in column C then the original formula is applied to column D. The result can be seen below:
Step 6: Another feature you may want to add to the invoice is a drop down list. A drop down list can be added using the data validation feature. Please see Excel VBA Drop Down Lists for more information about creating drop down lists using data validation:
You can also download the complete Invoice file here.