Be aware. If your cells B5:C5 contain multiple occurrances of aaa, bbb, ccc,
ddd, eee, fff, ggg, or hhh (of any 1 or combination thereof), your cell
result will include multiple references, i.e.: aaadddeee.


--
John C


"tommcbrny" wrote:

Thanks Pete, this did the trick. I'm OK with a blank instead of "other" so
long as all of the values I need to capture are covered. Many thanks!
Tom

"Pete_UK" wrote:

Rather than nesting the IFs, you can concatenate them, like this:

=IF(COUNTIF(B5:C5,"*aaa*")0,"aaa","")&IF(COUNTIF( B5:C5,"*bbb*")0,"aaa","")&IF(COUÂ*
NTIF(B5:C5,"*ccc*")0,"ccc","")&IF(COUNTIF(B5:C5," *ddd*")0,"ddd","")&IF(COUNTIF(B5Â*:C5,"*eee*")0, "eee","")&IF(COUNTIF(B5:C5,"fff*")0,"fff","")&IF( COUNTIF(B5:C5,"*ggÂ*
g*")0,"ggg","")

and then you can add other conditions on the end of this in the same
way. You lose the ability to return "Other", although you could pick
this up in a helper cell.

Hope this helps.

Pete

On Aug 7, 4:04 pm, tommcbrny
wrote:
Hi,

I am trying to check two cells for text and then display some other text in
a destination cell using if(countif...

It works quite well except for my having 8 values to check for and only 7
possible IF functions. Here is what I am trying to do:

If b5 or c5 contain the text "aaa", then set "aaa" in d5
If b5 or c5 contain the text "bbb", then set "aaa" in d5
If b5 or c5 contain the text "ccc", then set "ccc" in d5
If b5 or c5 contain the text "ddd", then set "ddd" in d5
and so on to "hhh"

You can see that I want the same destination text for the first two possible
source texts, but then go on one for one for the rest of the source texts..

Here is the formula (placed in d5) I am using, which again works quite well
except for hitting the 7 IF limit.

=IF(COUNTIF(B5:C5,"*aaa*")0,"aaa",IF(COUNTIF(B5:C 5,"*bbb*")0,"aaa",IF(COUÂ*NTIF(B5:C5,"*ccc*")0," ccc",IF(COUNTIF(B5:C5,"*ddd*")0,"ddd",IF(COUNTIF( B5Â*:C5,"*eee*")0,"eee",IF(COUNTIF(B5:C5,"fff*") 0,"fff",IF(COUNTIF(B5:C5,"*ggÂ*g*")0,"ggg","Other ")))))))

I have received help here before in using a List and ISNUMBER in place of
COUNTIF, but do not know how to make this function change the value of the
text (set destination cell to aaa if source cells contain either aaa or bbb).

I have also used VLOOKUP previously, but the source cells my current case do
not solely contain the text I am searching, so do not think VLOOKUP is the
right solution.

Can anyone help with this? Apologies for the long post, but I wanted to get
all of the relevant information in.

Thanks,

Tom