VLOOKUP #NA error

[Lift Off]

Using Excel 2000. Simple lookup produces the #NA when it encounters
no match on lookup. I want to eliminate the #NA and have tried to d
so with the following formula:

=isna(VLOOKUP(A9,$P$8:$Q$94,2,FALSE),"",VLOOKUP(A9,$P$8:$Q$94,2,FALSE))

Unfortunately I can't get it to accept the formula. Get error messag
"the formula you typed contains an error". Clicking OK the curso
jumps to the " " before the second VLOOKUP.

I've scooted around this site and the net and believe the formula'
correct. Cell format is general, but changing to number doesn't help.
I've tried the =ISERROR formula, which is the same, and get the sam
results.

Any ideas?? Thanks in advance.

Lift Of

 

[Frank Kabel]

Hi
nearly there. Just add the IF function:
=IF(isna(VLOOKUP(A9,$P$8:$Q$94,2,FALSE),"",VLOOKUP(A9,$P$8:$Q$94,2,FALS
E))

 

[Peo Sjoblom]

One way


=IF(ISNUMBER(MATCH(A9,$P$8:$P$94,0)),VLOOKUP(A9,$P$8:$Q$94,2,FALSE),"")

 

[Bob Phillips]

Almost!

=IF(ISNA(VLOOKUP(A9,$P$8:$Q$94,2,FALSE)),"",VLOOKUP(A9,$P$8:$Q$94,2,FALSE))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Alan Beban]

Are you intending

=IF(ISNA(VLOOKUP(A9,$P$8:$Q$94,2,FALSE)),0,VLOOKUP(A9,$P$8:$Q$94,2,FALSE))?

Alan Beban

 

[Lift Off]

Frank, Peo and Bob: Okay, Bob you got it. Frank and Peo,
copied/pasted yours and they didn't work. Same error. While tryin
those, Bob pops up with his solution and it worked.

So Bob, why is yours working. Looks the same as Frank's. Maybe
can't paste!

Many thanks to each of you for trying.

Lift Of

 

[Lift Off]

Bob: Forget it. Just notice the ')' . Thanks again

 

[Bob Phillips]

Frank missed a closing bracket in the first VLOOKUP, don't know about Poe's.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)