Prefer empty cell to #NA

  • Thread starter Thread starter jsjenn
  • Start date Start date
J

jsjenn

I have a formula =INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$42,0)) That returns
"#NA" when the target cell is empty. I would prefer that cell to
remain blank as well, any suggestions?

Thanks, jsjenn
 
Filter it out with

=IF(ISERROR(MATCH(R54,$H$7:$H$42,0)),"",INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$4
2,0))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Try this:

=IF(ISNA(MATCH(R54,$H$7:$H$42,0)),"",INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$42,0
)))
--

HTH,

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

I have a formula =INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$42,0)) That returns
"#NA" when the target cell is empty. I would prefer that cell to
remain blank as well, any suggestions?

Thanks, jsjenn
 
You can use a construct like:

=IF(ISNA(your_function),"",your_function)

So, applied to your case, it should be:

=IF(ISNA(INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$42,0))),"",INDEX($G$7:$G$42,MATC
H(R54,$H$7:$H$42,0)))
 
Bob,

Thanks for your solution, it worked like a charm (even though you had
an extra ) at the end).

Thanks again, jsjenn
 
Bob,

I think maybe one too many ")" at the end !
--


Regards,

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

Filter it out with

=IF(ISERROR(MATCH(R54,$H$7:$H$42,0)),"",INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$4
2,0))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
ISERROR() checks for all errors except #N/A - use ISNA() instead.



Filter it out with

=IF(ISERROR(MATCH(R54,$H$7:$H$42,0)),"",INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$4
2,0))))
Click to expand...

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
That's not exactly accurate Mike !

*ISERR* doesn't check for #N/A.

ISERROR checks for *all* errors.
--


Regards,

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

ISERROR() checks for all errors except #N/A - use ISNA() instead.



Filter it out with

=IF(ISERROR(MATCH(R54,$H$7:$H$42,0)),"",INDEX($G$7:$G$42,MATCH(R54,$H$7:$H$ 4
2,0))))
Click to expand...

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
Didn't realize that - I always thought they were synonymous.

In lookups, I prefer to use ISNA, because I would like to see if there
is a #REF or #VALUE error. I create workboks that other people use,
and it helps to see errors when something gets broken!!

That's not exactly accurate Mike !

*ISERR* doesn't check for #N/A.

ISERROR checks for *all* errors.
Click to expand...

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
Back
Top