Return Empty if "#N/A"

[ckroon]

Hi all.
I have this formula in many cells on a sheet:

=IF(ROWS($1:4)>$CQ$7,"",INDEX($A:$CK,$CL$4+ROWS($1:4),MATCH($CP$5,$A$1:$CK$1,0)))

It works great except I need it to return as empty if the result is #N/A.

Is this possible?

Thanks!

 

[barry houdini]

Try

=IF(ROWS($1:4)>$CQ$7,"",IF(COUNTIF($A$1:$CK$1,$CP$5),INDEX($A:$CK,$CL$4+ROWS($1:4),MATCH($CP$5,$A$1:$CK$1,0)),""))

 

[ckroon]

Nope

Came back as #N/A as well.
Good idea though.

 

[barry houdini]

So what's casuing the error? Do you have #N/As within the INDEX range? Try

=IF(ROWS($1:4)>$CQ$7,"",IF(ISNA(INDEX($A:$CK,$CL$4+ROWS($1:4),MATCH($CP$5,$A$1:$CK$1,0))),"",INDEX($A:$CK,$CL$4+ROWS($1:4),MATCH($CP$5,$A$1:$CK$1,0)))

 

[ckroon]

Solved

That was it!

It c orrected it to this:
=IF(ROWS(1:4)>$CQ$7,"",IF(ISNA(INDEX($A:$CK,$CL$4+ROWS(1:4),MATCH($CP$5,$A$1:$CK$1,0))),"",INDEX($A:$CK,$CL$4+ROWS(1:4),MATCH($CP$5,$A$1:$CK$1,0))))

and it works like a charm.

Thanks so much!