Excel IF Function: If Cell Contains Specific Text - Partial Match IF Formula

Подписаться 106 тыс.

Просмотров 907 тыс.

50% 1

In this video we use the IF function of Excel to test if a cell contains specific text.
Usually with the IF function you test a cell for a complete match, but in this example we need a partial match on text.
We then extend the scenario to handle a problem and then test for two different specific text strings.
Advanced Excel Formulas book (840 pages and more than 150 Excel functions)
amzn.to/3Rg87Go
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

Хобби

Опубликовано:

10 фев 2019

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 472

@Computergaga 10 месяцев назад

Advanced Excel Formulas book (840 pages and more than 150 Excel functions) amzn.to/3Rg87Go

@tonyvillarreal666 3 года назад

FINALLY!!! This is the IF function (partial match) I've been wondering about and looking for about a year! THANK YOU!

@Computergaga 3 года назад

Glad I could help, Tony 👍

@MarkBeckett-gq7lp Год назад

It's unreal how MS Excel has not created a simple" If (Contains ......)" function. Your tutorial really helped me through hours of figuring it out.

@Computergaga Год назад

Great! And yes, that is a surprise. But it would dent our fun 😂

@Miramu0 4 года назад

Thank you this is really helpful. Your way of explaining and teaching makes it easy!

@Computergaga 4 года назад

Thank you very much Mira.

@taikeh11 5 лет назад

Thank you for this video. Very clear and detailed. Very useful and time saving video. Again, thank you for taking the time to post this video.

@Computergaga 5 лет назад

You're very welcome. Thank you.

@humerahnaushadkhan 10 месяцев назад

Happy that yours was the first video that came up in my search. Liked they way you Zoomed in while typing the formula. Really thoughtful. Thank you so much.

@Computergaga 9 месяцев назад

Awesome, thank you!

@akashamale6277 3 года назад

Thank you so much. You really saved my time. I was looking for something like this. It's so useful for sorting from a large data.

@Computergaga 3 года назад

You're welcome, Akash. Happy to help.

@CMG321 3 года назад

That helped a lot, thanks - super glad you added the OR Function in the last part.

@CMG321 3 года назад

What if you want to add a third (or more) OR function - does that work?

@Computergaga 3 года назад

Sure. You can add as many conditions to OR as you require.

@jyotisanjay6067 4 года назад

Was struggling doing mannual changes everytime.. this saves a lot of time... thanks a lot

@Computergaga 4 года назад

You're welcome, Jyoti.

@andyg.3509 4 года назад

THANK YOU!! This is exactly what I was looking for. Not sure how I found it, but cheers to you sir.

@Computergaga 4 года назад

Wonderful! You're very welcome.

@gospelmoto2833 2 года назад

Wow! that was very clear. I learned something new today. I should have stumbled on this earlier. Thanks!

@Computergaga 2 года назад

Thank you 👍

@kashifbhatti7287 Год назад

Mann,almost got stuck for a whole week finding a short way to summarize this solution. But at last,something tickled in my mind,and relevant keywords took me to your video. Thanks a lot brother,you saved my almost,3-4 hours 😂❤

@Computergaga Год назад

You're welcome! Thank you for the comments, Kashif.

@bowiecreative 2 года назад

Great video. Do you have an example where if you have a text list that you want to add a specific value in a cell next the text if it is visible. For example everywhere I have the category “apples” I want to assign it the code 123 or if “oranges” is visible in a cell assign it “456”?

@mohideenthassim7180 5 лет назад

Wow! Alan, this is a great tip, many thanks for sharing a always. Best regards Mohideen

@Computergaga 5 лет назад

You're very welcome Mohideen.

@DamienDrake2389 Год назад

Great video, helped me out loads! I have a question on what you briefly touch on about how the formula checks for the data starting from x character. How do you make the search function only look at the first 2 characters? I would like the cell to display information based on 2 numbers found in the first 2 characters of the cell input, but I don't want an output from the same 2 desired numbers if they're found after the first 2 characters. If that makes sense.

@ukwonderboy Год назад

Thank you so much for this video, straight to the point and explained so easily!

@Computergaga Год назад

Thank you!

@vanityj1474 Год назад

Ahhhh!!! It worked for me 🎉🎉🎉 Just wanted to say Thank you so much for taking your time to teach. This video helped me immensely. Liked and subscribed ❤❤❤❤

@Computergaga Год назад

That's great! Thank you for taking the time to comment. I'm so glad that it helped.

@princess_k9164 4 года назад

Thank you!! Your video helped me after 8 hours on this assignment.

@Computergaga 4 года назад

Happy to help Princess K.

@davidhartley9381 4 года назад

Perfect! Exactly what i was looking for!!

@Computergaga 4 года назад

Great to hear David.

@CrysM66 3 года назад

Thank you! Thank you! I scoured the internet looking for something to help me today and you did!

@Computergaga 3 года назад

Great! Happy to help, Crystal.

@howyadoing9103 Год назад

Great tutorial. You have the gift of teaching!

@Computergaga Год назад

Thank you very much 😊

@cloverchips6913 2 года назад

Thank you, this was very easy to understand. It made my work easier.. Thank you for explaining it the simplest way possible

@Computergaga 2 года назад

You're very welcome. Thank you.

@arekie1234 4 года назад

Thank you so much to ease my master journey! May God bless you

@Computergaga 4 года назад

My pleasure Zack. Thank you.

@richardvaldez777 2 года назад

Thank you!!! This was very helpful and well explained. I appreciate it.

@Computergaga 2 года назад

You're very welcome, Richard.

@Enelya1228 3 года назад

Thank you so much for your video! The cell contains many words/values and I would like to perform a function, if contains *certainword*, then copy that word/value into another cell. Is this possible?

@SWAT4791 Год назад

Thanks for the tutorial; Excellent Explanation. Was the first tutorial I've encountered not full of Jargon that leaves my head spinning

@Computergaga Год назад

Great! Thank you.

@gwen1193 2 года назад

i just wanna let you know this video literally saved me! i was going to check and key in one by one...thank you so much!!!

@Computergaga 2 года назад

Glad I could help, Gwen.

@alejandrahurtado426 2 года назад

Gggwt

@alejandrahurtado426 2 года назад

Evans wnwsmwwwwbw Whwkw@@Computergaga gassbdjdcdd scwcsw

@wayneedmondson1065 5 лет назад

Hi Alan.. great tips on the use of FIND and SEARCH for partial match. Just because I've got my head in your VBA course, I attempted to write a User Defined Function to do the same using InStr(). See below. I tested it and it seems to work. The user can define the search text directly in the function argument with quotes or point to a cell reference to make it dynamic. Credit to you for your excellent VBA course. A few months ago, I wouldn't have even known where to begin writing a UDF, let alone being able to complete one without assistance.. so.. making progress. Thanks and thumbs up! Function Find_Partial_Match(CelRef As Range, Text As String) As String If InStr(CelRef, Text) = 0 Then Find_Partial_Match = "No Match" Else Find_Partial_Match = "Match Found" End If End Function

@Computergaga 5 лет назад

Awesome work Wayne.

@janturla2930 Год назад

Exactly what I was looking for!!! Thanks so much!

@Computergaga Год назад

You're very welcome, Jan. Great to hear.

@momoview 3 года назад

Thank you for this video. It helped me with my work. More videos please. Keep it up. 👍🏼

@Computergaga 3 года назад

You're welcome, Noel. Happy to hear that I was able to help.

@keerthanatirunagari6842 3 года назад

Thank you for uploading such elaborating Video on Excel word comparison. Helped me alot..

@Computergaga 3 года назад

You're welcome, Keerthana. You're welcome.

@bretirving3965 5 лет назад

You are so spot on and your videos are great.

@Computergaga 5 лет назад

Thank you Bret.

@glennlangford5897 5 лет назад

Excellent clear explanation, thanks Alan

@Computergaga 5 лет назад

You're very welcome Glenn.

@Pankaj-Verma- 4 года назад

Always more value than promise. Thanks for you kind help.

@Computergaga 4 года назад

My pleasure Pankaj. Thank you.

@ezhilvakes5420 4 года назад

Hi Alan, Thank you for the tutorial. I am trying to achieve in the formula to show the following: If one "CB2" is standalone to show "local", if "CB2 & "CB3" (combined) to show "local & far" and if "CB3" to show "far" How can I achieve this? TIA

@anishcavandegiessen1008 5 лет назад

Brilliant video - thank you!

@Computergaga 5 лет назад

You're welcome. Thank you Anishca.

@GJLNZ1 5 лет назад

brilliant. loved the cb2 / cb25 example.

@Computergaga 5 лет назад

Thank you.

@scottgaines2677 2 года назад

Thank you so much for the video. This definitely helps! Another tool for the Excel toolbelt :)

@Computergaga 2 года назад

You're welcome. That is great.

@josephselwan1652 2 года назад

Thank you! It added a ton value. Do you know if it is possible to have an if function that searches for a particular string only if it does not contain other particular strings? For example, an if function that finds "CB2", only if it does not contain, in your case, "1RB" ?

@Computergaga 2 года назад

We could probably do this with the AND function for multiple tests and wrap a NOT function around the "1RB".

@sasavienne 5 лет назад

Excellent.. 🌟 Thanks Alan. 👍 I liked the Trick with the space after CB2. 🌟 🌟..

@Computergaga 5 лет назад

Thank you Salim.

@georginahuertas4617 2 года назад

THANK YOU!!.. I was working in a search formula that did work but you help me adding up the ISNUMBER to it.. Now it works!!

@Computergaga 2 года назад

Excellent! You're welcome.

@usamamalik2325 2 года назад

Thanks for the tutorial! as in your example here can i merge the local address with commas using CB2 as a reference for locals in a single cell?

@ncujoemc Год назад

HI many thanks for sharing this, I was wondering if there's more concise approach for judging the contents if the criteria is way beyond CB2 or CB3, let's 50 specific texts or numbers? thank you and wish you all the best in 2023.

@Computergaga Год назад

You're welcome. It depends on the specific of what you require. But a look up table could be created with the entries i.e., CB2, CB3 etc. Then run a wildcard match using INDEX-MATCH or XLOOKUP on it.

@candid_paras2653 3 года назад

Amazing Mate....you solved my problem in few mins.... Thanks a bunch!

@Computergaga 3 года назад

Awesome!! You're welcome 👍

@centermassdefense9727 Год назад

Love the videos. I was hoping you could make a video or point me to an existing one. I have a list of 50 employees with employee number name etc then multiple items that are required etc. If all 5 requirements say yes can I then highlight the row? currently the individual cells turn green but I'd love the whole row or their name to turn green if they get a yes in all the required training cells. I hope this makes sense trying to make it as short as possible.

@davidtay18 5 лет назад

freakin awesome mate helped me out in my exam ye . much love

@Computergaga 5 лет назад

You're welcome David. Thank you.

@MrJordanSDean 2 года назад

This helped, thanks a lot. Is there a way to add a third argument in? Let's say you want the answers to be local, far and don't visit based off the postcodes. Is this something even possible?

@Spekialisti79 4 года назад

Super video! Saved me a ton of time 😁💪👍 Subbed.

@Computergaga 4 года назад

Awesome! Thank you.

@annvonbelow6012 2 года назад

Beautifully explained!

@Computergaga 2 года назад

Thank you 😊

@semperdiscendum7439 5 лет назад

Awesome!! Thank you so much! 👍👍

@Computergaga 5 лет назад

You're very welcome Shashikant.

@ramashryupadhyay3038 2 года назад

Thank you so much for making this video. You have save a lot of my time. I think i am going to take some time and watch all of your video to lern excel.

@Computergaga 2 года назад

You're welcome Ramashry. I hope I can help more in further videos.

@KathleenNewman Год назад

Thank you thank you thank you! I knew there had to be a way to do this. Used this method for IFS, and added a final clause for TRUE,"default" if all the previous tests were false.

@Computergaga Год назад

Excellent! No problem.

@rayfi790k 4 года назад

very helpful, much appreciated.

@Computergaga 4 года назад

My pleasure Rered. Thank you.

@velavanselvamani3387 5 лет назад

Very much useful.. thanks to you and your team..

@Computergaga 5 лет назад

You're very welcome Velavan. My pleasure.

@user-mx2pv8bb9m 4 месяца назад

Excellently explained. So good.

@Computergaga 4 месяца назад

Thank you very much!

@MInamiKojima379 2 года назад

Really useful tool. Thank you a lot!

@Computergaga 2 года назад

You're welcome Uriel.

@runderlusttours Год назад

Brilliant!!! Thank you so much! This helped me a great lot for my bookkeeping. Would love to know if we can set multiple conditions to expand on this: eg: if the adjacent cell contains "x" then the result is "1" and if the adjacent cell contains "y" then the result is "2" and so on.

@Computergaga Год назад

Yes absolutely. One method is to create a lookup table with the a list as you have entered in this comment. And then use a lookup function wildcards to return the appropriate result. Here is a video that shows wildcard characters with VLOOKUP for a partial match ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GJeSfXySByk.html

@nerdypizza3858 Год назад

Fantastic tutorial! Thank you sir!

@Computergaga Год назад

You're very welcome! Thank you.

@anandsharma4627 5 лет назад

Wow.....great sir....I use flash fill but flash fill doesn't always help....this one is very helpful. Thank you so much for the video

@Computergaga 5 лет назад

You're very welcome Anand. Yes Flash Fill is incredible.

@Dravidan1971 4 года назад

I want to know how to search the data in a different range of columns with if function? For example if A1 = any one cell in the range from d1:d5 then if true “Yes” if false “No”. Is this possible?

@gulagulasusu 2 года назад

Thanks mate. was looking for this.

@Computergaga 2 года назад

No problem 👍

@safiyam3241 6 месяцев назад

Thank you! Have been looking for this! :)

@Computergaga 6 месяцев назад

No problem!

@rokgaber 3 года назад

Wou, you just saved my day :) I would love if I could just copy paste formula from video description to the Excel. Haha, superlazy :D Subscribing for sure! Good luck.

@Computergaga 3 года назад

Thank you, Rok 👍

@emilio6117 4 года назад

AMAZING!! Thank you

@Computergaga 4 года назад

You're welcome Emilio, thank you.

@Wajih26 2 года назад

Thank you, this helped me for a work task. Appreciate it, liked and subscribed!!

@Computergaga 2 года назад

Glad it helped! Thank you.

@Mr.Handymaan 10 месяцев назад

Awesome! I would love to know if you know how to do a command to IF A1 contains a text like "X" THEN do a math between two cells like B3-D3. Cheers! :)

@wemghon1 3 года назад

Hi Computergaga, Thanks for your wonderful training. In your above training, how how you highlight the row that contain CB2?

@excel_ladz Год назад

Enter the formula outlined in the video into conditional formatting (found in the home tab).

@Computergaga Год назад

Select the range, use Conditional Formatting and the use a formula rule. Use the same formula shown in the video, but no need for the IF function. Just the ISERROR and SEARCH part. Ensure to put a $ before the column.

@geraldrobinson1684 4 года назад

Would there be a formula to identify the postcode and copy it to the next column for each address? ie] cell 2B would have CB5, cell 3B would have CB3, etc?

@Never3ver4get 3 года назад

I love your logical analysis..!

@Computergaga 3 года назад

Thank you 😊

@kamaur01 Год назад

This was helpful, thank you

@Computergaga Год назад

Great! No problem.

@moi984 3 года назад

really nice trick with the spacing!!

@Computergaga 3 года назад

Thank you, Mo.

@214Wildbill 5 лет назад

Thats really great!!, i use something like tjis all the time, but, i use the countif function, using another cell to match with, and space like you do, and i use "*" cell "*" i use it to find clues in what i must look for next. I like the way you teach, your very fun

@Computergaga 5 лет назад

Thank you William. Yes, a great a use of wildcards with COUNTIF for partial text matches.

@faizanahmed086 5 лет назад

@@Computergaga please make a video of this trick too, using wild card for search

@Computergaga 5 лет назад

Got one here my friend - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-TlyMhnsWioQ.html

@abrilcb5749 5 лет назад

Hi Alan!! thank you so much!! this helped me alot to save time

@Computergaga 5 лет назад

Great to hear Abril. You're very welcome.

@Balsero-qb9gv 2 года назад

Thank you very much for this video!! I have a question please, how can I separate the names if they have a minus symbol (-) in between? Example: John-Smith Thank you so much!!

@Computergaga 2 года назад

You could use Text to Columns or the Flash Fill tool.

@oyetolaemmanuel-ebikake9658 2 года назад

REAL Time Saver - Thank you

@Computergaga 2 года назад

You're welcome. Thank you.

@JamesDelaCruz 2 года назад

Thanks for the help!

@Computergaga 2 года назад

You're welcome, James.

@hathienantruong4148 3 года назад

Cheers mate this helps

@brightside01 4 года назад

Thank you so much it is great way , really thank you you save my time

@Computergaga 4 года назад

My pleasure Abeer. Thank you.

@andriikulishov3371 4 года назад

brilliant! thanks!

@Computergaga 4 года назад

You're welcome. Thank you, Andrii

@write2swapnil41 3 года назад

Excellent!!!

@Computergaga 3 года назад

Thank you 😊

@zerstorungdeslichts4706 8 месяцев назад

This is the formula that I'm looking for. Thanks

@Computergaga 7 месяцев назад

Awesome! Glad I could help.

@samipiyash9322 5 лет назад

Great Man!

@Computergaga 5 лет назад

Thank you Sami.

@rogeliojayarflores7511 2 года назад

Thank youuuuu

@Computergaga Год назад

My pleasure 👍

@somnathgupta2048 Год назад

It's really very helpful for use thank you very much

@Computergaga Год назад

You're very welcome.

@iComment87 2 года назад

Great video.

@Computergaga 2 года назад

Thank you.

@Dierry 3 года назад

Hi. I have a question. Can we use "IF" to compare for example "A2=B2" but these 2 cells with text? If so, how can we do it?

@tanmaykorgaonkar963 Год назад

One of the best videos in excel

@Computergaga Год назад

Thank you very much 😊

@tdhevents9206 5 лет назад

THANK ALAN !!!!! SAVED ME SOOO MUCH TIME ITS CRAZY!! Questions: Is there a limit on how many words it can search for in the formula?? lets say i wanted CB2-CB50 and everything in between??

@Computergaga 4 года назад

For something like this, we could probably combine it with a lookup table or better of using Power Query.

@Fehintiti_ 2 года назад

This video literally saved me!! Thank you!

@Computergaga 2 года назад

You're welcome! Great to hear.

@samathajaddu5992 4 года назад

Thanks a lot and is very useful and I've subscribed.

@Computergaga 4 года назад

Awesome! Happy to help Samatha.

@plamenyankov8476 2 года назад

Thaaanks!

@Computergaga 2 года назад

You're welcome, Plamen. Thank you.

@aaroncade1136 Год назад

You're a lifesaver!

@Computergaga Год назад

Awesome!

@BradPowers-PitPacat 3 месяца назад

Very good. Thanks!

@Computergaga 3 месяца назад

My pleasure. Thank you.

@jaswantdn7 4 года назад

Thank you so much sir..

@Computergaga 4 года назад

You're very welcome. Thank you Jaswant.

@RajKumar-ei3wi Год назад

Great video ,👌

@Computergaga Год назад

Thank you 👍

@deepeshsundar1574 4 года назад

what if i need more results than Local & far ? can u show how to do it ? nested IF

@mrbigone01 3 года назад

Hi! congratulations for the channel, very useful !. I have a question: is there a formula that tells me that the cell of numbers with a central space "10 30 50" is included in the number cell "5 10 30 40 50" and that returns me as a result the cell "5 10 30 40 50 "? can you help me? it's important please.

@SalerPaul Год назад

THANK YOU!!

@Computergaga Год назад

You're welcome!

@muhammadrehman6418 3 года назад

Hi, Thanks for your reply and solution. this formula is excellent for limited statements, but if statements get lengthly then MS Excel will prompt error of 64 nested statements (something like this). Is there any way to do the same formula but with a shorter version. Thanks again.

@muhammadrehman6418 3 года назад

Please take a look at this formula. Only problem that needs solution is "else condition" does not apply which is a text "gene". If nothing is found then formula should return text "gene" but is is showing a blank cell. =IF(OR(ISNUMBER(SEARCH({"nurse","doctor","hospital","patient","covid","radiolo","gynae","ortho","E.N.T","eye","medic","pharma","derma","surgeo","anesth","patholo","peadi","psych","orthop","cardio","chest","nephro","urolog"},B7))),"heal",IF(OR(ISNUMBER(SEARCH({"professor","teach","educat","traine","trainn","lectur","coach"},B7))),"educ",IF(OR(ISNUMBER(SEARCH({"motel","hotel","restaur","food","hospitality","cook","chef","wait"},B7))),"hote",IF(OR(ISNUMBER(SEARCH({"custome","client","recepti","desk","call center"},B7))),"cust",IF(OR(ISNUMBER(SEARCH({"sale","marke","brand"},B7))),"mark",IF(OR(ISNUMBER(SEARCH({"digital","social","seo"},B7))),"digi",IF(OR(ISNUMBER(SEARCH({"construc"},B7))),"cons",IF(OR(ISNUMBER(SEARCH({"account","finance","recover","audit","cash","revenu","billi"},B7))),"acco",IF(OR(ISNUMBER(SEARCH({"telecom"},B7))),"tele",IF(OR(ISNUMBER(SEARCH({"automo"},B7))),"auto",IF(OR(ISNUMBER(SEARCH({"logistic","transpor"},B7))),"logi",IF(OR(ISNUMBER(SEARCH({"teach","professor","instruc","traine","trainn","coach","lectu"},B7))),"edu",IF(OR(ISNUMBER(SEARCH({"IT ","developer","software","web","fronte","backe","database"},B7))),"it j",IF(OR(ISNUMBER(SEARCH({"secre","persoanl"},B7))),"secr",IF(OR(ISNUMBER(SEARCH({"enginee","archite","civil","mecha","electrica"},B7))),"engi",IF(OR(ISNUMBER(SEARCH({"monitori","evaluation","M&E"},B7))),"moni",IF(OR(ISNUMBER(SEARCH({"technicia"},B7))),"tech",IF(OR(ISNUMBER(SEARCH({"admini"},B7))),"admi",IF(OR(ISNUMBER(SEARCH({"operation"},B7))),"opera",IF(OR(ISNUMBER(SEARCH({"coord","commu"},B7))),"coor",IF(OR(ISNUMBER(SEARCH({"cler"},B7))),"cler",IF(OR(ISNUMBER(SEARCH({"public"},B7))),"publ",IF(OR(ISNUMBER(SEARCH({"project"},B7))),"proj",IF(OR(ISNUMBER(SEARCH({"operator"},B7))),"oper",IF(OR(ISNUMBER(SEARCH({"plan"},B7))),"plan",IF(OR(ISNUMBER(SEARCH({"HR ","human","recruit","talent","payroll"},B7))),"huma",IF(OR(ISNUMBER(SEARCH({"supply"},B7))),"supp",IF(OR(ISNUMBER(SEARCH({"producti","manufact"},B7))),"prod",IF(OR(ISNUMBER(SEARCH({"design","animator","editor"},B7))),"desi",IF(OR(ISNUMBER(SEARCH({"write","content","copy","book"},B7))),"writ",IF(OR(ISNUMBER(SEARCH({"business"},B7))),"busi",IF(OR(ISNUMBER(SEARCH({"legal","compliance"},B7))),"lega",IF(OR(ISNUMBER(SEARCH({"research","analyst"},B7))),"rese",IF(OR(ISNUMBER(SEARCH({"airport","crew","pilot","hostess"},B7))),"pilo",IF(OR(ISNUMBER(SEARCH({"fire","rescue"},B7))),"fire",IF(OR(ISNUMBER(SEARCH({"securit"},B7))),"secu",IF(OR(ISNUMBER(SEARCH({"law","police","army","navy","airforce"},B7))),"law",IF(OR(ISNUMBER(SEARCH({"labo","worker","staff"},B7))),"labo",IF(OR(ISNUMBER(SEARCH({"procure","purchase"},B7))),"proc",IF(OR(ISNUMBER(SEARCH({"import","export"},B7))),"impo",IF(OR(ISNUMBER(SEARCH({"agri","horti","enviro","forest","crop"},B7))),"agri",IF(OR(ISNUMBER(SEARCH({"wareho","stock","store"},B7))),"stoc",IF(OR(ISNUMBER(SEARCH({"","","","","","",""},B7))),"",IF(OR(ISNUMBER(SEARCH({"advis","consul"},B7))),"advi",IF(OR(ISNUMBER(SEARCH({"bank","insurance"},B7))),"bank","gene")))))))))))))))))))))))))))))))))))))))))))))