VLOOKUP error message

J

Janeen

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)
 
N

Niek Otten

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. Edit>Copy.
Select your "numbers" Edit>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.
 
B

bpeltzer

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
 
J

Janeen

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?
 
S

surg4u1975

I've also seen where one number will have a significant digit past th
second decimal place less than 5 so it rounded down, therefore until
extended the decimals to three or four I could not identify why th
numbers would not 'match'. i.e. one was 1234.0004 and the other wa
1234.0000.
 
B

bpeltzer

'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?
 
C

Cutter

Try copying the 710805 in cell A1 to the cell that apparently matches o
vice versa. The formula would have to match either way. Then figur
out what was different between them
 
Top