Prefer empty cell to #NA

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
 
B

Bob Phillips

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)
 
R

RagDyer

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
 
M

Max

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)))
 
J

jsjenn

Bob,

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

Thanks again, jsjenn
 
R

RagDyer

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)
 
M

Mike A

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
 
R

RagDyer

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
 
Top