conditional formula with partial word match

  • Thread starter Thread starter Audrey G.
  • Start date Start date
A

Audrey G.

Have a spreadsheet with following data:

Emp Name Title Bonus
John A Div Dir I
Jane B Div Dir IV
Mark C Tech
Mary D Admin

I want to create a conditional formula that under Bonus that looks at the
title, and for every position that contains "Dir" will be 0, while other
titles will be $125. Currently, my formula looks like this =IF(I94="Div Dir
I*"),0,125). However the result is coming out as $125. I need to know how
to adjust the formula to use a partial text word match.

Thank you,
Audrey G.
 
Use SEARCH or FIND, eg:

=IF(ISNUMBER(SEARCH("Dir",I94)),0,125)

FIND is case sensitive.

Hope this helps.

Pete
 
This works. Now, a little deeper...how can I add to this formula to look for
other titles that also should have 0 as the result, like the Div Dir title?
In other words, I think I need to add an "AND" statement to this in order to
encompass the other titles that should produce a 0 result. How would I
incorporate that, using another title like "Proj Mgr"?

Thanks!
 
I found that "Search" works better for my application. Thank you for taking
the time to respond!
 
Thank you for taking the time to respond. Please see my reply to Jacob for
some additional assitance I am needing.
 
You can extend your search like this:

=IF(ISNUMBER(SEARCH("Dir",I94)),0,IF(ISNUMBER(SEARCH("Proj Mgr",
0,125))

Hope this helps.

Pete
 
Thank you so much! You have saved me HOURS of work on a very LARGE set of
data!! Have a great day!
 
Back
Top