nested IF using MATCH

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

rudekid

re-posting this in case anyone who can help missed it when it was posted
last night

hello

Slight problem with formula I am trying to adapt, originally 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 add a caveat: an extra IF statement that says
if it finds an item from the same array in another field, (in this case
I329), then 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. In other words it should do this:

IF STREETCHECK appears in I329 return 0
IF it DOESN'T test for it in J:M columns
IF IT DOES appear, return 1
If it DOESN'T appear return 0

Confused?

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

=SUM(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.
 
=SUM(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.
Click to expand...

I'm not testing it but I would have thought that the solution would be to put a
NOT around the first ISERROR argument:

IF(not(ISERROR(MATCH("*"&streetcheck&"*",Rawdata!I329,0))),0,...
use NOT but it doesn't like that one bit
Click to expand...

What does it do?


--ron
 
what happens is....I realise I can't write Excel functions since it
works fine.

logical thinking with a lack of quality control on my behalf: story of
my life...
 
Back
Top