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.
Jump To:
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:
=VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE)
Step 2: I also mentioned in that article that we would need to add the following formula to cell D9 to calculate the total price for that order:
=C9*B9
Step 3: Now you probably don’t want the rest of the rows to remain empty when you have more items in column A, like the figure below:
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:
Step 4: By doing this one problem might occur. If VLOOKUP can’t find the Key in the database ( the table in sheet2 ) it will return “#N/A”. See the figure below:
This can be overcome by adding an “IF” function to the cell. Add the following formula to C9:
=IF(ISNA(VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))=TRUE, "", VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))
The “=IF” function has 3 parts:
=IF(A9, Sheet2!A$2:C$8, 2, FALSE))=TRUE, "", VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))
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.
=IF(ISNA(VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))=TRUE, "", VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))
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.
=IF(ISNA(VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))=TRUE, "", VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE))
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:
Step 5: Since some of the cells in column C are empty, the values in column D are displaying the text #VALUE!. This can be prevented by changing the formula in column D to the following:
=IF(C9="", "",C9*B9)
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.
See Also:
If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com
One thought on “Create Invoice/Bill Using VLOOKUP (Step by Step Tutorial)”