View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Maybe an array ?

Your are not making much sense.

for the formula you show

=if(countif(B2,"*retirement*")0,J5,"Not Found")

will return the value in J5 if the word retirement is found in B2

You initially say column B holds 10 word phases, then show column B with
single word search terms. No telling how one relates what is in column J to
specific rules, where the phases really are or what is actually in column B
or the significance of redundant values in B and what that would mean in
terms of deciding on a rule.

Regards,
Tom Ogilvy


Tom Rector wrote in message
om...
Office XP pro,

Thanks in advance, I have been trying to figure this out for a
week.


Have a 10 words text in each cell in column B
Have 1 reference each in Column J

Need to pick a cell from column J based on one or more of the words in
column B and show itin column H

Something like:
=if(b2="*retirement*",J5,"NOT FOUND") Notice the *, the word
retirement is included in a phrase that contain many other words.

but it is much more complicated than that:

Probably an "Array", which I just can't seen to get the hang of,
There are many possible conditions in column B that need corresponding
entries for column J:

Column B Column J
retirement Not found
award Rule 10
call Rule 5
Welcoming Rule 12
hosting Rule 2
change Rule 8
,etc ,etc


Such that B2:B2..B100 =J2..J2:J23 (only 22 rules + "Not Found")
Column B can be any length but would have only a limited amout of
words to lookup the rule in J. Each cell in B would have only one
reference in J, but B could be redundant, for ex: B2 could be "award"
and B6 could be "award" and B32 could be "award".

Thanks again,,, too tough for me