Vlookup problem

[howard]

I have used the following Vlookup formula to lookup an old acount number in col A and to give me the new account number. It gives me a zero

=IF(ISNA(VLOOKUP(A3,'[M_NECPE 1011.xls]TB CONSOLIDATION'!$F:$G,2,FALSE)),0,VLOOKUP(A3,'[M_NECPE 1011.xls]TB CONSOLIDATION'!$F:$G,2,FALSE))

However, it I put a ' in front of the number, the formula then gives me the correct result i.e the new acc # equivalent which is looked up from another workbook

I would like to amend the formula so that I do not have to put an ' in from of every account number in Col A

Your assistance in this regard will be most appreciated

Sales COS Account Numbers.xls
	A	B	C	D
1	NVSales
2	OldAcc#	NewAcc#
3	3000	TB CONSOLIDATION'!$F:$G,2,FALSE)),0,VLOOKUP(A3,'[M_NECPE 1011.xls]TB CONSOLIDATION'!$F:$G,2,FALSE))]0
4	3001	0
5	3002	0
6	3003	0
7	3004	0
8	3005	0
9	3006	0
10	3007	0
11	3008	0
NV Sales

 

[bebo021999]

Try this:
VLOOKUP(TEXT(A3,0),.......)
or
VLOOKUP(--A3,.......)
or
VLOOKUP(A3*1,.......)

 

[howard]

Thanks for the help, much appreciated