Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
After verifying that the formula is correct, matching cell formats, and
verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
You may look for numeric items which indeed look like numbers and are
formatted as such, but still are text. Format an empty cells as Number. Enter the number 1. EditCopy. Select your "numbers" EditPaste special, check Multiply. Does that help? If not, post again in this thread, but do give some more information like your formula, the values you look for, whether the data is sorted or not, etc. "Janeen" wrote in message ... After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
My detail would surely help: what are the values that aren't matching?
There are two issues that cause this frequently. First is text that has extra spaces before or, more commonly, after the visible text. That is "Apple" is NOT going to match "Apple ". If you're matching text to text, you might check for this. With the cell selected, click at the END of the formula bar. If the cursor isn't immediately after the text, you've got extra spaces. (Do this both in the table and where you're doing the lookup). You can just delete the extra spaces. The other common issue is a mismatch between a number and a text representation of that number. If the table has a string 6 and the lookup uses the number 6, they won't match. And cell formatting won't get them to match. Try applying the comma style to both. If they're really both numbers, you'll get two decimal points; if either is text, the comma style won't do much. If this is the issue, you can either reenter the data to get it to be numeric, or use a construct like vlookup(trim(a1)... to convert a number to a string for the purposes of the lookup or vlookup(value(a1) to conver a string to a number for the lookup. HTH. If not, please provide more detail. --Bruce "Janeen" wrote: After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
The data in call A1 is 710805, which is entered as a number with no decimal
places. In the source file the data is also 710805, which is entered as a number with no decimal places. I've already verified that there are no spaces before or after the number, and I've verified that the cells are formatted the same. I've re-constructed the formula from scratch, I've copied and pasted the formula, and I've allowed the formula wizard to create the formula - each time giving me a result of "N/A". Now what? "bpeltzer" wrote: My detail would surely help: what are the values that aren't matching? There are two issues that cause this frequently. First is text that has extra spaces before or, more commonly, after the visible text. That is "Apple" is NOT going to match "Apple ". If you're matching text to text, you might check for this. With the cell selected, click at the END of the formula bar. If the cursor isn't immediately after the text, you've got extra spaces. (Do this both in the table and where you're doing the lookup). You can just delete the extra spaces. The other common issue is a mismatch between a number and a text representation of that number. If the table has a string 6 and the lookup uses the number 6, they won't match. And cell formatting won't get them to match. Try applying the comma style to both. If they're really both numbers, you'll get two decimal points; if either is text, the comma style won't do much. If this is the issue, you can either reenter the data to get it to be numeric, or use a construct like vlookup(trim(a1)... to convert a number to a string for the purposes of the lookup or vlookup(value(a1) to conver a string to a number for the lookup. HTH. If not, please provide more detail. --Bruce "Janeen" wrote: After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
I've also seen where one number will have a significant digit past the second decimal place less than 5 so it rounded down, therefore until I extended the decimals to three or four I could not identify why the numbers would not 'match'. i.e. one was 1234.0004 and the other was 1234.0000. Janeen Wrote: After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) -- surg4u1975 ------------------------------------------------------------------------ surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718 View this thread: http://www.excelforum.com/showthread...hreadid=496251 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
'Formatted the same' won't do it if one is text and the other is numeric.
What's the effect, in each case, of the comma style? Of the percent style? Are the rest of the key values in your table also numeric? "Janeen" wrote: The data in call A1 is 710805, which is entered as a number with no decimal places. In the source file the data is also 710805, which is entered as a number with no decimal places. I've already verified that there are no spaces before or after the number, and I've verified that the cells are formatted the same. I've re-constructed the formula from scratch, I've copied and pasted the formula, and I've allowed the formula wizard to create the formula - each time giving me a result of "N/A". Now what? "bpeltzer" wrote: My detail would surely help: what are the values that aren't matching? There are two issues that cause this frequently. First is text that has extra spaces before or, more commonly, after the visible text. That is "Apple" is NOT going to match "Apple ". If you're matching text to text, you might check for this. With the cell selected, click at the END of the formula bar. If the cursor isn't immediately after the text, you've got extra spaces. (Do this both in the table and where you're doing the lookup). You can just delete the extra spaces. The other common issue is a mismatch between a number and a text representation of that number. If the table has a string 6 and the lookup uses the number 6, they won't match. And cell formatting won't get them to match. Try applying the comma style to both. If they're really both numbers, you'll get two decimal points; if either is text, the comma style won't do much. If this is the issue, you can either reenter the data to get it to be numeric, or use a construct like vlookup(trim(a1)... to convert a number to a string for the purposes of the lookup or vlookup(value(a1) to conver a string to a number for the lookup. HTH. If not, please provide more detail. --Bruce "Janeen" wrote: After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
Try copying the 710805 in cell A1 to the cell that apparently matches or vice versa. The formula would have to match either way. Then figure out what was different between them. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=496251 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP error message
Did you follow my advice? I'm pretty sure that is the problem.
-- Kind regards, Niek Otten "Janeen" wrote in message ... The data in call A1 is 710805, which is entered as a number with no decimal places. In the source file the data is also 710805, which is entered as a number with no decimal places. I've already verified that there are no spaces before or after the number, and I've verified that the cells are formatted the same. I've re-constructed the formula from scratch, I've copied and pasted the formula, and I've allowed the formula wizard to create the formula - each time giving me a result of "N/A". Now what? "bpeltzer" wrote: My detail would surely help: what are the values that aren't matching? There are two issues that cause this frequently. First is text that has extra spaces before or, more commonly, after the visible text. That is "Apple" is NOT going to match "Apple ". If you're matching text to text, you might check for this. With the cell selected, click at the END of the formula bar. If the cursor isn't immediately after the text, you've got extra spaces. (Do this both in the table and where you're doing the lookup). You can just delete the extra spaces. The other common issue is a mismatch between a number and a text representation of that number. If the table has a string 6 and the lookup uses the number 6, they won't match. And cell formatting won't get them to match. Try applying the comma style to both. If they're really both numbers, you'll get two decimal points; if either is text, the comma style won't do much. If this is the issue, you can either reenter the data to get it to be numeric, or use a construct like vlookup(trim(a1)... to convert a number to a string for the purposes of the lookup or vlookup(value(a1) to conver a string to a number for the lookup. HTH. If not, please provide more detail. --Bruce "Janeen" wrote: After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |