VLOOKUP #NA error

  • Thread starter Thread starter Lift Off
  • Start date Start date
L

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
 
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))
 
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)
 
Are you intending

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

Alan Beban
 
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
 
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)
 
Back
Top