nested IF using MATCH

  • Thread starter Thread starter rudekid
  • Start date Start date
R

rudekid

hello

Slight problem with formula I am trying to adapt written for a subtly
different purpose. The formula is as follows:

=SUM(IF(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!J329:M329,0)),0,1))

It's pretty simple: it returns a 1 if an item from the array
Streetcheck appears in fields J329-M329 and a 0 if it doesn't.

What I'm trying to do is be a bit cleverer and add an extra IF
statement that says if it finds an item from the same array in another
field, (in this case I329), to ignore the above statement. However, if
it doesn't find the item in I329 then it should still return 1 if the
original statement is found to be TRUE.

Confused?

The problem I have is that when I write the statement the only way I
know how to, (as follows):

=IF(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!I329,0)),0,IF(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!J329:M329,0)),1,0))

the first IF statement actually does the opposite of what I thought it
would, i.e. it's TRUE only if it doesn't find the item in the array.
For it to work it has to look in the field I329 first and return TRUE
(or, in this case, 0) if it finds a match.

This is doing my head in as the only other way I can think of doing it
is to use NOT but it doesn't like that one bit.

Any ideas?

Thanks in advance.
 
Slight problem with formula I am trying to adapt written for a subtly
different purpose. The formula is as follows:

=SUM(IF(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!J329:M329,0)),0,1))

It's pretty simple: it returns a 1 if an item from the array
Streetcheck appears in fields J329-M329 and a 0 if it doesn't.
Click to expand...

Consider rewriting this as

=SUMPRODUCT(COUNTIF(Rawdata!J329:M329,"*"&streetcheck&"*"))

which would be more recalculation-efficient as well as requiring fewer levels of
nested function calls.
What I'm trying to do is be a bit cleverer and add an extra IF
statement that says if it finds an item from the same array in another
field, (in this case I329), to ignore the above statement. However, if
it doesn't find the item in I329 then it should still return 1 if the
original statement is found to be TRUE. ...
=IF(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!I329,0)),0,
IF(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!J329:M329,0)),1,0))
Click to expand...
...

Do you still want to sum the results if streetcheck isn't found in Rawdata!I329?
If so, try

=IF(SUMPRODUCT(COUNTIF(Rawdata!I329,"*"&streetcheck&"*")),0,
SUMPRODUCT(COUNTIF(Rawdata!J329:M329,"*"&streetcheck&"*")))
 
Back
Top