Isolating part of text to use in an IF/AND FUNCTION

[Smacky]

I have two columns, one containing names and the other colours. I want
to add values to a third column based on a portion of the first column
and the entire second column.

For example:

A(name) B(shirt)
bobby red
joe blue
mary black
bobbyjunior red
joe blue
mary black

I want to give bobby with a red shirt $60, and joe 40. mary will get
120. but bobbyjunior with a red shirt should only get $20. this is an
arbitrary example for a much more complex cell entry, and i need to
isolate *junior*, cannot just use all of bobbyjunior since maryjunior
and joejunior would also get smaller amounts, that's the logic i'm
going for.

 

[Don Guillett Excel MVP]

I have two columns, one containing names and the other colours. I want
to add values to a third column based on a portion of the first column
and the entire second column.

For example:

A(name)                        B(shirt)
bobby                            red
joe                                blue
mary                             black
bobbyjunior                    red
joe                                blue
mary                             black

I want to give bobby with a red shirt $60, and joe 40. mary will get
120. but bobbyjunior with a red shirt should only get $20. this is an
arbitrary example for a much more complex cell entry, and i need to
isolate *junior*, cannot just use all of bobbyjunior since maryjunior
and joejunior would also get smaller amounts, that's the logic i'm
going for.

Not clear what you are doing but SUMPRODUCT should be helpful....

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Ron Rosenfeld]

I have two columns, one containing names and the other colours. I want
to add values to a third column based on a portion of the first column
and the entire second column.

For example:

A(name) B(shirt)
bobby red
joe blue
mary black
bobbyjunior red
joe blue
mary black

I want to give bobby with a red shirt $60, and joe 40. mary will get
120. but bobbyjunior with a red shirt should only get $20. this is an
arbitrary example for a much more complex cell entry, and i need to
isolate *junior*, cannot just use all of bobbyjunior since maryjunior
and joejunior would also get smaller amounts, that's the logic i'm
going for.

Sometimes it is easier to describe the entire problem, than trying to simplify it, so far as devising appropriate solutions is concerned.

However, the SEARCH worksheet function with appropriate use of wild cards may be what you are looking for.

For example:

=ISNUMBER(SEARCH("*junior",A1))

will return TRUE for any cell that ends with junior.