Overcoming #N/A being shown as value and display blank value in that cell?

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Microsoft Excel:  VLOOKUP shows #N/A but want it to show blank if no matching information.   What is the correct syntax I should use?

The following gives #N/A but I just want it to be blank for those values not matching.
It does work and display the correct values when they match.

=VLOOKUP('Claim Library BCV'!A:T, 6, FALSE)

Open in new window


The following does work but I'm concerned about using IFERROR.

=IFERROR(VLOOKUP('Claim Library BCV'!A:T, 6, FALSE), "")

Open in new window


The ISBLANK function I have tried - of course will work but displays FALSE
=ISBLANK(VLOOKUP('Claim Library BCV'!A:T, 6, FALSE))

Open in new window


if I try:  I get the error - You've entered too many arguments for this function.
=ISBLANK(VLOOKUP('Claim Library BCV'!A:T, 6, FALSE), "")

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Use IFERROR. #N/A is sub-type of "error", thus it's exactly what you normally would use.

ISBLANK is only for testing the cell, so it makes no sense. You may use ISNA for testing whether a cell is #N/A.

So, when you want to suppress #N/A only, but no other error use

=IF(ISNA(yourColumnOrFormula);yourColumnOrFormula;"")

Open in new window

.
Rob HensonFinance Analyst
Commented:
IFERROR replaces the use of ISERROR within an IF statement. To use ISNA as suggested by ste5an then use:

=IF(ISNA(VLOOKUP('Claim Library BCV'!A:T, 6, FALSE)),"",VLOOKUP('Claim Library BCV'!A:T, 6, FALSE))

Likewise you can use the same syntax for ISBLANK:

=IF(ISBLANK(VLOOKUP('Claim Library BCV'!A:T, 6, FALSE)),"",VLOOKUP('Claim Library BCV'!A:T, 6, FALSE))
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you have Mac Excel 2011 or later, or Windows Excel 2013 or later, you may use the IFNA function to return an empty string instead of #N/A error value:
=IFNA(VLOOKUP('Claim Library BCV'!A:T, 6, FALSE),"")

Open in new window

Author

Commented:
thank you all.

Wouldn't it be better though if I just create my own function?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial