Dear all,
I hope you can help me with this. I want to create a column where I get a 1(yes) or 2(no) depending on whether cells C38:K38 have any of these words " alamycin" " aurofac", "chloromed"...
I've tried this formula but it doesn't work
=IF($C38:$K38= OR("ALAMYCIN","AUROFAC","CHLOROMED","CTC"),1,2)
I would appreciate your help!
Many thnaks
Last edited by ANAVPL; 04-18-2019 at 11:32 AM.
Formula:
Please Login or Register to view this content.
Assuming the values in C38:K38 might be part of longer strings of text, put * in front of and behind each word you’re searching for. Like so:
Formula:Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thank you so much for this! It works perfectly
This is more compact:
=IF(SUM(COUNTIF(C$3:K$3,{"ALAMYCIN","AUROFAC","CHLOROMED","CTC")}))>0,1,2)
Please mark the thread as solved.
Last edited by AliGW; 04-19-2019 at 05:57 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
One more question on these if you don't mind:
would it be possible to get, instead of just 1 for finding any of those words, to get the actual count of those words in C3:K3?
Thanks!
Of course! Use this bit of the formula:
=SUM(COUNTIF(C$3:K$3,{"ALAMYCIN","AUROFAC","CHLOROMED","CTC"}))
Thank you!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks