K
Kashyap
I was try on a formula something like, if 1 of the words in b10=d10 then it
should give output as 1 in E10
should give output as 1 in E10
J
Jock
In E10 put:
=COUNTIF(B10
10,"your word")
=COUNTIF(B10
10,"your word")
K
Ken Johnson
=IF(COUNTIF(B10,"*"&D10&"*")=1,1,"")
Ken Johnson
G
Glenn
Kashyap said:
Case sensitive:
=IF(ISERROR(FIND(B10,D10)),0,1)
Not case sensitive:
=IF(ISERROR(SEARCH(B10,D10)),0,1)
G
Glenn
Glenn said:
Actually, I think i reversed your needs and didn't allow for only whole word
matches:
=IF(ISERROR(FIND(" "&D10&" "," "&B10&" ")),0,1)
=IF(ISERROR(SEARCH(" "&D10&" "," "&B10&" ")),0,1)
K
Kashyap
I tried implimenting this for a range.. But..
That is Say I have words list from H1:H25 and sentences from B1:B200
Now, in ColC (C1:C200) I need a formula which will search for a word (which
is in H1:H25) in B1:B200 and enter that particular word.
That is Say I have words list from H1:H25 and sentences from B1:B200
Now, in ColC (C1:C200) I need a formula which will search for a word (which
is in H1:H25) in B1:B200 and enter that particular word.
G
Glenn
The following array formula (commit with CTRL+SHIFT+ENTER) in C1 and copied down
to C200 should work if only one word will be found in each sentence:
=INDEX($H$1:$H$25,MATCH(0,--ISERROR(FIND(" "&$H$1:$H$25&" "," "&B1&" ")),0))
to C200 should work if only one word will be found in each sentence:
=INDEX($H$1:$H$25,MATCH(0,--ISERROR(FIND(" "&$H$1:$H$25&" "," "&B1&" ")),0))
K
Kashyap
=IF(ISERROR(SEARCH(B10,D10)),0,1) - This formula will search a word even
though something else is prefixed/suffixed to the word
where as
=INDEX($H$1:$H$25,MATCH(0,--ISERROR(search(" "&$H$1:$H$25&" "," "&B1&"
")),0))
- This will not serch if something else is prefixed/suffixed to the word.
For Eg., - Take a word Kashyap
if in a sentence word is Kashyap# then 1st formula detects where as 2nd
doesn't..
How to make 2nd formula detect words as 1st?
though something else is prefixed/suffixed to the word
where as
=INDEX($H$1:$H$25,MATCH(0,--ISERROR(search(" "&$H$1:$H$25&" "," "&B1&"
")),0))
- This will not serch if something else is prefixed/suffixed to the word.
For Eg., - Take a word Kashyap
if in a sentence word is Kashyap# then 1st formula detects where as 2nd
doesn't..
How to make 2nd formula detect words as 1st?
K
Kashyap
Now formula is not working at all..
Glenn said:
K
Kashyap
I'm getting required result from
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(" "&DB!$A$1:$A$4003&" ","
"&B8&" ")),0))
but not from
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(DB!$A$1:$A$4003,B8)),0))
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(" "&DB!$A$1:$A$4003&" ","
"&B8&" ")),0))
but not from
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(DB!$A$1:$A$4003,B8)),0))
G
Glenn
Kashyap said:
Remove the
" "&
and
&" "
from both parts of the formula.
G
Glenn
What do you have for the "new formula"?
K
Kashyap
yes, I did enter both as array-formulas.. result from 2nd formula was '0'
G
Glenn
Looks right...did you enter both as array-formulas (commit with
CTRL+SHIFT+ENTER)? What result are you getting from the second one?
CTRL+SHIFT+ENTER)? What result are you getting from the second one?
G
Glenn
Kashyap said:
Without seeing your data, I can't say why you would be getting that result. If
you want, post your workbook on www.savefile.com and I'll take a look at it.
K
Kashyap
have uploaded file at http://www.savefile.com/files/2086874
G
Glenn
It is the blank lines within DB!A1:A4003 that are causing the problem. Either
remove them, replace them with non-matching data (I used "blank line") or adjust
the formula to something like this (array-entered):
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(IF(DB!$A$1:$A$4003="",
"blank line",DB!$A$1:$A$4003),A2)),0))
remove them, replace them with non-matching data (I used "blank line") or adjust
the formula to something like this (array-entered):
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(IF(DB!$A$1:$A$4003="",
"blank line",DB!$A$1:$A$4003),A2)),0))
K
Kashyap
I'm getting error.. #N/A
G
Glenn
Kashyap said:
Yes, if no match is found.