Vlookup Help please

E

Eamon

Hi,

I have a workbook to track employee expenses

Sheet 1 i have...
in cells A8:H8 the following headings
Date, Item Description, Quantity, Currency, "Unit
Price", $ (inc VAT), $ (exc VAT),$ (VAT)

in cells A9:H9 i want...
01-Jan-04, Meals, 1, £, 25.00, $40.54 $34.50 $6.04

this will be based on a lookup table on sheet2 named "range" i.e..
Currency, Country, Exchange Rate $, VAT Rate
£, UK, 1.38, 17.5%
DM, GERMANY, 0.46, 18.0%
Zloty, POLAND, 0.24, 15.0%

in cell F9 i am able to use...
=VLOOKUP(D9,range,3,FALSE)*(C9*E9) to return the value in dollars from the
"rates" table in sheet2, but i also want to include the VAT. Is this
possible?

Thanks

Eamon
 
F

Frank Kabel

Hi Eamon,

assuming your Unit price is 'ex VAT) just use a second VLOOKUP
=VLOOKUP(D9,range,3,FALSE)*(1+VLOOKUP(D9,range,4,FALSE))*(C9*E9)

Frank
P.S: Though it's only an example we use EUR in Germany :)
 
E

Eamon

Frank,

Thank you,

Works perfect ...

As you stated "assuming your Unit price is 'ex VAT)"
what would be the formula if the unit price was 'inc.VAT?

Eamon
 
E

Eamon

Thanks again for your help,

If the unit price included VAT I think the formulas below would be what is
required....

If the unit price included VAT...
$ (inc VAT)
=VLOOKUP(D9,Range,3,FALSE)*(C9*E9)

$ (exc VAT)
=F9-(VLOOKUP(D9,Range,4,FALSE)*F9)

VAT
=F9-G9

Could you please confirm if this is correct?

Best Regards,
Eamon
 
F

Frank Kabel

Hi Eamon

IMHO I think the first formula is correct (assumption unit price
includes VAT) but the second one to calculate the amount exl. VAT is
inccorect. I'd change it to
=(F9/(1+VLOOKUP(D9,Range,4,FALSE)))*100

Frank
 
Top