if countif is true, return the match

[xtreme07]

hi,

i need a formula that would return in columnC the match between columns A and B.

so, i need to scan colA using the colB, and return in colC the match that was found between the 2 col

---------------------/

columnA - one cell with a group of words (ex: going to gym)
columnB - single words. 100 lines

columnA is scaned using the list from columnB
------------//


your input is very much appreciated

 

[AlphaFrog]

Excel Workbook
	A	B	C
1	going to the gym	Dog	No Match
2	The boy can swim.	gym	going to the gym
3	I have a cat	Cat	I have a cat
4	the quick brown fox
...

Cell Formulas
Range	Formula
C1	=IF(COUNTIF(A:A,"*"&B1&"*")>0,INDEX(A:A,MATCH("*"&B1&"*",A:A,0)),"No Match")
C2	=IF(COUNTIF(A:A,"*"&B2&"*")>0,INDEX(A:A,MATCH("*"&B2&"*",A:A,0)),"No Match")
C3	=IF(COUNTIF(A:A,"*"&B3&"*")>0,INDEX(A:A,MATCH("*"&B3&"*",A:A,0)),"No Match")

 

[xtreme07]

ty AlphaDog for posting / for your help ... seems your formula is working fine. i also found meanwhile a formula that does exactly what i need and can be seen below for those that run into this thread and need similar help.

=IF(COUNTIF(A2,"*"&$B$2:$B$31&"*"),$B$2:$B$31,0)

AlphaDog - i would be interested how you pasted tables into your post. a link to the tutorial would be great if tyiping the answer here doesn't take to much time.

Edit: for some reason both formulas work only for the 1st cell and not the second,3rd and all others.. i wonder why ..

 

[AlphaFrog]

AlphaFrog - i would be interested how you pasted tables into your post. a link to the tutorial would be great if tyiping the answer here doesn't take to much time.

Click on the links in my signature below.

 

[xtreme07]

AlphaDog - i actually tried the formula and it doesn't do what i need, i tried tho some tweaking / inverting of cells and it does what is needed. so, bottom line: it works! but it doesn't

same as my formula, your formula works only for the 1st cell .. i wonder why .,. any ideea ?

here the inversion of the cells
=IF(COUNTIF(A2,"*"&B1:B100&"*")>0,INDEX(A2,MATCH("*"&B1:B100&"*",A2,0)),"No Match")

preview of what i need

group of word1, word1, word1
group of word1, group, group
group of word1, other, 0

 

[barry houdini]

Edit: for some reason both formulas work only for the 1st cell and not the second,3rd and all others.. i wonder why ..

Try this formula in C2 copied down

=LOOKUP(2^15,SEARCH(B$2:B$31,A2),B$2:B$31)

I'm assuming that B2:B31 has no blanks

 

[xtreme07]

Try this formula in C2 copied down

=LOOKUP(2^15,SEARCH(B$2:B$31,A2),B$2:B$31)

I'm assuming that B2:B31 has no blanks

perfect .. ty very much

 

[Aladin Akyurek]

hi,

i need a formula that would return in columnC the match between columns A and B.

so, i need to scan colA using the colB, and return in colC the match that was found between the 2 col

---------------------/

columnA - one cell with a group of words (ex: going to gym)
columnB - single words. 100 lines

columnA is scaned using the list from columnB
------------//


your input is very much appreciated

Try...

Either:

=LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$100,A2),$B$2:$B$100)

Or: (probably better)

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$B$2:$B$100&" "," "&A2&" "),$B$2:$B$100)

 

[AlphaFrog]

Or this...

Excel Workbook
	A	B	C
1	group of word1	word1	word1
2	group of word1	group	group
3	group of word1	other	0
...

Cell Formulas
Range	Formula
C1	=IF(COUNTIF(A:A,"*"&B1&"*"), B1, 0)
C2	=IF(COUNTIF(A:A,"*"&B2&"*"), B2, 0)
C3	=IF(COUNTIF(A:A,"*"&B3&"*"), B3, 0)