Hi,

The MID function creates a text return value, your lookup value are probably
numbers. Both the lookup value and the lookup column for VLOOKUP must both
be the same data types.

You can do this at least two ways:
1. Replace the MID function with =VALUE(MID(A1,2,6)
2. Change the VLOOKUP's first argument to text
VLOOKUP(TEXT(A10,"@"),A$1:C$6,3,FALSE)
--
Cheers,
Shane Devenshire


"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