We have the following formula that returns a numerical result for some, but
#N/A for others. Why an error and not a number?

=VLOOKUP(A8,'Highmark run'!$A$5:$Y$1821,23,FALSE)

--
Wanda Garner
Highmark Blue Shield

If there is no exact match for A8 in the target range (W5:W1821 in this
case), #N/A is returned.

--
Ian
--
"WandaSG" wrote in message
...
We have the following formula that returns a numerical result for some,
but
#N/A for others. Why an error and not a number?

=VLOOKUP(A8,'Highmark run'!$A$5:$Y$1821,23,FALSE)

--
Wanda Garner
Highmark Blue Shield

If you want to avoid the error, you can amend your formula as follows:

=IF (ISNA(Vlookup formula), 0 , Vlookup formula)

This will return a zero instead of #N/A

Pete

Wanda,

Find an example where this happens and check to see if you have trailing
spaces in one of the entries, etc.



"WandaSG" wrote:

We have the following formula that returns a numerical result for some, but
#N/A for others. Why an error and not a number?

=VLOOKUP(A8,'Highmark run'!$A$5:$Y$1821,23,FALSE)

--
Wanda Garner
Highmark Blue Shield

Wanda

VLOOKUP with the False argument looks for an exact match and if not found
returns the NA error.

Do you believe that it should return a number?

Can you find what looks like a match in the table?

Perhaps there is a space in the cell with the correct number?


Gord Dibben Excel MVP

On Mon, 12 Dec 2005 09:06:01 -0800, "WandaSG"
wrote:

We have the following formula that returns a numerical result for some, but
#N/A for others. Why an error and not a number?

=VLOOKUP(A8,'Highmark run'!$A$5:$Y$1821,23,FALSE)

The information was matching up, but it returned the #N/A error anyway.
After investigating for a while, this is what I found...

On the Highmark run worksheet, if the numbers to match up had a green tag on
the upper left corner of the cell, I did the following:

Clicked in the cell
Clicked the "Smart Tag" that appeared on the right
Choose Convert to Number

This returned the number needed instead of the #N/A----Whew hew!!! Thank
you all for your input.

Wanda Garner
Highmark Blue Shield


"Pete" wrote:

If you want to avoid the error, you can amend your formula as follows:

=IF (ISNA(Vlookup formula), 0 , Vlookup formula)

This will return a zero instead of #N/A

Pete