Vlookup displays wrong result

[Melbridge]

I have 2 workbooks open and want to lookup data from one and display it in
the other.
The workbook with the data in [CUST NOS.xls] contains the customer name in
column A (sorted) and the customer number in column B
I want to lookup the customer name (which is in column A of the active
workbook) and display the relevant customer number, I'm using the following
formula:

=VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)

For some reason it displays the number from the previous row.
eg
A B
1 Alan 123
2 Bob 234
3 Dean 345
4 John 456

If the customer being looked up was Dean it displays 234 when it should
display 345.
I can bodge it by inserting a cell at the beginning of the customer name
column to push all the names down 1 cell relative to the customer number
column but this is an ugly solution to a problem that shouldn't exist!

Any help? Thanks, Dean. (Melbridge)

 

[John Bundy]

Change your true to false.

 

[Niek Otten]

Hi Dean,

Two things:

a. Setting the 4th argument to TRUE (as you did) or omit it, means an exact match is not required. Set it to FALSE.
b. You probably have one or more hidden characters (like spaces) in either the lookup table or your search argument ("Dean", in
this case). You can easily find out with the LEN() function. Correct that and all should be fine
If not, post again in this thread.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 workbooks open and want to lookup data from one and display it in
| the other.
| The workbook with the data in [CUST NOS.xls] contains the customer name in
| column A (sorted) and the customer number in column B
| I want to lookup the customer name (which is in column A of the active
| workbook) and display the relevant customer number, I'm using the following
| formula:
|
| =VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)
|
| For some reason it displays the number from the previous row.
| eg
| A B
| 1 Alan 123
| 2 Bob 234
| 3 Dean 345
| 4 John 456
|
| If the customer being looked up was Dean it displays 234 when it should
| display 345.
| I can bodge it by inserting a cell at the beginning of the customer name
| column to push all the names down 1 cell relative to the customer number
| column but this is an ugly solution to a problem that shouldn't exist!
|
| Any help? Thanks, Dean. (Melbridge)

 

[John Bundy]

Though i should state that i don't see why that would matter, just something
to try.

 

[Melbridge]

Then it displays #N/A

Just found out what is wrong - there are a load of spaces after each
customer name!!
When I delete these it's ok

Thanks for your help.

Dean (Melbridge)

Change your true to false.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

I have 2 workbooks open and want to lookup data from one and display it in
the other.
The workbook with the data in [CUST NOS.xls] contains the customer name in
column A (sorted) and the customer number in column B
I want to lookup the customer name (which is in column A of the active
workbook) and display the relevant customer number, I'm using the following
formula:

=VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)

For some reason it displays the number from the previous row.
eg
A B
1 Alan 123
2 Bob 234
3 Dean 345
4 John 456

If the customer being looked up was Dean it displays 234 when it should
display 345.
I can bodge it by inserting a cell at the beginning of the customer name
column to push all the names down 1 cell relative to the customer number
column but this is an ugly solution to a problem that shouldn't exist!

Any help? Thanks, Dean. (Melbridge)

 

[Peo Sjoblom]

You should still use FALSE since you are looking for an exact match, that
way you will always know if there is not a match (N/A) thus you can find out
the reason like with your trailing spaces whereas a TRUE or nothing will
return the wrong result


--


Regards,


Peo Sjoblom

Then it displays #N/A

Just found out what is wrong - there are a load of spaces after each
customer name!!
When I delete these it's ok

Thanks for your help.

Dean (Melbridge)

Change your true to false.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

I have 2 workbooks open and want to lookup data from one and display it
in
the other.
The workbook with the data in [CUST NOS.xls] contains the customer name
in
column A (sorted) and the customer number in column B
I want to lookup the customer name (which is in column A of the active
workbook) and display the relevant customer number, I'm using the
following
formula:

=VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)

For some reason it displays the number from the previous row.
eg
A B
1 Alan 123
2 Bob 234
3 Dean 345
4 John 456

If the customer being looked up was Dean it displays 234 when it should
display 345.
I can bodge it by inserting a cell at the beginning of the customer
name
column to push all the names down 1 cell relative to the customer
number
column but this is an ugly solution to a problem that shouldn't exist!

Any help? Thanks, Dean. (Melbridge)

 

[Melbridge]

Yes I thought that was the case - thanks

You should still use FALSE since you are looking for an exact match, that
way you will always know if there is not a match (N/A) thus you can find out
the reason like with your trailing spaces whereas a TRUE or nothing will
return the wrong result


--


Regards,


Peo Sjoblom

Then it displays #N/A

Just found out what is wrong - there are a load of spaces after each
customer name!!
When I delete these it's ok

Thanks for your help.

Dean (Melbridge)

Change your true to false.
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


:

I have 2 workbooks open and want to lookup data from one and display it
in
the other.
The workbook with the data in [CUST NOS.xls] contains the customer name
in
column A (sorted) and the customer number in column B
I want to lookup the customer name (which is in column A of the active
workbook) and display the relevant customer number, I'm using the
following
formula:

=VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)

For some reason it displays the number from the previous row.
eg
A B
1 Alan 123
2 Bob 234
3 Dean 345
4 John 456

If the customer being looked up was Dean it displays 234 when it should
display 345.
I can bodge it by inserting a cell at the beginning of the customer
name
column to push all the names down 1 cell relative to the customer
number
column but this is an ugly solution to a problem that shouldn't exist!

Any help? Thanks, Dean. (Melbridge)

 

[Melbridge]

Thanks Niek - it was spaces at the end of the data in the lookup table -
don't suppose you know how to automatically delete spaces at the end - it
might save me from getting RST holing down the delete key for so long!!

Thanks, Dean (Melbridge)

 

[Melbridge]

To answer my own question - I've just had a rummage around the help file and
found the TRIM function which would appear to what I'm looking for.

Thanks Niek - it was spaces at the end of the data in the lookup table -
don't suppose you know how to automatically delete spaces at the end - it
might save me from getting RST holing down the delete key for so long!!

Thanks, Dean (Melbridge)

Hi Dean,

Two things:

a. Setting the 4th argument to TRUE (as you did) or omit it, means an exact match is not required. Set it to FALSE.
b. You probably have one or more hidden characters (like spaces) in either the lookup table or your search argument ("Dean", in
this case). You can easily find out with the LEN() function. Correct that and all should be fine
If not, post again in this thread.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 workbooks open and want to lookup data from one and display it in
| the other.
| The workbook with the data in [CUST NOS.xls] contains the customer name in
| column A (sorted) and the customer number in column B
| I want to lookup the customer name (which is in column A of the active
| workbook) and display the relevant customer number, I'm using the following
| formula:
|
| =VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)
|
| For some reason it displays the number from the previous row.
| eg
| A B
| 1 Alan 123
| 2 Bob 234
| 3 Dean 345
| 4 John 456
|
| If the customer being looked up was Dean it displays 234 when it should
| display 345.
| I can bodge it by inserting a cell at the beginning of the customer name
| column to push all the names down 1 cell relative to the customer number
| column but this is an ugly solution to a problem that shouldn't exist!
|
| Any help? Thanks, Dean. (Melbridge)

 

[Peo Sjoblom]

If you do this on a regular scale you might want to install a macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


put that macro in your personal xls and attach it to a button or a keyboard
short cut

to install a macro

http://www.mvps.org/dmcritchie/excel/install.htm

once it is installed, just select the range of data and run the macro

it also removes trailing invisible html characters


--


Regards,


Peo Sjoblom

Thanks Niek - it was spaces at the end of the data in the lookup table -
don't suppose you know how to automatically delete spaces at the end - it
might save me from getting RST holing down the delete key for so long!!

Thanks, Dean (Melbridge)

Hi Dean,

Two things:

a. Setting the 4th argument to TRUE (as you did) or omit it, means an
exact match is not required. Set it to FALSE.
b. You probably have one or more hidden characters (like spaces) in
either the lookup table or your search argument ("Dean", in
this case). You can easily find out with the LEN() function. Correct that
and all should be fine
If not, post again in this thread.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 workbooks open and want to lookup data from one and display it
in
| the other.
| The workbook with the data in [CUST NOS.xls] contains the customer name
in
| column A (sorted) and the customer number in column B
| I want to lookup the customer name (which is in column A of the active
| workbook) and display the relevant customer number, I'm using the
following
| formula:
|
| =VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)
|
| For some reason it displays the number from the previous row.
| eg
| A B
| 1 Alan 123
| 2 Bob 234
| 3 Dean 345
| 4 John 456
|
| If the customer being looked up was Dean it displays 234 when it should
| display 345.
| I can bodge it by inserting a cell at the beginning of the customer
name
| column to push all the names down 1 cell relative to the customer
number
| column but this is an ugly solution to a problem that shouldn't exist!
|
| Any help? Thanks, Dean. (Melbridge)

 

[Melbridge]

I have included it in some code which runs when the workbook is opened.

Thanks for the link though, looks like a very useful website.

Cheers, Dean (Melbridge)

If you do this on a regular scale you might want to install a macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


put that macro in your personal xls and attach it to a button or a keyboard
short cut

to install a macro

http://www.mvps.org/dmcritchie/excel/install.htm

once it is installed, just select the range of data and run the macro

it also removes trailing invisible html characters


--


Regards,


Peo Sjoblom

Thanks Niek - it was spaces at the end of the data in the lookup table -
don't suppose you know how to automatically delete spaces at the end - it
might save me from getting RST holing down the delete key for so long!!

Thanks, Dean (Melbridge)

Hi Dean,

Two things:

a. Setting the 4th argument to TRUE (as you did) or omit it, means an
exact match is not required. Set it to FALSE.
b. You probably have one or more hidden characters (like spaces) in
either the lookup table or your search argument ("Dean", in
this case). You can easily find out with the LEN() function. Correct that
and all should be fine
If not, post again in this thread.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 workbooks open and want to lookup data from one and display it
in
| the other.
| The workbook with the data in [CUST NOS.xls] contains the customer name
in
| column A (sorted) and the customer number in column B
| I want to lookup the customer name (which is in column A of the active
| workbook) and display the relevant customer number, I'm using the
following
| formula:
|
| =VLOOKUP(A2,'[CUST NOS.xls]Sheet1'!$A$1:$B$174,2,TRUE)
|
| For some reason it displays the number from the previous row.
| eg
| A B
| 1 Alan 123
| 2 Bob 234
| 3 Dean 345
| 4 John 456
|
| If the customer being looked up was Dean it displays 234 when it should
| display 345.
| I can bodge it by inserting a cell at the beginning of the customer
name
| column to push all the names down 1 cell relative to the customer
number
| column but this is an ugly solution to a problem that shouldn't exist!
|
| Any help? Thanks, Dean. (Melbridge)