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

Shelby21

Board Regular
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.

Attachments

• 123.PNG
55.5 KB · Views: 8

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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,

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:
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

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.

You're welcome, thanks for the feedback.

Replies
1
Views
271
Replies
19
Views
238
Replies
3
Views
310
Replies
13
Views
245
Replies
0
Views
102

1,218,538
Messages
6,143,078
Members
450,461
Latest member
Bosavon

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.

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

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