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)

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)

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)

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)

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

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)

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

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)