Look account number up in table to determine VAT code

[ChrisCpH]

Hi all, I apologise if this has been answered before, but as anewbie all the terms are a little confusing.

My challenge is this - I am posting all my accounting posts (lineposts?) in an Excel sheet. As part of the linepost, I enter in column C the account number that the amount of the post should be entered into in my accounts. In column H I have to enter the associated VAT code.

Associated with some account numbers, there is a certain VAT code (several accounts will have the same code). Others have no code.

For example:
- if I enter account number 2145 (column B), Then I have to enter the VAT code "I" in column H
- if I enter account number 2133, Then I have to enter the VAT code "UKP"
- if I enter account number 2147, Then I have to leave the cell empty

I was hoping I could make a formula in column H that looks up a list of accounts on another sheet and enters the appropriate VAT code if there is one.

I can reorder an account list on the other sheet with VAT codes so that all the accounts with the same VAT code are under each other (i.e. sort my accounts list according to VAT code).

I hope that this makes sense, if not please ask.

Can anyone help?

Thanks in advance.

 

[James006]

Hi,

You take a look at Debra's excellent explanation (and Video...) of the Vlookup() function ... :wink:

http://www.contextures.com/xlFunctions02.html

HTH

 

[Special-K99]

In Sheet2 enter your account numbers in column A, in column B enter the VAT code for each account.

in Sheet1!H1
=VLOOKUP(C1,Sheet2!A1:B1000,2,0)
and copy down the column

This will pull in the appropriate VAT code for that account.
If it doesnt exist you'll get an #N/A error
Increase the reference to B1000 if you have more than 1000 accounts to look up.

 

[ChrisCpH]

Thank you very much - is there a way to get rid of #N/A error result?

 

[Special-K99]

=IFERROR(formula,"")

replace formula with my formula above

 

[ChrisCpH]

=IFERROR(formula,"")

replace formula with my formula above

So the full formula would be:
=VLOOKUP(C1,Sheet2!A1:B1000,2,0,IFERROR(C1,Sheet2!A1:B1000,"")) ?

 

[Special-K99]

No, my formula is

VLOOKUP(C1,Sheet2!A1:B1000,2,0)

so it should be

=IFERROR(VLOOKUP(C1,Sheet2!A1:B1000,2,0),"")