How do I change the value in one cell if two or more cells in the same row match one another?

[evanlamarr88]

In the attached report, I want to change the value of the cells in column C based off the results in the same row in columns F & H (plus other columns to be added later).

To set up the problem:

Each staff member on the left has traditionally had to spend 10% of their time calling partners on an annual basis.
This year, we're testing an opt-in initiative in which each staff member only has to complete 13 hours per month but only has one grace month, and must make up the hours he missed that month the following month, as well as complete that month's quota. I.e., if you only completed 10 hours in January, you must complete the three hours you missed in February, as well as February's 13 hours, for a total of 16 hours in February.
If they do not complete 16 hours in February, they have to revert to the traditional completion plan.

My problem is that with several months of data and several staff, it will become hard to tell who has used a grace month more than once.

Is there any way to change "Opt-In" in Column C to "Standard" if any two cells in that row in Columns F, H, etc. contain the word "Under"?

The formulas in column F & H are already based off column C, so that adds an extra layer of complexity.

 

[Hui]

Why not just add a Conditional Format to Column C to highlight the issues
select C2:C68
Goto CF
New Rules, Use a Formula
=AND(F2="Under",H2="Under")
apply a Format

see attached file:

 

[evanlamarr88]

Thanks, @Hui! This is a good roundabout fix for what I wanted; however, I was hoping a formula could be created that would take into consideration ALL cells in a certain range, check for duplicity of specific data, and change/toggle the output value by set parameters accordingly - like some kind of =IF iteration with a duplicate values check formula. Can it be done, to your knowledge? I tried to use MATCH, but you can't choose certain values within a range - only the entire range.

 

[bosco_yip]

....
Is there any way to change "Opt-In" in Column C to "Standard" if any two cells in that row in Columns F, H, etc. contain the word "Under"....

Try,

Select C2:C68 >> Conditional Format >>

>> Click "New Rules", select "Use a Formula…", in the rule formula box enter :

=AND(F2="Under",H2="Under")

Then,

>> Click "Format" >>

Click "Number" tag >> choose "Custom", in the Type box enter: ;;;"Standard"

and, Click "Font" tag, in "color" choose red color

>> OK

Regards
Bosco