Dear Sir,

I use =MID(A1,2,6) to convert the original reference numbers to six digit
reference in column B.

After that I use VLOOKUP formula in cells B10 to B15 to lookup for the
amount in Table A.

Table A
A B C
Original Ref 6 digits Ref Amount
1 0424907001 424907 277.37
2 0424908001 424908 515.97
3 0424909001 424909 1,237.46
4 0424910001 424910 2,028.02
5 0424911001 424911 1,237.46
6 0424912001 424912 586.85

Table B
Ref Amount
10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE)
11 424906 #N/A
12 424907 #N/A
13 424908 #N/A
14 424909 #N/A
15 424910 #N/A


However all the cells return with #N/A even though B1:B6 and A10:A15 are
values.

I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does
not work either.

For your information the original 10 digits reference in table A was
generated by another business system in Excel Format.

May I know what goes wrong with this and how to overcome this problem?


Thanks


Low

--
A36B58K641