Here is one way to do this.
On your worksheet create a table containing the list of products, with price excluding VAT, VAT percentage, and price including VAT.
For this example I created this table:
J K L M N
4 Product Price VAT % VAT Price
ex.VAT incl. VAT
5 Nuts 1.50 18% 0.27 1.77
6 Bolts 0.90 18% 0.16 1.06
7 Hand 2.56 5% 0.13 2.69
Note that the product name must be in the first column in this table.
You can use formulas in columns M and N to create these values from the original price ex. VAT and the VAT %.
In the part of the worksheet you are using as an Invoice, select the cell that will contain the product name.
For this example I am using cell B5.
From the Ribbon select 'Data' and in 'Data Tools' select Data Validation.
From the drop-down select 'Data Validation' and in the dialog box that opens select List from the 'Allow:' box.
In the 'Source:' box enter the range of cells containing the names for your list.
For this example select cells J5 to J7
It will show in the box as $J$5:$J$7
When you click on cell B5 you will now get a list of available products.
Cell C5 will contain the VAT rate (%)
Cell D5 will contain the price including VAT
Cell E5 will contain the VAT amount
Enter this formula in cell C5
=VLOOKUP($B5,$J$5:$N$7,3,FALSE)Note the $ signs - they are required to allow you to drag and extend this formula and keep the cell references correct.
Select cell C5 and drag/extend it to the right to column E (cell E5)
Now adjust the offset value in the formulas in cells D5 and E5
change D5 from 3 to 5
change E5 from 3 to 4
If you want more items from your list on subsequent rows on your invoice, just select cells B5 to E5 and drag/extend them down as many rows as required.
Changing the product name in cell C5 will result in the correct prices/VAT rates showing in the adjacent cells.
You can look at the Help file for VLOOKUP to find out more about how it works.