matching values

  • Thread starter Thread starter rob.baker71
  • Start date Start date
R

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???
 
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.
 
You don't really need MAX(....

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

ctrl+shift+enter, not just enter
 
Thank you so much. You guys rock!

- - -


Teethless said:
You don't really need MAX(....

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

ctrl+shift+enter, not just enter
Click to expand...
 
No problem, thanks for the comeback.

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