Vlookup Problem

[HowardC]

I have the old account numbers in Col A (workbook "account numbers). I want to look up the new account number that is equivalent to the old account numberr in another workbook -New account numbers")

The formula I have used is =VLOOKUP(A2,'[New account Numbers.xls]TB CONSOLIDATION'!$F:$G,2,FALSE)

The result I get is "N/A

Your assistance in resolving this will be most appreciated

[RetiredGeek]

Howard,

There is nothing wrong with your formula. Your problem is that the lookup table account numbers are formatted as TEXT and the ones in the sheet with the formula {Col A} are NUMBERS. If you enter the items in col a with a preceeding ' the formula works fine.

[rory]

FWIW, you could also use:
=VLOOKUP(TEXT(A2,"0"),'[New account Numbers.xls]TB CONSOLIDATION'!$F:$G,2,FALSE)

[HowardC]

Thanks for the reply. Is there a formiula that one could incorporate into the Vlookup to do this instead of ghaving to put a ' in front of the numbers?

[RetiredGeek]

Howard,

That's what Rory's formula, in post 3, does.

[HowardC]

Hi Rory & RetiredGeek

Thanks for the help. The formula supplied by Rory does the trick