Searching for Words in two Separate Columns

[gmelchor]

I am trying to determine if a word appears in two separate columns in the same row. The difficulty is that I have to search for the word in both columns.


I have had success using {=sum(if(isnumber(find(WORD,array),1,0))} to find out how many times the word occurs in each column. However, I need to figure out if it occurs in the same row across two columns.


I have succesfully used the formula =IF(ISNUMBER(FIND(word,row))*AND(IF(ISNUMBER(FIND(word,row)),1,0)),1,0) to do it row by row, but I was wondering if there was a way to make this an array formula.


Any help will be greatly appreciated!

 

[SirJB7]

Hi, gmelchor!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Assuming your data ranges from A2:A14 and your search argument is in D1, try this:

=SUMAPRODUCTO(SIGNO(SI.ERROR(ENCONTRAR($D$1;A$2:A$14);0))*SIGNO(SI.ERROR(ENCONTRAR($D$1;B$2:B$14);0))) -----> in english: =SUMPRODUCT(SIGN(IFERROR(FIND($D$1,A$2:A$14),0))*SIGN(IFERROR(FIND($D$1,B$2:B$14),0)))


Related file link:

https://dl.dropbox.com/u/60558749/Searching%20for%20Words%20in%20two%20Separate%20Columns%20%28for%20gmelchor%20at%20chandoo.org%29.xlsx


Regards!

 

[gmelchor]

That worked brilliantly! You truly are an excel ninja! Thanks for your help!

 

[shrivallabha]

If you are going to use ARRAY formula (CSE) then you will be better off with this SUM formula as SUMPRODUCT tends to be slower:

=SUM(ISNUMBER(FIND(D1,A1:A14,1))*ISNUMBER(FIND(D1,B1:B14,1)))


And if you want to use SUMPRODUCT then this will work without CSE:

=SUMPRODUCT(ISNUMBER(FIND(D1,A1:A14,1))*ISNUMBER(FIND(D1,B1:B14,1)))

 

[Haseeb A]

You can also use that with 1 ISNUMBER,


=SUMPRODUCT(ISNUMBER(FIND(D1,A2:A14)+FIND(D1,B2:B14))+0)


If both ranges are together, like A:B, B:C NOT A & C, B & E..


=SUMPRODUCT((MMULT(ISNUMBER(FIND(D1,A2:B14))+0,{1;1})=2)+0)


FIND is case sensitive, if you don't want to look for case sensitive change to SEARCH

 

[SirJB7]

Hi, gmelchor!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

PS: Give a try to both suggestions from shrivallabha & Haseeb A.


@shrivallabha & Haseeb A

Hi!

Nice approach, I liked both your 2nd formulas.

Regards!