Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
Change your true to false.
-- -John Please rate when your question is answered to help us and others know what is helpful. "Melbridge" wrote: 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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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 "Melbridge" wrote in message ... |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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
Though i should state that i don't see why that would matter, just something
to try. -- -John Please rate when your question is answered to help us and others know what is helpful. "Melbridge" wrote: 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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) "John Bundy" wrote: Change your true to false. -- -John Please rate when your question is answered to help us and others know what is helpful. "Melbridge" wrote: 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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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 "Melbridge" wrote in message ... 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) "John Bundy" wrote: Change your true to false. -- -John Please rate when your question is answered to help us and others know what is helpful. "Melbridge" wrote: 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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
Yes I thought that was the case - thanks
"Peo Sjoblom" wrote: 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 "Melbridge" wrote in message ... 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) "John Bundy" wrote: Change your true to false. -- -John Please rate when your question is answered to help us and others know what is helpful. "Melbridge" wrote: 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) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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) "Niek Otten" wrote: 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 "Melbridge" wrote in message ... |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) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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. "Melbridge" wrote: 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) "Niek Otten" wrote: 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 "Melbridge" wrote in message ... |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) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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 "Melbridge" wrote in message ... 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) "Niek Otten" wrote: 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 "Melbridge" wrote in message ... |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) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup displays wrong result
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) "Peo Sjoblom" wrote: 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 "Melbridge" wrote in message ... 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) "Niek Otten" wrote: 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 "Melbridge" wrote in message ... |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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
line chart displays wrong values | Charts and Charting in Excel | |||
IF function displays wrong amount | Excel Worksheet Functions |