software

After verifying that the formula is correct, matching cell formats, and
verifying the cell data is an exact match, I continue to get a quot;N/Aquot; 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)

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. Editgt;Copy.
Select your quot;numbersquot; Editgt;Paste 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.

quot;Janeenquot; gt; wrote in message
...
gt; After verifying that the formula is correct, matching cell formats, and
gt; verifying the cell data is an exact match, I continue to get a quot;N/Aquot;
gt; response
gt; in two cells of my workbook. I've even gone so far as to back out of
gt; Excel,
gt; re-launch the program, and re-open my source file and VLOOKUP file. Out
gt; of
gt; over 1300 matched items, these two refuse to cooperate! Can anyone tell
gt; me
gt; why?
gt;
gt; I'm frazzled beyond understanding at this point... (sigh)
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
quot;Applequot; is NOT going to match quot;Apple quot;. 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

quot;Janeenquot; wrote:

gt; After verifying that the formula is correct, matching cell formats, and
gt; verifying the cell data is an exact match, I continue to get a quot;N/Aquot; response
gt; in two cells of my workbook. I've even gone so far as to back out of Excel,
gt; re-launch the program, and re-open my source file and VLOOKUP file. Out of
gt; over 1300 matched items, these two refuse to cooperate! Can anyone tell me
gt; why?
gt;
gt; I'm frazzled beyond understanding at this point... (sigh)

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 quot;N/Aquot;.

Now what?

quot;bpeltzerquot; wrote:

gt; My detail would surely help: what are the values that aren't matching?
gt; There are two issues that cause this frequently. First is text that has
gt; extra spaces before or, more commonly, after the visible text. That is
gt; quot;Applequot; is NOT going to match quot;Apple quot;. If you're matching text to text, you
gt; might check for this. With the cell selected, click at the END of the
gt; formula bar. If the cursor isn't immediately after the text, you've got
gt; extra spaces. (Do this both in the table and where you're doing the lookup).
gt; You can just delete the extra spaces.
gt; The other common issue is a mismatch between a number and a text
gt; representation of that number. If the table has a string 6 and the lookup
gt; uses the number 6, they won't match. And cell formatting won't get them to
gt; match. Try applying the comma style to both. If they're really both
gt; numbers, you'll get two decimal points; if either is text, the comma style
gt; won't do much. If this is the issue, you can either reenter the data to get
gt; it to be numeric, or use a construct like vlookup(trim(a1)... to convert a
gt; number to a string for the purposes of the lookup or vlookup(value(a1) to
gt; conver a string to a number for the lookup.
gt; HTH. If not, please provide more detail. --Bruce
gt;
gt; quot;Janeenquot; wrote:
gt;
gt; gt; After verifying that the formula is correct, matching cell formats, and
gt; gt; verifying the cell data is an exact match, I continue to get a quot;N/Aquot; response
gt; gt; in two cells of my workbook. I've even gone so far as to back out of Excel,
gt; gt; re-launch the program, and re-open my source file and VLOOKUP file. Out of
gt; gt; over 1300 matched items, these two refuse to cooperate! Can anyone tell me
gt; gt; why?
gt; gt;
gt; gt; I'm frazzled beyond understanding at this point... (sigh)


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:
gt; After verifying that the formula is correct, matching cell formats, and
gt; verifying the cell data is an exact match, I continue to get a quot;N/Aquot;
gt; response
gt; in two cells of my workbook. I've even gone so far as to back out of
gt; Excel,
gt; re-launch the program, and re-open my source file and VLOOKUP file.
gt; Out of
gt; over 1300 matched items, these two refuse to cooperate! Can anyone
gt; tell me
gt; why?
gt;
gt; I'm frazzled beyond understanding at this point... (sigh)--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: www.excelforum.com/member.php...oamp;userid=28718
View this thread: www.excelforum.com/showthread...hreadid=496251'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?

quot;Janeenquot; wrote:

gt; The data in call A1 is 710805, which is entered as a number with no decimal
gt; places. In the source file the data is also 710805, which is entered as a
gt; number with no decimal places. I've already verified that there are no
gt; spaces before or after the number, and I've verified that the cells are
gt; formatted the same. I've re-constructed the formula from scratch, I've
gt; copied and pasted the formula, and I've allowed the formula wizard to create
gt; the formula - each time giving me a result of quot;N/Aquot;.
gt;
gt; Now what?
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; My detail would surely help: what are the values that aren't matching?
gt; gt; There are two issues that cause this frequently. First is text that has
gt; gt; extra spaces before or, more commonly, after the visible text. That is
gt; gt; quot;Applequot; is NOT going to match quot;Apple quot;. If you're matching text to text, you
gt; gt; might check for this. With the cell selected, click at the END of the
gt; gt; formula bar. If the cursor isn't immediately after the text, you've got
gt; gt; extra spaces. (Do this both in the table and where you're doing the lookup).
gt; gt; You can just delete the extra spaces.
gt; gt; The other common issue is a mismatch between a number and a text
gt; gt; representation of that number. If the table has a string 6 and the lookup
gt; gt; uses the number 6, they won't match. And cell formatting won't get them to
gt; gt; match. Try applying the comma style to both. If they're really both
gt; gt; numbers, you'll get two decimal points; if either is text, the comma style
gt; gt; won't do much. If this is the issue, you can either reenter the data to get
gt; gt; it to be numeric, or use a construct like vlookup(trim(a1)... to convert a
gt; gt; number to a string for the purposes of the lookup or vlookup(value(a1) to
gt; gt; conver a string to a number for the lookup.
gt; gt; HTH. If not, please provide more detail. --Bruce
gt; gt;
gt; gt; quot;Janeenquot; wrote:
gt; gt;
gt; gt; gt; After verifying that the formula is correct, matching cell formats, and
gt; gt; gt; verifying the cell data is an exact match, I continue to get a quot;N/Aquot; response
gt; gt; gt; in two cells of my workbook. I've even gone so far as to back out of Excel,
gt; gt; gt; re-launch the program, and re-open my source file and VLOOKUP file. Out of
gt; gt; gt; over 1300 matched items, these two refuse to cooperate! Can anyone tell me
gt; gt; gt; why?
gt; gt; gt;
gt; gt; gt; I'm frazzled beyond understanding at this point... (sigh)


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: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=496251Did you follow my advice? I'm pretty sure that is the problem.

--
Kind regards,

Niek Otten

quot;Janeenquot; gt; wrote in message
...
gt; The data in call A1 is 710805, which is entered as a number with no
gt; decimal
gt; places. In the source file the data is also 710805, which is entered as a
gt; number with no decimal places. I've already verified that there are no
gt; spaces before or after the number, and I've verified that the cells are
gt; formatted the same. I've re-constructed the formula from scratch, I've
gt; copied and pasted the formula, and I've allowed the formula wizard to
gt; create
gt; the formula - each time giving me a result of quot;N/Aquot;.
gt;
gt; Now what?
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt;gt; My detail would surely help: what are the values that aren't matching?
gt;gt; There are two issues that cause this frequently. First is text that has
gt;gt; extra spaces before or, more commonly, after the visible text. That is
gt;gt; quot;Applequot; is NOT going to match quot;Apple quot;. If you're matching text to text,
gt;gt; you
gt;gt; might check for this. With the cell selected, click at the END of the
gt;gt; formula bar. If the cursor isn't immediately after the text, you've got
gt;gt; extra spaces. (Do this both in the table and where you're doing the
gt;gt; lookup).
gt;gt; You can just delete the extra spaces.
gt;gt; The other common issue is a mismatch between a number and a text
gt;gt; representation of that number. If the table has a string 6 and the
gt;gt; lookup
gt;gt; uses the number 6, they won't match. And cell formatting won't get them
gt;gt; to
gt;gt; match. Try applying the comma style to both. If they're really both
gt;gt; numbers, you'll get two decimal points; if either is text, the comma
gt;gt; style
gt;gt; won't do much. If this is the issue, you can either reenter the data to
gt;gt; get
gt;gt; it to be numeric, or use a construct like vlookup(trim(a1)... to convert
gt;gt; a
gt;gt; number to a string for the purposes of the lookup or vlookup(value(a1) to
gt;gt; conver a string to a number for the lookup.
gt;gt; HTH. If not, please provide more detail. --Bruce
gt;gt;
gt;gt; quot;Janeenquot; wrote:
gt;gt;
gt;gt; gt; After verifying that the formula is correct, matching cell formats, and
gt;gt; gt; verifying the cell data is an exact match, I continue to get a quot;N/Aquot;
gt;gt; gt; response
gt;gt; gt; in two cells of my workbook. I've even gone so far as to back out of
gt;gt; gt; Excel,
gt;gt; gt; re-launch the program, and re-open my source file and VLOOKUP file.
gt;gt; gt; Out of
gt;gt; gt; over 1300 matched items, these two refuse to cooperate! Can anyone
gt;gt; gt; tell me
gt;gt; gt; why?
gt;gt; gt;
gt;gt; gt; I'm frazzled beyond understanding at this point... (sigh)