Formula for Multiple OR Conditions Based on Different Cell Values with Text Output

[Shelby21]

Hi guys,

Need some help with creating a formula for multiple OR conditions based on different cell values with text output.

Looking at the chart below, there are several tables:

- The first table under EEQA Timing Checks is where the user will input the current speed value of the cylinder movement

- The second table under Speed Controller Location just shows which speed controller correlates with the user values entered in the EEQA timing Checks Table

- The smaller tables to the right are meant to help with location of speed controller to be adjusted. The ordered numbers signify where each speed controller is located as they are stacked on top of one another.

What I want to do:

In cell V21, IF(OR(D5>4,D5<3),"SC11",IF(OR(E5>4,E5<3),"SC15",IF(OR(F5>4,F5<3),"SC19"))))

Of course this formula does not work, but should explain what I am trying to do. I want cell V21 to output the speed controller number based on the user entered data in cells D5:F5 Passing Range is 3 - 4. Anything outside of 3 - 4 and I would like V21 to output which speed controller is out of range.

 

[ThanP]

Hi,

This formula will work

=IF(OR(D5>4,D5<3),D20,IF(OR(E5>4,E5<3),E20,IF(OR(F5>4,F5<3),F20,"")))

But, if you have many cells outside 3-4, it will return the first one.

Hope it helps,

Regards,

 

[jtakw]

Hi,

If there can only be 1 value in the range D5:O5, this will result in the corresponding value in D21:O21
If there are more than 1 value matching criteria in range D5:O5, this will result for the last occurrence
If no value or no value >4, <3, results "" blank.

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P					U	V
5	5
6
20
21	SC11	SC15	SC19	SC21	SC25	SC29	SC31	SC35	SC39	SC41	SC45	SC49							SC11
Sheet1014

Cell Formulas
Range	Formula
V21	V21	=IFERROR(LOOKUP(2,1/(((D5:O5>4)+(D5:O5<3))*(D5:O5<>"")),D21:O21),"")

 

[Shelby21]

Hi,

This formula will work

=IF(OR(D5>4,D5<3),D20,IF(OR(E5>4,E5<3),E20,IF(OR(F5>4,F5<3),F20,"")))

But, if you have many cells outside 3-4, it will return the first one.

Hope it helps,

Regards,

Hi Than,

Thanks for your help! I tried your formula, but it did not work. It was behaving the same as my original formula.

Cell V21 would always output SC11 even if there was no user entered data. Output would also never change even when user data was outside of 3 - 4

 

[Shelby21]

Hi,

If there can only be 1 value in the range D5:O5, this will result in the corresponding value in D21:O21
If there are more than 1 value matching criteria in range D5:O5, this will result for the last occurrence
If no value or no value >4, <3, results "" blank.

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P					U	V
5	5
6
20
21	SC11	SC15	SC19	SC21	SC25	SC29	SC31	SC35	SC39	SC41	SC45	SC49							SC11
Sheet1014

Cell Formulas
Range	Formula
V21	V21	=IFERROR(LOOKUP(2,1/(((D5:O5>4)+(D5:O5<3))*(D5:O5<>"")),D21:O21),"")

Hi jtakw,

Your formula worked perfectly. When there is no user data, cell V21 is blank and when user data was outside of 3 - 4, the correct speed controller number was displayed as the output.

Thank you for your help!

 

[jtakw]

You're welcome, thanks for the feedback.