VLookup N/A Error

[Guest]

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)

 

[Ian]

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

 

[Pete]

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

 

[Guest]

Wanda,

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

 

[Gord Dibben]

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

 

[Guest]

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