Excel 2002: How to make the formula works ?

G

Guest

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
 
D

Dave Peterson

=MID(A1,2,6)
return text that looks like a number.

=--MID(A1,2,6)
will coerce that text back to a number.
 
T

Tim Shnell

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
Click to expand...

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
 
G

Guest

I see 2 potential problems. First, your formula
"=VLOOKUP(A10,A$1:C$6,3,FALSE)" is looking for a 6 digit value in a column of
10 digit values. Should probably be "=VLOOKUP(A10,B$1:C$6,2,FALSE)".

Also, I suspect your values at A10 etc. are numbers and the values in column
B are text. If so, I'd advise altering your "=MID(A1,2,6)" formula to
"=--MID(A1,2,6)".
Will
 
G

Guest

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)
 
G

Guest

Hello Tim,

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

Thank you anyway for your reply.

Low
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2002: Vlookup formula not working 3

Top