Maybe an array ?

[Tom Rector]

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
(e-mail address removed)

 

[Tom Ogilvy]

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]

Thanks for your help sorry, I didn't explain it more clearly. I do
appreciate your help.

Using what you sent, this fromula in J15 almost gets me to were I need
to be.

=IF(COUNTIF(B15,"*retirement*")>0,VLOOKUP(B15,Rule1!$A$1:$B$6,2,FALSE),"
Not Found")

B15 is "not" in the sheet Rule1.

If I can explain correctly, where "*retirement*" is in the formula, I
want to check B15, if B15 has any word in
Rule1!$A$1:$A$6 I want to return the text value corresponding in
Rule1!$B$1:$B$6

And the word "retirement" is in a longer text string in B15. ie:
"Individual recognition, retirement, Maj Bob Jones"
would be the text string in cell B15

This is how Rule1 looks:
A B
1 retirement Rule 1
2 award Rule 2
3 CC call Rule 3
4 certificate Rule 4
5 memneto Rule 5
6 hosting Rule 6


Thomas Rector



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!