IF function - need to evaluate cell content in 2 separate files-#N

[Guest]

Hallo
I have two source files feeding into a report page. I need to extract
information based on matching cells in each of the files with the same set of
reference data. I have this formula which does the job:

=IF(ISNA(MATCH(Switches!B4,Computers!$A$2:$A$43,0)),OFFSET(NonAuditableDevices.xls!$A$1,MATCH(Switches!C4,NonAuditableDevices.xls!$A$2:$A$20,0),1),OFFSET(Computers!$A$1,MATCH(Switches!B4,Computers!$A$2:$A$43,0),6))

But if there are no matches with Switches!B4 in either the Computers or
NonAuditableDevices sheets it will return a #NA argument - what I really want
to do is leave the cell blank in that case. I thought adding an additional
ISNA function somewhere should help but can't seem to get it to work.

 

[Bob Phillips]

Don't really follow what you are doing in the formula, but it would be
something along the lines of

=IF(OR(ISNA(test1),ISNA(test2)),"",lookup_formula)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

You could use another ISNA but that would double your formula size. This
could make the formula difficult to read and would also increase file size
since the false condition would need to be that same formula again. You could
use a helper column which would hold the following formula

=IF(ISERROR(D4),"",D4)

your main formula would be in column D which you could hide.

Matt

 

[Guest]

Thanks for the quick responses, both look like they will sort my problem.
I've got to go out now bit wiull be trying them both tomorrow.

I really appreciate the help

Peter