Search for multiple words

T

titoto

I need a formula which would enable me to search for more than 7 words ( I
have at least 20 word) and when 1 of the word is found to display that word.
In any given cell will contain 1 of the words needed.

'IF', 'Countif' function allow no more than 7 words.Any idea
eg
A B
hdfjjkduo...PAUL...iuepoipwp search PAUL or MARC or so on- Result
= PAUL


hhjhwoujiowqu...MARC...ioo Result = MARC
so on so on
 
D

Domenic

I'm not sure this is what you're looking for, but see if this helps...

B1, copied down:

=CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$2,A1)),0),"Paul","Marc")

...entered using CONTROL+SHIFT+ENTER, where D1:D2 contain the words fo
which you're searching. You'll be able to expand the list of words fo
which to search. Make sure you make the necessary adjustments to th
formula. For example, you'll have to change the range (D1:D2) an
you'll have to add the values/names..
Choose(...,"Paul","Marc",etc...)

Hope this helps!
 
T

titoto

Ok - Let's say 'sheet 2' col A contains the names
eg
JULIE
SAM
PAUL
RAY
so on

PS - DOMENIC/Frank Kabel - on 9/22 RE: Finding a string within a string you
posted the following formula
=TRIM(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))
This formula is good only if search words have the exact common character
before and after it
 
F

Frank Kabel

Hi
try:
=INDEX('sheet2'!A1:A10,MATCH(TRUE,ISNUMBER(FIND('sheet2'!A1:A10,A1)),0)
)
enter this as array formula with CTRL+SHIFT+ENTER
 
D

Domenic

titoto said:
It is not working - When I brake down, the Formula "Match" returns #N/A
Click to expand...

I'm still not sure what it is you're looking for. Can you provide mor
details? Preferably a small sample along with the expected results
 
K

Kassie

Hi
On searching for your query re Macros, I noticed that you also had a problem
with multiple IF statements. Apart from using Index, you can, by using
helper column, create if statements nesting as many as you like. the formula
is:
=IF(<Cond1>,<Result1>,<Cond2>,<Res2>......Cond&,Res7,"NOT YET")))))))
In the next column, you insert the following formula:
=IF(<Previous Column><>"NOT YET",<Previous Column>,IF(Cond8,Res8 and so on.
 
Top