Calculating Vat using if formula

Question

Hi,

So I created one sheet with categories and then the colum next to it has yes or no to whether it includes Vat.

Second sheet you can use the drop down column to select the category and then you can add the net value . The column after that will calculate the vat automatically but wanted a formula to calculate based on the option selectedin the drop down category as some do not Inc. Vat so it calculates automatically and you don’t have just type zero. Can any one help please

Answer 1

Hi Rachelle

Please, try the following steps

On Sheet1

1. Create a table listing all the Categories and their respective VAT %
2. Optionally, you may give a Named Range to the table. per example: "VATperCategoryTb"

Note1: VAT-exempt Categories are reflected on the table as 0.0% (green cells)

On Sheet2

3. You may then use the formula =B2*VLOOKUP(A2,VATperCategoryTb,2,FALSE) and copy/drag it down

Note2:

If you choose not to create a Named Range the formula would be

=B2*VLOOKUP(A2,Sheet1!$A$1:$B$11,2,FALSE)

Adapt the ranges in the formula according to your real scenario.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Let's say the sheet with the categories and yes/no is named Categories, and the data are in A2:B50.

On the other sheet, you select a category in A2 and down, and enter the net value in B2 and down.

The VAT rate is in cell V2.

Enter the following formula in C2, then fill down:

=IF(XLOOKUP(A2, Categories!$A$2:$A$50, Categories!$B$2:$B$50, "No")="Yes", $V$2*B2, 0)

This should work in Excel in Microsoft 365 and Office 2021, as well as in Excel Online.

For older versions:

=IF(IFERROR(VLOOKUP(A2, Categories!$A$2:$B$50, 2,, FALSE), "No")="Yes", $V$2*B2, 0)