ExcelBanter - Excel 2002: How to make the formula works ?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Excel 2002: How to make the formula works ? (https://www.excelbanter.com/excel-discussion-misc-queries/146638-excel-2002-how-make-formula-works.html)

Mr. Low

Excel 2002: How to make the formula works ?

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

Dave Peterson

Excel 2002: How to make the formula works ?

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

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

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

--

Dave Peterson

Tim Shnell

Excel 2002: How to make the formula works ?

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

roadkill

Excel 2002: How to make the formula works ?

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

"Mr. Low" wrote:

Dear Sir,

I see 2 potential problems. 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

ShaneDevenshire

Excel 2002: How to make the formula works ?

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

Mr. Low

Excel 2002: How to make the formula works ?

Hello Dave,

Many thanks for your formula, the VLookup formula is now working

Low

--
A36B58K641

"Dave Peterson" wrote:

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

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

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

--

Dave Peterson

Mr. Low

Excel 2002: How to make the formula works ?

Dear Sir,

Many thanks for your formula, the VLookup formula is now working

Low

--
A36B58K641

"roadkill" wrote:

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

"Mr. Low" wrote:

Dear Sir,

I see 2 potential problems. 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

Mr. Low

Excel 2002: How to make the formula works ?

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

Mr. Low

Excel 2002: How to make the formula works ?

Hello Shane,

Many thanks for your formulas, the VLookup formula is now working.

Low

--
A36B58K641

"ShaneDevenshire" wrote:

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

All times are GMT +1. The time now is 02:33 PM.