Identify VAT rate based on country using VLOOKUP

March 17, 2022

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.

Select VAT rate using VLOOKUP

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 fourth argument in our function. In this case, we used the FALSE argument which means the VLOOKUP  function will look for the exact match when comparing our input to the country list.

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 criterion determines one of your calculation's parameters.

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

About me
Radu from ExcelExplained

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

  • serg says:

    Many thanks mate, you helped me a lot. God bless you

    • Radu says:

      Hi Serg,

      I’m glad I was able to help. This is the reason I’ve created the blog in the first place, to help people like you get better at Excel.

      All the best,
      Radu

  • Essential Functions

    Popular Articles

    Excel courses

    I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
    View courses
    Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

    Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
    Copyright © 2017-2022 ExcelExplained.com
    >