Hello Tim,

By using the formula =--MID( ), I have solve the problem.

Thank you anyway for your reply.

Low

--
A36B58K641


"Tim Shnell" wrote:

On Jun 15, 9:34 am, Mr. Low wrote:
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

VLOOKUP will only look for the value in the first column of your
selection. Try using your VLOOKUP formula with the range from B$1:C$6
instead of A$1:C$6.

Tim