Difficult Formula

[tommcbrny]

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: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(COUNTIF(B5:C5,"fff*")>0,"fff",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

 

[John C]

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(COUNTIF(B5:C5,"*fff*")>0,"fff","")&IF(COUNTIF(B5:C5,"*ggg*")>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(COUNTIF(B5:C5,"*bbb*")>0,64,0)+IF(COUNTIF(B5:C5,"*ccc*")>0,32,0)+IF(COUNTIF(B5:C5,"*ddd*")>0,16,0)+IF(COUNTIF(B5:C5,"*eee*")>0,8,0)+IF(COUNTIF(B5:C5,"*fff*")>0,4,0)+IF(COUNTIF(B5:C5,"*ggg*")>0,2,0)+IF(COUNTIF(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!

 

[Sandy Mann]

Adapting your existing formula try:

=IF(OR(COUNTIF(B5:C5,"*aaa*"),COUNTIF(B5:C5,"*bbb*")),"aaa",IF(COUNTIF(B5:C5,"*ccc*"),"ccc",IF(COUNTIF(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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

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

 

[Bernd P]

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

 

[Bernd P]

Oops, take MAX instead of MIN...

Regards,
Bernd

 

[tommcbrny]

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

 

[tommcbrny]

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]

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

Pete

 

[John C]

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.