Blank cell if #N/A

[Steved]

Hello from Steved

=VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4)

With the above formula what is required to
blank the cell if #N/A is showing.

Thankyou.

 

[RagDyer]

Try this:

=IF(ISNA(MATCH(E6,'Audit Team'!$A$2:$A$2000,0)),"",VLOOKUP(E6,'Audit
Team'!$A$2:$D$2000,4))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hello from Steved

=VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4)

With the above formula what is required to
blank the cell if #N/A is showing.

Thankyou.

 

[Norman Harker]

Hi Steved!

One Way:

=IF(ISNA(VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4)),"",VLOOKUP(E6,'Audit
Team'!$A$2:$D$2000,4))

Uses the standard approach:

=IF(ISNA(YourFormula),"",YourFormula)

 

[Steved]

Helo from Steved

Thankyou very much Gentleman.

 

[Aladin Akyurek]

With

=VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4)

where the match-type (range_lookup) is omitted, should only occur when E6 <
'Audit Team'!$A$2 or 'Audit Team'!$A$2:$A$200 is emtpty. If the omission of
the match-type is justified:

=IF(E6<'Audit Team'!$A$2,"",VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4))

would suffice.

 

[Norman Harker]

Hi Aladin!

Agreed! That's the problem with using a "one size fits all approach".

 

[Excel man]

-----Original Message-----
Hello from Steved

=VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4)

With the above formula what is required to
blank the cell if #N/A is showing.

Thankyou.
.
=IF(vlookup(E6,'Audit Team'!$A$2:$D$2000,4)

="#N/A,"",vlookup(e6,'Audit Team'!$A$2:$D$2000,4)

 

[Nick Hodge]

Excel Man

There are built in error formulae to check for errors, in this case,
specifically...

IF(ISNA(VLOOKUP(E6,'Audit Team'!$A$2:$D$2000,4)),"",VLOOKUP(E6,'Audit
Team'!$A$2:$D$2000,4))

Equally you could use ISERROR as this traps all, but not ISERR as this traps
everything except #N/A errors.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)