vlookup false gives "#N/A" - can it say something else?

[Guest]

VLOOKUP(D3,F7:G114,2,FALSE)
The false part produces "#N/A" if there is not an exact match.
Can I substitute some other text for the "#N/A"? Like "NO MATCH"?
Thanks much! -Bob

 

[Dave Peterson]

This will work in all versions of excel.
=if(iserror(vlookup(...)),"No Match",vlookup(...))

This'll work in xl2007:
=iferror(vlookup(...),"No Match")

 

[Guest]

Hi Bob,

Since VLOOKUP returns NA your shortest formula would be

=IF(ISNA(VLOOKUP(D3,F7:G114,2,)),"No Match",VLOOKUP(D3,F7:G114,2,))

I have also removed the FALSE argument because it is used by default if you
just add the comma.

This problem will be handled more elegently in 2007 because you won't need
to repeat the VLOOKUP function.

 

[Guest]

Thanks, guys, for your replies. I appreciate the help. As much as a person
thinks they use Excel, there are innumerable areas one has never touched.
What forum do I go to to ask how to keep my CDROM drive from losing its
drive letter? Sometimes it's (F and sometimes I lose it I guess when I
plug in other devices like digital camera or flash drive or voice recorder.
My DVD drive is always present on (E. But I lose the CDROM drive. My
flash drive sometimes becomes (F. I have only recently learned I can scan
for hardware changes, and I can get my CDROM drive back, perhaps to (L. I
used to think I had to re-boot in case I wanted to burn a CD. Can I
permanently assign (F to the CDROM drive? Thanks much, Bob

 

[Gord Dibben]

Bob

microsoft.public.windowsxp.general

is probably the best New Group for your CDROM drive problem.


Gord

 

[Dave Peterson]

I would try a newsgroup dedicated to the version of windows you're running.

I search in the *windowsxp* newsgroup and found a link to this:
http://www.dougknox.com/tips/xp_drive_letters.htm

Which had a link to this, too:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307844

Nope. I didn't try it.

But be careful!