Identify VAT rate based on country using VLOOKUP

To identify a VAT rate based on a simple tax rate table, you can use the VLOOKUP function. For this example we have used a Data Validation List to limit the input to EU countries.

Cell G4 contains the following formula:
=VLOOKUP(G3,VAT_table,2,FALSE)

How this formula works

The VLOOKUP function scans the first column of the table and identifies the input value (cell G3). Then it returns the value from the second column, in our case the VAT rate.

G3 – The drop down list with EU countries.
VAT_table – the VAT table containing cells A2:C29

Note: The VLOOKUP function has two matching modes, exact match and approximate match. This is the forth argument in our function. In this case we used the FALSE argument which means the VLOOKUP  function will look for exact match when comparing our input to the country list.

VAT vlookup example

11.73 KB   213 downloads

This example can be applied to a wide range of real life situations. I have recently used this approach to calculate and generate VAT invoices based on the buyer’s country (for EU electronic purchases) but you can expand this to any type of calculation where a criteria determines one of you calculation’s parameters.

If you have additional questions please let me know by posting a comment.