I have a simple V-lookup formula in sheet1!A1:
=VLOOKUP($C$2,Names!$A:$C,2,FALSE)

Sometimes there is not a match and I get a #NA value. I want Sheet1!A1 to
equal "???" instead of #N/A when this happens. I would use an If statement
but don't know exactly how?
--
Thanks
Shawn

Hi,

Try one of these:

=IF(COUNTIF(Names!$A:$A,$C$2),VLOOKUP($C$2,Names!$ A:$C,2,FALSE),"???")
=IF(ISNA(VLOOKUP($C$2,Names!$A:$C,2,FALSE)),"???", VLOOKUP($C$2,Names!$A:$C,2,FALSE))
=IF(ISERROR(VLOOKUP($C$2,Names!$A:$C,2,FALSE)),"?? ?",VLOOKUP($C$2,Names!$A:$C,2,FALSE))

Regards,
KL


"Shawn" wrote in message
...
I have a simple V-lookup formula in sheet1!A1:
=VLOOKUP($C$2,Names!$A:$C,2,FALSE)

Sometimes there is not a match and I get a #NA value. I want Sheet1!A1 to
equal "???" instead of #N/A when this happens. I would use an If
statement
but don't know exactly how?
--
Thanks
Shawn

On Mon, 5 Sep 2005 13:27:25 +0200, "KL"
wrote:

Hi,

Try one of these:

=IF(COUNTIF(Names!$A:$A,$C$2),VLOOKUP($C$2,Names! $A:$C,2,FALSE),"???")
=IF(ISNA(VLOOKUP($C$2,Names!$A:$C,2,FALSE)),"???" ,VLOOKUP($C$2,Names!$A:$C,2,FALSE))
=IF(ISERROR(VLOOKUP($C$2,Names!$A:$C,2,FALSE)),"? ??",VLOOKUP($C$2,Names!$A:$C,2,FALSE))

Regards,
KL


"Shawn" wrote in message
...
I have a simple V-lookup formula in sheet1!A1:
=VLOOKUP($C$2,Names!$A:$C,2,FALSE)

Sometimes there is not a match and I get a #NA value. I want Sheet1!A1 to
equal "???" instead of #N/A when this happens. I would use an If
statement
but don't know exactly how?
--
Thanks
Shawn



Why wouldn't the simpler

=if(iserror(vlookup blah blah)),"???",vlookup(more blah))

suffice?

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi,

"Richard Buttrey" wrote in message
Why wouldn't the simpler
=if(iserror(vlookup blah blah)),"???",vlookup(more blah))
suffice?

That was the third option I offered. The first option is the shortest, the
second only checks for a specific error and is shorter than the third one.
All three should be equal performance wise.

Regards,
KL