matching values

[rob.baker71]

I have a spreadsheet that contains data exported from a database
containing staff information. What I need to do is compare values to
see if they match and if so, to return the value "Yes". Sounds like a
straightforward IF(AND job, but it's not so simple.

For example, if C10 and E10 contains the value "Grade B" but D10
contains "Nurse Grade B", I want to recognise D10 as matching the
other values. I need to use cell references as I have 1500+ rows with
different job titles in them, so I guess wildcards are out of the
question.

Is there anyway I can refer to C10, D10 and E10 and get it to
recognise not only exact matches but partial ones like in my example
too? Am I making any sense???

 

[Lori]

You could try this array formula (ctrl+shift+enter to execute):

=IF(MAX(COUNTIF(C10:E10,"*"&C10:E10&"*"))=3,"Yes")

you may want to replace blanks in the range with e.g. "(blank)" or add
another condition for them.

 

[Teethless mama]

You don't really need MAX(....

=IF(COUNTIF(C10:E10,"*"&C10:E10&"*")=3,"Yes")

ctrl+shift+enter, not just enter

 

[rob.baker71]

Thank you so much. You guys rock!

- - -

You don't really need MAX(....

=IF(COUNTIF(C10:E10,"*"&C10:E10&"*")=3,"Yes")

ctrl+shift+enter, not just enter

 

[Lori]

No problem, thanks for the comeback.

Think you do need max(...) though, otherwise only the first value is
compared.