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

Shelby21

New Member
Joined
Nov 21, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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.

123.PNG
 

Attachments

  • 123.PNG
    123.PNG
    55.5 KB · Views: 6

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ThanP

New Member
Joined
Feb 15, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
DEFGHIJKLMNOPUV
55
6
20
21SC11SC15SC19SC21SC25SC29SC31SC35SC39SC41SC45SC49SC11
Sheet1014
Cell Formulas
RangeFormula
V21V21=IFERROR(LOOKUP(2,1/(((D5:O5>4)+(D5:O5<3))*(D5:O5<>"")),D21:O21),"")
 
Last edited:
Solution

Shelby21

New Member
Joined
Nov 21, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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
DEFGHIJKLMNOPUV
55
6
20
21SC11SC15SC19SC21SC25SC29SC31SC35SC39SC41SC45SC49SC11
Sheet1014
Cell Formulas
RangeFormula
V21V21=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

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Forum statistics

Threads
1,176,137
Messages
5,901,567
Members
434,906
Latest member
Prabhu_Churi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top