IF and OR nested statement help

[mralmackay]

Hi,

Think I'm nearly there with my requirement but can't seem to get it
correct and into one formula!!

Basically need to look for where "*** Created ***" or "*** Deleted
***" appears within column J and Column G contains "Bank Details".
I've worked this formula out as per below:
=IF(AND(G21="Bank Details"),(OR(J21="*** Deleted ***",J21="*** Created
***")),)

However, I also need to have another IF statement in this formula for
if the above isn't appearing but the first four letters of column J
contains "Bank" then again show True. On it's own this formula would
do this:
=IF(FIND("Bank",J22)=1,TRUE,FALSE)

How do I basically combine this into one formula? This would be for
each row so obviously my example is just using the row underneath. In
summary:
- If entry in column J = *** Created *** OR = *** Deleted *** AND
entry in column G = "Bank Details" then put true.
- If first 4 characters within column J's entry = "Bank" then put
true

ELSE:
FALSE

Appreciate any help.

Thanks, Al.

 

[Dave Peterson]

If you just want to return true/false:

=AND(LEFT(J22,4)="bank",OR(J21="*** deleted ***",J21="*** created ***"))
or
=AND(LEFT(J22,4)="bank",OR(J21={"*** deleted ***","*** created ***"}))

You could use =find() or =search() if you wanted, but =left(,4) maybe easier to
understand.

 

[Sandy Mann]

Does:

=IF(OR(LEFT(G21,4)="Bank",AND(G21="Bank Details"),(OR(J21="*** Deleted
***",J21="*** Created ***"))),TRUE,FALSE)

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

try:

=IF(OR(ISNUMBER(FIND("Bank",J21)),AND(G21="Bank Details"),(OR(J21="***
Deleted ***",J21="*** Created****"))),TRUE,FALSE)

 

[mralmackay]

Ahh... Thanks all. Last formula seems to do this, however I've now
got a further problem when looking @ the data set. It seems though
looking at the data with the formulas a better way of sorting my data
would be if I could also say if the line is the line with "Bank" as
the first 4 characters in the cell J, then only show TRUE if the line
above is the one that contains *** Created *** or *** Deleted ***

Sorry to be a pain.

Thanks for your help so far.

Al.