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

Giganews Newsgroups
Subject: Isolating part of text to use in an IF/AND FUNCTION
Posted by:  Smacky (smackyjac…@gmail.com)
Date: Thu, 30 Dec 2010

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.

Replies