I need to replace the #N/A values to be blank within a VLOOKUP formula. How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

Hi,
You are missing a closing parens in the formula and I usually use ISERROR.

=IF(ISERROR(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

HTH
--
Ken Hudson


"AZExcelNewbie" wrote:

I need to replace the #N/A values to be blank within a VLOOKUP formula. How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

Replace ISBLANK with ISERROR. Also, it looks like you're missing the ending
) for your ISBLANK function (posting error perhaps).


"AZExcelNewbie" wrote:

I need to replace the #N/A values to be blank within a VLOOKUP formula. How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

Hello,

The proper formula would be ...

=IF(ISNA(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,0)),"",VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,0))

Although I would suggest downloading Laurent Longre's Morefunc.xll add-in.
Found he http://xcell05.free.fr/english/index.html (english).

You could then shorten it to ...

=IF(SETV(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,0)),"",GETV())

Makes it a little simpler to read/understand/troubleshoot.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"AZExcelNewbie" wrote in message
...
I need to replace the #N/A values to be blank within a VLOOKUP formula.
How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

Why ISERROR? Why not ISNA?

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Ken Hudson" wrote in message
...
Hi,
You are missing a closing parens in the formula and I usually use
ISERROR.

=IF(ISERROR(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

HTH
--
Ken Hudson


"AZExcelNewbie" wrote:

I need to replace the #N/A values to be blank within a VLOOKUP formula.
How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

Hi Zack,
Just my preference. ISNA accounts for the #NA error which is in the original
posting. I added the ISERROR suggestion to alert the poster that it would
cover the gamut of possible errors and is what I usually want to test.
--
Ken Hudson


"Zack Barresse" wrote:

Why ISERROR? Why not ISNA?

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Ken Hudson" wrote in message
...
Hi,
You are missing a closing parens in the formula and I usually use
ISERROR.

=IF(ISERROR(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

HTH
--
Ken Hudson


"AZExcelNewbie" wrote:

I need to replace the #N/A values to be blank within a VLOOKUP formula.
How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

True. I just am of the thinking, "If the glove fits..." Ya know? I mean,
I can do almost anything with VBA, but that doesn't mean use it when a
simple worksheet function would do the trick, does it? I know we're talking
petty change here, I've just seen these semantics often times get blown up
on a proportionately bigger scale with the same priciples.

Take care.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Ken Hudson" wrote in message
...
Hi Zack,
Just my preference. ISNA accounts for the #NA error which is in the
original
posting. I added the ISERROR suggestion to alert the poster that it would
cover the gamut of possible errors and is what I usually want to test.
--
Ken Hudson


"Zack Barresse" wrote:

Why ISERROR? Why not ISNA?

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Ken Hudson" wrote in message
...
Hi,
You are missing a closing parens in the formula and I usually use
ISERROR.

=IF(ISERROR(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))

HTH
--
Ken Hudson


"AZExcelNewbie" wrote:

I need to replace the #N/A values to be blank within a VLOOKUP
formula.
How
do I do this?

I've tried the following but excel doesn't seem to like it:

=IF(ISBLANK(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept
9'!$A$1:$U$47,14,FALSE))