Search for multiple words

[Guest]

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

 

[Frank Kabel]

Hi
could you put your list of words in a separate range?

 

[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 D12 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 (D12) an
you'll have to add the values/names..
Choose(...,"Paul","Marc",etc...)

Hope this helps!

 

[Guest]

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

 

[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

 

[Guest]

It is not working - When I brake down, the Formula "Match" returns #N/A

 

[Domenic]

It is not working - When I brake down, the Formula "Match" returns #N/A

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

 

[Frank Kabel]

Hi
have you entered this formula as array formula?

 

[Guest]

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.