conditional formula with partial word match

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.
 
P

Pete_UK

Use SEARCH or FIND, eg:

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

FIND is case sensitive.

Hope this helps.

Pete
 
A

Audrey G.

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!
 
A

Audrey G.

I found that "Search" works better for my application. Thank you for taking
the time to respond!
 
A

Audrey G.

Thank you for taking the time to respond. Please see my reply to Jacob for
some additional assitance I am needing.
 
P

Pete_UK

You can extend your search like this:

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

Hope this helps.

Pete
 
A

Audrey G.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top