Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Hello,
I have a file with two sheets. One the second sheet, I am using a vlookup to find values in the first sheet. Simple enough. However, I came across a unique problem that I have never encountered. The result I recieved was #n/a. Now, the value does exist from both sheets, even using the find function, it works. When I copied the value from the first sheet to the second sheet, it returns a valid value, not #n/a. I don't understand why? As you can in this example, the 15 digit numbers are the same, but produces two different answers. They should be the same! Format should have nothing to do with it, correct? I hope that this is simple for you to answer. 330000050001068 1.00% 4 (I copied the 15 digit from sheet 1) 330000050001068 #N/A #N/A (Originial 15 digit value from sheet 2) Help Please. MrRJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Assuming you have your 15 digit nos (original and copied from other sheet) in
A1 and A2 try this in an empty cell =IF(A1=A2,"Same","Different") You will, most likely, get "Different" Try this =LEN(A1) and see if it is 15. There might be a blank. Send the file to me... I will take a look. "MrRJ" wrote: Hello, I have a file with two sheets. One the second sheet, I am using a vlookup to find values in the first sheet. Simple enough. However, I came across a unique problem that I have never encountered. The result I recieved was #n/a. Now, the value does exist from both sheets, even using the find function, it works. When I copied the value from the first sheet to the second sheet, it returns a valid value, not #n/a. I don't understand why? As you can in this example, the 15 digit numbers are the same, but produces two different answers. They should be the same! Format should have nothing to do with it, correct? I hope that this is simple for you to answer. 330000050001068 1.00% 4 (I copied the 15 digit from sheet 1) 330000050001068 #N/A #N/A (Originial 15 digit value from sheet 2) Help Please. MrRJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Hi,
Just got back from lunch. I did the If function, and it did return as different. That is interesting. However, I did the LEN function, results were 15. How do I send the file to you? MrRJ "Sheeloo" wrote: Assuming you have your 15 digit nos (original and copied from other sheet) in A1 and A2 try this in an empty cell =IF(A1=A2,"Same","Different") You will, most likely, get "Different" Try this =LEN(A1) and see if it is 15. There might be a blank. Send the file to me... I will take a look. "MrRJ" wrote: Hello, I have a file with two sheets. One the second sheet, I am using a vlookup to find values in the first sheet. Simple enough. However, I came across a unique problem that I have never encountered. The result I recieved was #n/a. Now, the value does exist from both sheets, even using the find function, it works. When I copied the value from the first sheet to the second sheet, it returns a valid value, not #n/a. I don't understand why? As you can in this example, the 15 digit numbers are the same, but produces two different answers. They should be the same! Format should have nothing to do with it, correct? I hope that this is simple for you to answer. 330000050001068 1.00% 4 (I copied the 15 digit from sheet 1) 330000050001068 #N/A #N/A (Originial 15 digit value from sheet 2) Help Please. MrRJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
to get my id add @hotmail.com to to_sheeloo
or click on my name in the post and follow instructions "MrRJ" wrote: Hi, Just got back from lunch. I did the If function, and it did return as different. That is interesting. However, I did the LEN function, results were 15. How do I send the file to you? MrRJ "Sheeloo" wrote: Assuming you have your 15 digit nos (original and copied from other sheet) in A1 and A2 try this in an empty cell =IF(A1=A2,"Same","Different") You will, most likely, get "Different" Try this =LEN(A1) and see if it is 15. There might be a blank. Send the file to me... I will take a look. "MrRJ" wrote: Hello, I have a file with two sheets. One the second sheet, I am using a vlookup to find values in the first sheet. Simple enough. However, I came across a unique problem that I have never encountered. The result I recieved was #n/a. Now, the value does exist from both sheets, even using the find function, it works. When I copied the value from the first sheet to the second sheet, it returns a valid value, not #n/a. I don't understand why? As you can in this example, the 15 digit numbers are the same, but produces two different answers. They should be the same! Format should have nothing to do with it, correct? I hope that this is simple for you to answer. 330000050001068 1.00% 4 (I copied the 15 digit from sheet 1) 330000050001068 #N/A #N/A (Originial 15 digit value from sheet 2) Help Please. MrRJ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Just sent you an email.
"Sheeloo" wrote: to get my id add @hotmail.com to to_sheeloo or click on my name in the post and follow instructions "MrRJ" wrote: Hi, Just got back from lunch. I did the If function, and it did return as different. That is interesting. However, I did the LEN function, results were 15. How do I send the file to you? MrRJ "Sheeloo" wrote: Assuming you have your 15 digit nos (original and copied from other sheet) in A1 and A2 try this in an empty cell =IF(A1=A2,"Same","Different") You will, most likely, get "Different" Try this =LEN(A1) and see if it is 15. There might be a blank. Send the file to me... I will take a look. "MrRJ" wrote: Hello, I have a file with two sheets. One the second sheet, I am using a vlookup to find values in the first sheet. Simple enough. However, I came across a unique problem that I have never encountered. The result I recieved was #n/a. Now, the value does exist from both sheets, even using the find function, it works. When I copied the value from the first sheet to the second sheet, it returns a valid value, not #n/a. I don't understand why? As you can in this example, the 15 digit numbers are the same, but produces two different answers. They should be the same! Format should have nothing to do with it, correct? I hope that this is simple for you to answer. 330000050001068 1.00% 4 (I copied the 15 digit from sheet 1) 330000050001068 #N/A #N/A (Originial 15 digit value from sheet 2) Help Please. MrRJ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
I did not get it...
Send it to after removing "remove." "MrRJ" wrote: Just sent you an email. "Sheeloo" wrote: |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Just re-sent it.
"Sheeloo" wrote: I did not get it... Send it to after removing "remove." "MrRJ" wrote: Just sent you an email. "Sheeloo" wrote: |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Updated file sent back.
Values in second sheet were stored as TEXT whereas in the first sheet they were stored as NUMBERS. You can verify this with ISNUMBER and ISTEXT functions. To convert TEXT to NUMBERS, enter 1 anywhere, Copy it and then select the column with text and choose PASTE SPECIAL|Multiply "MrRJ" wrote: Just re-sent it. "Sheeloo" wrote: I did not get it... Send it to after removing "remove." "MrRJ" wrote: Just sent you an email. "Sheeloo" wrote: |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Thank You...Thank You. It has been many years since I have encountered this
one. Again, many thanks. "Sheeloo" wrote: Updated file sent back. Values in second sheet were stored as TEXT whereas in the first sheet they were stored as NUMBERS. You can verify this with ISNUMBER and ISTEXT functions. To convert TEXT to NUMBERS, enter 1 anywhere, Copy it and then select the column with text and choose PASTE SPECIAL|Multiply "MrRJ" wrote: Just re-sent it. "Sheeloo" wrote: I did not get it... Send it to after removing "remove." "MrRJ" wrote: Just sent you an email. "Sheeloo" wrote: |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup issue
Encounter same problem and the paste special-multiply solution works like a
magic. Bravo, Sheeloo! "Sheeloo" wrote: Updated file sent back. Values in second sheet were stored as TEXT whereas in the first sheet they were stored as NUMBERS. You can verify this with ISNUMBER and ISTEXT functions. To convert TEXT to NUMBERS, enter 1 anywhere, Copy it and then select the column with text and choose PASTE SPECIAL|Multiply "MrRJ" wrote: Just re-sent it. "Sheeloo" wrote: I did not get it... Send it to after removing "remove." "MrRJ" wrote: Just sent you an email. "Sheeloo" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup issue | Excel Worksheet Functions | |||
Vlookup issue | Excel Discussion (Misc queries) | |||
Vlookup issue | Excel Discussion (Misc queries) | |||
VLOOKUP issue | Excel Worksheet Functions | |||
am not sure if its a vlookup issue | Excel Discussion (Misc queries) |