IF OR and AND formular, help!.... I want to use so much criteria, Im toally stumped..

novabond · Oct 20, 2022

hello.

Im really stuck, I hope someone can help.

The formular I am looking for has this criteria:

formular in cell S28 (i will refer to this cell as Output cell)
- If there is a "P" in cell $BA28 (I will refer to this cell as Include cell)
  - value if true :
    - If there is text in either S$11 OR S$15 (I will refer to this range as set A) and it matches part of text in $BN28 (i will refer to this cell as Lookup cell)
    - AND
    - If there is text in either S$19 OR S$23 (i will refer to this range as set B) and it matches part of text in $BN28 (as above)
      - = "P"
  - value if false :
    - - = "" (blank)

note, (as i want to try, and best explain the criteria still)
so it will also be that,

If there is a "P" in Include cell
and
If there is text in one or both cells is set A, and both or one of these text doesnt match part text in Lookup cell
AND
If there is text in one or both cells is set B, and both or one of these text does match part text in Lookup cell
or visa versa
I need this to return "" (blank) in the Output cell

and

I was also struggling with a simple way to get the formular, not to recognise an empty cell in sets A or B, as being matched as TRUE, with part text in the Lookup cell

btw, I use "P" in cells, whereby I was to display a tick, by also having font Wingdings 2......

and

as per the illustration image below, I will be copying this formular to other cells.

but, I can eaisly rename the cells in the formular myself, if you want to write this formular uisng the first cells in a worksheet say:
(NB: I wanted to include my actual cell references earlier, incase it becuase relevent in the overall solution, I also cant get XL2BB to work for me at the moment, so cant drop in minisheets to help)
A$1:A$2, set A
A$3:A$4, set B
A5, Output cell
$J5, Include call
$K5, Lookup cell

thanks so much for reading this!... fingeres crossed someone wants to take it on... tnanks again !

SanjayGMusafir · Oct 20, 2022

novabond said:
formular in cell S28 (i will refer to this cell as Output cell)

If there is a "P" in cell $BA28 (I will refer to this cell as Include cell)

value if true :

If there is text in either S$11 OR S$15 (I will refer to this range as set A) and it matches part of text in $BN28 (i will refer to this cell as Lookup cell)

AND

If there is text in either S$19 OR S$23 (i will refer to this range as set B) and it matches part of text in $BN28 (as above)

= "P"

value if false :

= "" (blank)

Try this -

Excel Formula:

=IF(AND($BA28="P",AND(OR(S$11<>"",S$15<>""),OR(Find(S$11,$BN26)>0,Find(S$15,$BN28)>0)),AND(OR(S$19<>"",S$23<>""),OR(Find(S$19,$BN26)>0,Find(S$23,$BN28)>0))),"P","")

novabond · Oct 21, 2022

SanjayGMusafir said:

Try this -

Excel Formula:

=IF(AND($BA28="P",AND(OR(S$11<>"",S$15<>""),OR(Find(S$11,$BN26)>0,Find(S$15,$BN28)>0)),AND(OR(S$19<>"",S$23<>""),OR(Find(S$19,$BN26)>0,Find(S$23,$BN28)>0))),"P","")

Hi Sanjay, thank you for this.... It doesnt seem to work as it is...



																	Include	Lookup
Result of Formular should be			P	P	P	P				P	P	P		P		P
set A	yep	out	yep		yep		out		out	out		yep	yep	yep	out
	out	out	out	in					out	yep		out	in	in	out
set B	out	yep	in	yep		yep	out		yep		out	in	out		yep	yep
	out	out		out							yep	out	out		in	in
Formular					TRUE	TRUE		TRUE						TRUE		TRUE	P	yep in

novabond · Oct 21, 2022

SanjayGMusafir said:

Try this -

Excel Formula:

=IF(AND($BA28="P",AND(OR(S$11<>"",S$15<>""),OR(Find(S$11,$BN26)>0,Find(S$15,$BN28)>0)),AND(OR(S$19<>"",S$23<>""),OR(Find(S$19,$BN26)>0,Find(S$23,$BN28)>0))),"P","")

me again.... i have been able to insert the table with XL2BB now, incase you want to take a look, Im also going to close this thread and start a new one so I can include the table from the start, I hope to see you there... thank you again!

SanjayGMusafir · Oct 21, 2022

novabond said:
Hi Sanjay, thank you for this.... It doesnt seem to work as it is...

I copied your XL2BB file to check the problem - It's giving strange formula you have put. Please check the formula and cell references.

novabond · Oct 21, 2022

hey

SanjayGMusafir said:
I copied your XL2BB file to check the problem - It's giving strange formula you have put. Please check the formula and cell references.

hey, is this any better?

Bank_Vat_and_Departments_Return_WORKING_DocNOWNEW.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

2

Include

Lookup

3

Result of Formular should be

P

4

set A

yep

out

yep

out

yep

out

5

out

in

out

yep

out

in

out

6

set B

out

yep

in

yep

out

yep

out

in

out

yep

7

out

yep

out

in

8

Formular

TRUE

P

yep in

or and or workings (3)

Cell Formulas
Range	Formula
C8:R8	C8	=IFERROR(IF(AND($S8="P",AND(OR(C$4<>"",C$5<>""),OR(FIND(C$4,$T8)>0,FIND(C$5,$T8)>0)),AND(OR(C$6<>"",C$7<>""),OR(FIND(C$6,$T8)>0,FIND(C$7,$T8)>0))),"P","")=IF(AND($S8="P",AND(OR(C$4<>"",C$5<>""),OR(FIND(C$4,$T8)>0,FIND(C$5,$T8)>0)),AND(OR(C$6<>"",C$7<>""),OR(FIND(C$6,$T8)>0,FIND(C$7,$T8)>0))),"P",""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:R3	Cell Value	=0	text	NO
C3:R3,C8:R8	Expression	=C$3="P"	text	NO
N7	Cell Value	=0	text	NO
G7:M7,O7:R7	Cell Value	=0	text	NO
B7:F7	Cell Value	=0	text	NO
N6	Cell Value	=0	text	NO
G6:M6,O6:R6	Cell Value	=0	text	NO
B6:F6	Cell Value	=0	text	NO
N5	Cell Value	=0	text	NO
G5:M5,O5:R5	Cell Value	=0	text	NO
B5:F5	Cell Value	=0	text	NO
B4:R4	Cell Value	=0	text	NO

novabond · Oct 21, 2022

hmm....

SanjayGMusafir said:
I copied your XL2BB file to check the problem - It's giving strange formula you have put. Please check the formula and cell references.

hmm i see what you mean... unsure why the formulars are jumping around... ive tried adjusting the colum width incase that helps...

IFORAND.xlsx

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

2

Include

Lookup

3

Should output

P

4

set A

yep

out

yep

out

yep

out

5

out

in

out

yep

out

in

out

6

set B

out

yep

in

yep

out

yep

out

in

out

yep

7

out

yep

out

in

8

Formular

TRUE

P

yep in

if and or workings

Cell Formulas
Range	Formula
C8:R8	C8	=IFERROR(IF(AND($S8="P",AND(OR(C$4<>"",C$5<>""),OR(FIND(C$4,$T8)>0,FIND(C$5,$T8)>0)),AND(OR(C$6<>"",C$7<>""),OR(FIND(C$6,$T8)>0,FIND(C$7,$T8)>0))),"P","")=IF(AND($S8="P",AND(OR(C$4<>"",C$5<>""),OR(FIND(C$4,$T8)>0,FIND(C$5,$T8)>0)),AND(OR(C$6<>"",C$7<>""),OR(FIND(C$6,$T8)>0,FIND(C$7,$T8)>0))),"P",""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:R3	Cell Value	=0	text	NO
C3:R3,C8:R8	Expression	=C$3="P"	text	NO
N7	Cell Value	=0	text	NO
G7:M7,O7:R7	Cell Value	=0	text	NO
B7:F7	Cell Value	=0	text	NO
N6	Cell Value	=0	text	NO
G6:M6,O6:R6	Cell Value	=0	text	NO
B6:F6	Cell Value	=0	text	NO
N5	Cell Value	=0	text	NO
G5:M5,O5:R5	Cell Value	=0	text	NO
B5:F5	Cell Value	=0	text	NO
B4:R4	Cell Value	=0	text	NO

SanjayGMusafir · Oct 21, 2022

Check below -

As my suspicion was, you had put wrong formula

In cell C8 I have corrected the formula
While in D8 I have kept formula that you have used, so that you could recognize change & error.
In Cell C8 it is returning Blank as the conditions given it should return blank only.

Please check and revert.

All Records.xlsb

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

Include

Lookup

3

Result of Formular should be

P

4

set A

yep

out

yep

out

yep

out

5

out

in

out

yep

out

in

out

6

set B

out

yep

in

yep

out

yep

out

in

out

yep

7

out

yep

out

in

8

Formular

TRUE

P

yep in

Sheet2

Cell Formulas
Range	Formula
C8	C8	=IF(AND($S8="P",AND(OR(C$4<>"",C$5<>""),OR(IFERROR(FIND(C$4,$T8),0)>0,IFERROR(FIND(C$5,$T8),0)>0)),AND(OR(C$6<>"",C$7<>""),OR(IFERROR(FIND(C$6,$T8),0)>0,IFERROR(FIND(C$7,$T8),0)>0))),"P","")
D8:R8	D8	=IFERROR(IF(AND($S8="P",AND(OR(D$4<>"",D$5<>""),OR(FIND(D$4,$T8)>0,FIND(D$5,$T8)>0)),AND(OR(D$6<>"",D$7<>""),OR(FIND(D$6,$T8)>0,FIND(D$7,$T8)>0))),"P","")=IF(AND($S8="P",AND(OR(D$4<>"",D$5<>""),OR(FIND(D$4,$T8)>0,FIND(D$5,$T8)>0)),AND(OR(D$6<>"",D$7<>""),OR(FIND(D$6,$T8)>0,FIND(D$7,$T8)>0))),"P",""),"")

novabond · Oct 21, 2022

SanjayGMusafir · Oct 21, 2022

novabond said:
View attachment 76737

You have highlighted few cells in above PNG (screenshot) - Hope all is working well?

IF OR and AND formular, help!.... I want to use so much criteria, Im toally stumped..

novabond

New Member

Excel Facts

SanjayGMusafir

Well-known Member

novabond

New Member

novabond

New Member

Attachments

SanjayGMusafir

Well-known Member

novabond

New Member

novabond

New Member

SanjayGMusafir

Well-known Member

novabond

New Member

SanjayGMusafir

Well-known Member

Similar threads

Forum statistics

Share this page

IF OR and AND formular, help!.... I want to use so much criteria, Im toally stumped..

New Member

Excel Facts

Well-known Member

New Member

New Member

Attachments

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock