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

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(Switch es!C4,NonAuditableDevices.xls!$A$2:$A$20,0),1),OFF SET(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.

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)


"PBM" wrote in message
...
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(NonAuditableDevic
es.xls!$A$1,MATCH(Switches!C4,NonAuditableDevices. xls!$A$2:$A$20,0),1),OFFSE
T(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.

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


"PBM" wrote:

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(Switch es!C4,NonAuditableDevices.xls!$A$2:$A$20,0),1),OFF SET(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.

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