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(COUNTIF(B5:C5,"*ccc*")0,"cc c",IF(COUNTIF(B5:C5,"*ddd*")0,"ddd",IF(COUNTIF(B5 :C5,"*eee*")0,"eee",IF(COUNTIF(B5:C5,"fff*")0,"f ff",IF(COUNTIF(B5:C5,"*ggg*")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

Two thoughts on it.
1 If each row in columns B & C will only ever have one occurance of aaa
through fff, then you could use the following formula:
=IF(COUNTIF(B5:C5,"*aaa*")0,"aaa","")&IF(COUNTIF( B5:C5,"*bbb*")0,"aaa","")&IF(COUNTIF(B5:C5,"*ccc* ")0,"ccc","")&IF(COUNTIF(B5:C5,"*ddd*")0,"ddd"," ")&IF(COUNTIF(B5:C5,"*eee*")0,"eee","")&IF(COUNTI F(B5:C5,"*fff*")0,"fff","")&IF(COUNTIF(B5:C5,"*gg g*")0,"ggg","")&IF(COUNTIF(B5:C5,"*hhh*")0,"hhh" ,"")
NOTE: This will not display "Other", however

2 If there is a possibility of multiple occurances of the letters, and in
the example you have given aaa or bbb, then aaa is a result even if ccc, ddd,
eee, fff, ggg,or hhh is in B5:C5, then next priority is ccc resulting in ccc,
even if ddd, eee, fff, ggg, or hhh are in B5:C5. I used the following formula:
=VLOOKUP(IF(COUNTIF(B5:C5,"*aaa*")0,128,0)+IF(COU NTIF(B5:C5,"*bbb*")0,64,0)+IF(COUNTIF(B5:C5,"*ccc *")0,32,0)+IF(COUNTIF(B5:C5,"*ddd*")0,16,0)+IF(C OUNTIF(B5:C5,"*eee*")0,8,0)+IF(COUNTIF(B5:C5,"*ff f*")0,4,0)+IF(COUNTIF(B5:C5,"*ggg*")0,2,0)+IF(CO UNTIF(B5:C5,"*hhh*")0,1,0),$G$1:$H$9,2,TRUE)

My LOOKUP table at $G$1:$H$9 is populated as follows:
G1:G9: 0,1,2,4,8,16,32,64,128
H1:H9: Other,hhh,ggg,fff,eee,ddd,ccc,aaa,aaa

Modify as needed.
Hope this helps!


--
John C


"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(COUNTIF(B5:C5,"*ccc*")0,"cc c",IF(COUNTIF(B5:C5,"*ddd*")0,"ddd",IF(COUNTIF(B5 :C5,"*eee*")0,"eee",IF(COUNTIF(B5:C5,"fff*")0,"f ff",IF(COUNTIF(B5:C5,"*ggg*")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

Adapting your existing formula try:

=IF(OR(COUNTIF(B5:C5,"*aaa*"),COUNTIF(B5:C5,"*bbb* ")),"aaa",IF(COUNTIF(B5:C5,"*ccc*"),"ccc",IF(COUNT IF(B5:C5,"*ddd*"),"ddd",IF(COUNTIF(B5:C5,"*eee*"), "eee",IF(COUNTIF(B5:C5,"fff*"),"fff",IF(COUNTIF(B5 :C5,"*ggg*"),"ggg","Other"))))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"tommcbrny" wrote in message
...
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(COUNTIF(B5:C5,"*ccc*")0,"cc c",IF(COUNTIF(B5:C5,"*ddd*")0,"ddd",IF(COUNTIF(B5 :C5,"*eee*")0,"eee",IF(COUNTIF(B5:C5,"fff*")0,"f ff",IF(COUNTIF(B5:C5,"*ggg*")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

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(C OUNTIF(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,"c cc",IF(COUNTIF(B5:C5,"*ddd*")0,"ddd",IF(COUNTIF(B 5*: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

Hello Tom,

1. Put your search values aaa, bbb, ccc, ..., hhh into a helper area,
say G3:G10 (must start anywhere in row 3).
2. Before that "search list" add references to your input values B5
and C5:
Enter into G1:
=B5
Enter into G2:
=C5
3. Give that area G1:G10 the name slist.
4. Put your return values aaa, aaa (ok, if this was a typo, then bbb),
ccc, ..., hhh into H3:H10.
5. Add two error values (or whatever you prefer here) into H1 and H2
(for example: UNDEF).
6. Give that area H1:H10 the name rlist.

7. Now enter into D5:
=INDEX(rlist,MIN(LOOKUP(2,1/FIND(slist,B5),ROW(slist)),LOOKUP(2,1/
FIND(slist,C5),ROW(slist))))

Regards,
Bernd

Oops, take MAX instead of MIN...

Regards,
Bernd

John, thanks for the response, but I couldn't quite match up the solution
with my requirements. I appreciate the help though, thank you.

"John C" wrote:

Two thoughts on it.
1 If each row in columns B & C will only ever have one occurance of aaa
through fff, then you could use the following formula:
=IF(COUNTIF(B5:C5,"*aaa*")0,"aaa","")&IF(COUNTIF( B5:C5,"*bbb*")0,"aaa","")&IF(COUNTIF(B5:C5,"*ccc* ")0,"ccc","")&IF(COUNTIF(B5:C5,"*ddd*")0,"ddd"," ")&IF(COUNTIF(B5:C5,"*eee*")0,"eee","")&IF(COUNTI F(B5:C5,"*fff*")0,"fff","")&IF(COUNTIF(B5:C5,"*gg g*")0,"ggg","")&IF(COUNTIF(B5:C5,"*hhh*")0,"hhh" ,"")
NOTE: This will not display "Other", however

2 If there is a possibility of multiple occurances of the letters, and in
the example you have given aaa or bbb, then aaa is a result even if ccc, ddd,
eee, fff, ggg,or hhh is in B5:C5, then next priority is ccc resulting in ccc,
even if ddd, eee, fff, ggg, or hhh are in B5:C5. I used the following formula:
=VLOOKUP(IF(COUNTIF(B5:C5,"*aaa*")0,128,0)+IF(COU NTIF(B5:C5,"*bbb*")0,64,0)+IF(COUNTIF(B5:C5,"*ccc *")0,32,0)+IF(COUNTIF(B5:C5,"*ddd*")0,16,0)+IF(C OUNTIF(B5:C5,"*eee*")0,8,0)+IF(COUNTIF(B5:C5,"*ff f*")0,4,0)+IF(COUNTIF(B5:C5,"*ggg*")0,2,0)+IF(CO UNTIF(B5:C5,"*hhh*")0,1,0),$G$1:$H$9,2,TRUE)

My LOOKUP table at $G$1:$H$9 is populated as follows:
G1:G9: 0,1,2,4,8,16,32,64,128
H1:H9: Other,hhh,ggg,fff,eee,ddd,ccc,aaa,aaa

Modify as needed.
Hope this helps!


--
John C


"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(COUNTIF(B5:C5,"*ccc*")0,"cc c",IF(COUNTIF(B5:C5,"*ddd*")0,"ddd",IF(COUNTIF(B5 :C5,"*eee*")0,"eee",IF(COUNTIF(B5:C5,"fff*")0,"f ff",IF(COUNTIF(B5:C5,"*ggg*")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

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

Glad to hear it, Tom - thanks for feeding back.

Pete

On Aug 8, 10:02*am, 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

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