|
vlookup returns #N/A if value contains letters and range numbers
A B C
040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. |
vlookup returns #N/A if value contains letters and range numbers
I am surprised that this works at all, as your final parameter of the
VLOOKUP is zero, indicating that you want an exact match - you can't use * as a wildcard. Also, your lookup array is C:D, but you only want to return from column 1, so the array could be C:C. The LEFT function will return a text value, so you must ensure that all the entries in column C are text values - the way it stands, 1102900068 would be interpreted as numeric, so you won't find an exact match. Highlight column C and click Format | Cells | Number (tab) and select Text. Then change your formula to: =VLOOKUP(LEFT(B1,10),C:C,1,0) and ensure that all your values in column C are 10 characters long. Hope this helps. Pete checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. |
vlookup returns #N/A if value contains letters and range numbers
my guess...
The value in C2 is a real number--if you used: =isnumber(c2) in an empty cell, what gets returned? I'm guessing True. And =left() returns a string--and excel treats a string that looks like a number differently from a that real number. '123 < 123 (text 123 < number 123) ==== You have a couple of solutions. One is to convert all those values in column C to text. You can use a helper column (D??) and do something like: =c1&"" and drag down. Then select column D edit|copy select column C edit|Paste special|Values and delete column D (we're done with it). Then format the "new" column C as Text so that any new entries/changes to existing entries will be Text. ==== Another way is to use a different formula: This array formula works ok for me: =VLOOKUP(LEFT(B2,10)&"*",""&C1:C100,1,0) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. ==== Since you're actually returning the first column in the lookup range, =vlookup() seems like an unusual choice. I think I'd use this array formula: =INDEX(C1:C100,MATCH(LEFT(B2,10)&"*",""&C1:C100,0) ) (still array entered) ==== One of the differences between these two alternative formulas is what is returned. In the first (=vlookup()), a text value is returned. In the second formula (=index(match()), whatever is in column C will be returned--text or number. checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson |
vlookup returns #N/A if value contains letters and range numbers
You can use wildcards in =vlookup().
And if the values in column C are longer than 10 characters, this formula won't work the way the OP intended (as I read it). Pete_UK wrote: I am surprised that this works at all, as your final parameter of the VLOOKUP is zero, indicating that you want an exact match - you can't use * as a wildcard. Also, your lookup array is C:D, but you only want to return from column 1, so the array could be C:C. The LEFT function will return a text value, so you must ensure that all the entries in column C are text values - the way it stands, 1102900068 would be interpreted as numeric, so you won't find an exact match. Highlight column C and click Format | Cells | Number (tab) and select Text. Then change your formula to: =VLOOKUP(LEFT(B1,10),C:C,1,0) and ensure that all your values in column C are 10 characters long. Hope this helps. Pete checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson |
vlookup returns #N/A if value contains letters and range numbe
|
vlookup returns #N/A if value contains letters and range numbe
Thanks Dave. I loved your last 2 formulas for the problem.
I was trying to subsitute C1:C100 with C:C but it would not work. As I copy the formula down C1:C100 change to C2:c101,C3:c102 ect any solutions? Mark "Dave Peterson" wrote: You can use wildcards in =vlookup(). And if the values in column C are longer than 10 characters, this formula won't work the way the OP intended (as I read it). Pete_UK wrote: I am surprised that this works at all, as your final parameter of the VLOOKUP is zero, indicating that you want an exact match - you can't use * as a wildcard. Also, your lookup array is C:D, but you only want to return from column 1, so the array could be C:C. The LEFT function will return a text value, so you must ensure that all the entries in column C are text values - the way it stands, 1102900068 would be interpreted as numeric, so you won't find an exact match. Highlight column C and click Format | Cells | Number (tab) and select Text. Then change your formula to: =VLOOKUP(LEFT(B1,10),C:C,1,0) and ensure that all your values in column C are 10 characters long. Hope this helps. Pete checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson |
vlookup returns #N/A if value contains letters and range numbe
Thanks Pete,but I tried it but it did not work, I guess vlookup cannot return
values if one column contains numbers and a letter and the other only numbers "Pete_UK" wrote: I am surprised that this works at all, as your final parameter of the VLOOKUP is zero, indicating that you want an exact match - you can't use * as a wildcard. Also, your lookup array is C:D, but you only want to return from column 1, so the array could be C:C. The LEFT function will return a text value, so you must ensure that all the entries in column C are text values - the way it stands, 1102900068 would be interpreted as numeric, so you won't find an exact match. Highlight column C and click Format | Cells | Number (tab) and select Text. Then change your formula to: =VLOOKUP(LEFT(B1,10),C:C,1,0) and ensure that all your values in column C are 10 characters long. Hope this helps. Pete checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. |
vlookup returns #N/A if value contains letters and range numbe
That's what this warning was for:
Adjust the range to match--but you can't use the whole column. <vbg Choose a range large enough so you don't have to worry. Then make that reference absolute: =INDEX($C$1:$C$1000,MATCH(LEFT(B2,10)&"*",""&$C$1: $C$1000,0)) The larger the number of rows, the safer you'll be--but the slower the recalculation, too. So use a number as large as you need, but no larger <vvbg. checkQ wrote: Thanks Dave. I loved your last 2 formulas for the problem. I was trying to subsitute C1:C100 with C:C but it would not work. As I copy the formula down C1:C100 change to C2:c101,C3:c102 ect any solutions? Mark "Dave Peterson" wrote: You can use wildcards in =vlookup(). And if the values in column C are longer than 10 characters, this formula won't work the way the OP intended (as I read it). Pete_UK wrote: I am surprised that this works at all, as your final parameter of the VLOOKUP is zero, indicating that you want an exact match - you can't use * as a wildcard. Also, your lookup array is C:D, but you only want to return from column 1, so the array could be C:C. The LEFT function will return a text value, so you must ensure that all the entries in column C are text values - the way it stands, 1102900068 would be interpreted as numeric, so you won't find an exact match. Highlight column C and click Format | Cells | Number (tab) and select Text. Then change your formula to: =VLOOKUP(LEFT(B1,10),C:C,1,0) and ensure that all your values in column C are 10 characters long. Hope this helps. Pete checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson -- Dave Peterson |
vlookup returns #N/A if value contains letters and range numbe
I't me again Pete I just saw in your reply that I would have to adjust the
range since it won't be able to use the whole column C;C. Thanks again "Dave Peterson" wrote: my guess... The value in C2 is a real number--if you used: =isnumber(c2) in an empty cell, what gets returned? I'm guessing True. And =left() returns a string--and excel treats a string that looks like a number differently from a that real number. '123 < 123 (text 123 < number 123) ==== You have a couple of solutions. One is to convert all those values in column C to text. You can use a helper column (D??) and do something like: =c1&"" and drag down. Then select column D edit|copy select column C edit|Paste special|Values and delete column D (we're done with it). Then format the "new" column C as Text so that any new entries/changes to existing entries will be Text. ==== Another way is to use a different formula: This array formula works ok for me: =VLOOKUP(LEFT(B2,10)&"*",""&C1:C100,1,0) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. ==== Since you're actually returning the first column in the lookup range, =vlookup() seems like an unusual choice. I think I'd use this array formula: =INDEX(C1:C100,MATCH(LEFT(B2,10)&"*",""&C1:C100,0) ) (still array entered) ==== One of the differences between these two alternative formulas is what is returned. In the first (=vlookup()), a text value is returned. In the second formula (=index(match()), whatever is in column C will be returned--text or number. checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson |
vlookup returns #N/A if value contains letters and range numbe
You're getting Pete/Peterson mixed up <bg.
You have another followup. checkQ wrote: I't me again Pete I just saw in your reply that I would have to adjust the range since it won't be able to use the whole column C;C. Thanks again "Dave Peterson" wrote: my guess... The value in C2 is a real number--if you used: =isnumber(c2) in an empty cell, what gets returned? I'm guessing True. And =left() returns a string--and excel treats a string that looks like a number differently from a that real number. '123 < 123 (text 123 < number 123) ==== You have a couple of solutions. One is to convert all those values in column C to text. You can use a helper column (D??) and do something like: =c1&"" and drag down. Then select column D edit|copy select column C edit|Paste special|Values and delete column D (we're done with it). Then format the "new" column C as Text so that any new entries/changes to existing entries will be Text. ==== Another way is to use a different formula: This array formula works ok for me: =VLOOKUP(LEFT(B2,10)&"*",""&C1:C100,1,0) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. ==== Since you're actually returning the first column in the lookup range, =vlookup() seems like an unusual choice. I think I'd use this array formula: =INDEX(C1:C100,MATCH(LEFT(B2,10)&"*",""&C1:C100,0) ) (still array entered) ==== One of the differences between these two alternative formulas is what is returned. In the first (=vlookup()), a text value is returned. In the second formula (=index(match()), whatever is in column C will be returned--text or number. checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson -- Dave Peterson |
vlookup returns #N/A if value contains letters and range numbe
Thanks Dave. I was at my wits end trying to figure out the problem. thanks a
million! "Dave Peterson" wrote: That's what this warning was for: Adjust the range to match--but you can't use the whole column. <vbg Choose a range large enough so you don't have to worry. Then make that reference absolute: =INDEX($C$1:$C$1000,MATCH(LEFT(B2,10)&"*",""&$C$1: $C$1000,0)) The larger the number of rows, the safer you'll be--but the slower the recalculation, too. So use a number as large as you need, but no larger <vvbg. checkQ wrote: Thanks Dave. I loved your last 2 formulas for the problem. I was trying to subsitute C1:C100 with C:C but it would not work. As I copy the formula down C1:C100 change to C2:c101,C3:c102 ect any solutions? Mark "Dave Peterson" wrote: You can use wildcards in =vlookup(). And if the values in column C are longer than 10 characters, this formula won't work the way the OP intended (as I read it). Pete_UK wrote: I am surprised that this works at all, as your final parameter of the VLOOKUP is zero, indicating that you want an exact match - you can't use * as a wildcard. Also, your lookup array is C:D, but you only want to return from column 1, so the array could be C:C. The LEFT function will return a text value, so you must ensure that all the entries in column C are text values - the way it stands, 1102900068 would be interpreted as numeric, so you won't find an exact match. Highlight column C and click Format | Cells | Number (tab) and select Text. Then change your formula to: =VLOOKUP(LEFT(B1,10),C:C,1,0) and ensure that all your values in column C are 10 characters long. Hope this helps. Pete checkQ wrote: A B C 040W02193A 040W02193A0B 040W02193A #N/A 1 1028600680B 1102900068 Formula used in column A is=VLOOKUP(LEFT(B1,10)&"*",C:D,1,0) The value that I am looking for is in the first 10 characters in column B that match the first 10 characters in column C The formula works for the first line but returns an #N/A for the second line I believe it is because my column B has the letter B in it and column C does not have any alphabet. Does anyone know a formula to fix this?. -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 03:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com