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.

On 13 Mar, 16:57, wrote:
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 don't really need MAX(....

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

ctrl+shift+enter, not just enter



"Lori" wrote:

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.

On 13 Mar, 16:57, wrote:
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???

Thank you so much. You guys rock!

- - -


Teethless mama wrote:
You don't really need MAX(....

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

ctrl+shift+enter, not just enter



"Lori" wrote:

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.

On 13 Mar, 16:57, wrote:
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???

No problem, thanks for the comeback.

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

On 14 Mar, 13:12, wrote:
Thank you so much. You guys rock!

- - -



Teethless mama wrote:
You don't really need MAX(....

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

ctrl+shift+enter, not just enter

"Lori" wrote:

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.

On 13 Mar, 16:57, wrote:
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???- Hide quoted text -

- Show quoted text -