| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formula with partial word match
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
=IF(ISERROR(SEARCH("Div Dir",I94)),125,0)
If this post helps click Yes --------------- Jacob Skaria "Audrey G." wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
=IF(ISERROR(FIND("Dir",I94)),125,0)
"Audrey G." wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
Use SEARCH or FIND, eg:
=IF(ISNUMBER(SEARCH("Dir",I94)),0,125) FIND is case sensitive. Hope this helps. Pete On Aug 13, 4:45*pm, Audrey G. wrote: 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
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! -- Audrey G. "Jacob Skaria" wrote: =IF(ISERROR(SEARCH("Div Dir",I94)),125,0) If this post helps click Yes --------------- Jacob Skaria "Audrey G." wrote: 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
I found that "Search" works better for my application. Thank you for taking
the time to respond! -- Audrey G. "Sean Timmons" wrote: =IF(ISERROR(FIND("Dir",I94)),125,0) "Audrey G." wrote: 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
Thank you for taking the time to respond. Please see my reply to Jacob for
some additional assitance I am needing. -- Audrey G. "Pete_UK" wrote: Use SEARCH or FIND, eg: =IF(ISNUMBER(SEARCH("Dir",I94)),0,125) FIND is case sensitive. Hope this helps. Pete On Aug 13, 4:45 pm, Audrey G. wrote: 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
You can extend your search like this:
=IF(ISNUMBER(SEARCH("Dir",I94)),0,IF(ISNUMBER(SEAR CH("Proj Mgr", 0,125)) Hope this helps. Pete On Aug 13, 5:57*pm, Audrey G. wrote: Thank you for taking the time to respond. *Please see my reply to Jacob for some additional assitance I am needing. -- Audrey G. "Pete_UK" wrote: Use SEARCH or FIND, eg: =IF(ISNUMBER(SEARCH("Dir",I94)),0,125) FIND is case sensitive. Hope this helps. Pete On Aug 13, 4:45 pm, Audrey G. wrote: 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.- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
Thank you so much! You have saved me HOURS of work on a very LARGE set of
data!! Have a great day! -- Audrey G. "Pete_UK" wrote: You can extend your search like this: =IF(ISNUMBER(SEARCH("Dir",I94)),0,IF(ISNUMBER(SEAR CH("Proj Mgr", 0,125)) Hope this helps. Pete On Aug 13, 5:57 pm, Audrey G. wrote: Thank you for taking the time to respond. Please see my reply to Jacob for some additional assitance I am needing. -- Audrey G. "Pete_UK" wrote: Use SEARCH or FIND, eg: =IF(ISNUMBER(SEARCH("Dir",I94)),0,125) FIND is case sensitive. Hope this helps. Pete On Aug 13, 4:45 pm, Audrey G. wrote: 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.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula with partial word match
You're welcome, Audrey - thanks for feeding back.
Pete On Aug 13, 10:59*pm, Audrey G. wrote: Thank you so much! *You have saved me HOURS of work on a very LARGE set of data!! * *Have a great day! -- Audrey G. "Pete_UK" wrote: You can extend your search like this: =IF(ISNUMBER(SEARCH("Dir",I94)),0,IF(ISNUMBER(SEAR CH("Proj Mgr", 0,125)) Hope this helps. Pete On Aug 13, 5:57 pm, Audrey G. wrote: Thank you for taking the time to respond. *Please see my reply to Jacob for some additional assitance I am needing. -- Audrey G. "Pete_UK" wrote: Use SEARCH or FIND, eg: =IF(ISNUMBER(SEARCH("Dir",I94)),0,125) FIND is case sensitive. Hope this helps. Pete On Aug 13, 4:45 pm, Audrey G. wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If Formula When Contains Partial Word | Excel Worksheet Functions | |||
| Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
| partial word match for conditional formatting | Excel Worksheet Functions | |||
| DSUM Partial Match | Excel Worksheet Functions | |||
| partial lookup/match | Excel Worksheet Functions | |||
Linear Mode
