Excel If formula - If CONTAINS formula in Excel - 2 Minute Excel Formula

[@ghern9953]

Wow, this was EXACTLY what I was looking for!!!!!! The various excel boards need this posted !

[@datauntelligence]

Great, thanks for the comment. Really good to hear. Being a Excel user myself, I try and make videos the way I like to learn... A quick explanation, and straight to the point. Not too much fluff.

[@gmdthinkalikepodcast]

Finally! Straight to the point. Thank you

[@datauntelligence]

No worries. The 2 minute formula series aims to teach without dragging out videos longer than needed.

[@gmdthinkalikepodcast]

@@datauntelligence smart 👍🏽

[@divinag.2803]

Hello, I am trying to get a name from column b, that matches the day of the week which is on a different cell, what formula should I be using?

[@datauntelligence]

Hi there, maybe a filter function my work for you. You can filter a big list of data and extract only those cells that match your reference cell. kzbin.info/www/bejne/e4mpYpitqNd6nLc

[@alirehman4041]

Exactly what I was looking for

[@datauntelligence]

Nice. I'm glad you found my video.

[@georgepapoulidis9728]

Hi this was very helpful, I have done something similar using your sample above for employees who iether get a lift to work or drive in with. I know stuck on how I can add the totals, as it stands I can not AutoSum the contains Colum. do you have any hints

[@miniverma2768]

How can we get the same type of result for more than one criteria like, Mints, bananas & Blueberry etc.

[@JESJACKDAD]

Worked great for me, thank you

[@datauntelligence]

Excellent, glad to hear. Thanks for the comment

[@ayacleopa5467]

Interesting! I have a list of fruits and vegetables. I want if the item listed is a vegetable, a it should show vegetable in the next column or else it should show fruit. E.g, if I have lemon or ovacado or mango, the next column should classify it as a fruit. How do I go about this? Kindly.

[@datauntelligence]

Hi there, as Excel won't know what you will need to return, you might need to make a 'mapping/lookup' table to reference. This video on xlookups might give you some ideas. kzbin.info/www/bejne/an3PoGSDp9aGhc0

[@cubeiq]

What if instead of "" creating blank. I want it to apply no changes at all? Thnx in advance

[@datauntelligence]

Hi there, thanks for watching. To answer your question... instead of entering " " in the formula, just reference the original cell (for eg. B2). Therefore, if the formula comes up with an error, just show the original cell value. Hope that helps

[@cubeiq]

Thank you for your fast reply. Helped me out a lot.

[@rubinikrishnan4682]

Hi , I need help . if my column B contains value of column A i want to display the value it has in column A

[@datauntelligence]

Hi there, thanks for watching. In my formula, just replace where i have "mints" at the end of the formula, to the cell reference that you want to bring back. For example A3.

[@rabdu2002]

Hi, how we can deal with more than criteria. spouse that I am searching for a specific word & a value in another cell that should be >=0 & the result will be 0 if the word not there & the value for the other cell is >=0 otherwise the value will be another result. how we can do? thanks a lot & waiting for your reply.

[@san00732-cg3dc]

I found it very helpful thanks

[@datauntelligence]

Excellent, glad to hear.

[@sana1m]

Hey, If I have column as By MCX Bill for 13-05-2024 By MCX Bill for 14-05-2024 FO BILL FOR FO BILL FOR By MCX Bill for 15-05-2024 FO BILL FOR and I want to extract either FO or MCX, can you help me with the correct formula? It keeps extracting FO from the word"FOR". :(

[@Mansijswarnkar]

Nice, however what if we have to search for a word from a list to check all if it all anyone exists in the cell. Thanks

[@datauntelligence]

Hey, not sure if it will help, but the filter function can help when searching through lists with certain criteria. kzbin.info/www/bejne/e4mpYpitqNd6nLc

[@dianemartin2908]

I need to know if a cells value is on a specific list. (named list)

[@datauntelligence]

In this instance, you could possibly use a variation of the XLOOKUP function... My favourite function!

[@dianemartin2908]

@@datauntelligence Thank you so much! We were able to make the xlookup work.

[@RekhaDevi-rc3sn]

What to do when suppose if condition is true than we want to multiply some value and if false than want to multiply by other digits

[@datauntelligence]

Good question. In this case, replace "mints" with the true outcome you want... for example A2*15. Again for the result of false, place the formula in the space instead of "".

[@emmanuelryanrogers-bean4490]

what if instead of blank I wanted it to repeat the value in the cell?

[@datauntelligence]

Hi. Yes, in the formula you can change it to return anything you want. Just replace the double quotation marks "" with what you want to put instead.. for example, "mints not found". Otherwise you can simply reference another cell instead, by replacing the "" with B5 for example.

[@vespa2901]

Perfect, thanks alot!

[@datauntelligence]

Great! Glad it helped.

[@jarlelidal532]

Hi, thanks for the video. It so happens that I need to find the "Mints" in several cells. Tried with this formula but got an error:: =IF(ERERROR(SEARCH(A3;'Sheet1'!I2:L2));"";"YES") How do I solve this? Thank you

[@rebeccacoleman8293]

Can I do the same with data on in a list?

[@datauntelligence]

Hey there, i'm not 100% sure of your details, but I think you should be able to.

[@sweetybalhara6679]

life saver!!🙏🙏

[@datauntelligence]

Ha, thanks. Glad it was able to help.

[@dcbrucelee]

When I sum(A1:G1), I get the total whether the cell input in number or text , sum(A1+C1+E1) and sum(B1+D1+F1), I get 2 different total which is good. But when I enter text into that row, the total now display value. What function should I use? Thank you.

[@datauntelligence]

Hi, Instead of using +, try using a comma. For example sum(A1,C1,E1) should give you a result wheather or not there is text in one of the cells.

[@dcbrucelee]

@@datauntelligence Thank you. It works! I am a beginner:)

[@shivamharwalkar6815]

What if Mints in other column is there any formula for this

[@TheLooking4sunset]

Great, thank you

[@datauntelligence]

Thanks. I appreciate the comment.

[@Zerkclash]

Is there a way to instead of searching for a specific word, search for any value. (I have the row in which its searching under the accounting format, and i want the formula to bring back blank if there is no input in that cell, but to work if there is ANY value in that cell) i hope that makes sense. Thanks!

[@datauntelligence]

Hi there, in your case I'd probably use a counta function. If you are searching in cell A1... =if(counta(a1)>0,a1,""). This will bring back the Value in cell A1 or nothing.

[@Zerkclash]

@@datauntelligence this worked! Thank you.

[@komplitrandom]

If I have a datasheet that contains a character in it, say, latter A, can the formular, identify cells with that letter and return "This is the error"?

[@datauntelligence]

Hi, I think this formula should work for you. Instead of typing "mints", type "A". And At the end of the formula, replace the word "mints" and type "this is the error".

[@mr.bipstv9264]

Can you have more than 1 true value word in the formula

[@datauntelligence]

Hey there. It is a bit tricky, but you could 'nest' another (if) formula in the formula on the video.

[@kellyrussell4515]

Trying to pull a value from a column on one worksheet that contains two different plan types (for employee benefits) to another worksheet. Example: Column Header = Plan, Text values in that column = Medical or Dental. Wanting the formula to pull in one value if it's Medical and another value if it's Dental to the other worksheet.

[@jared1432]

How hard is it to expand the search options to include multiple words? For instance I want to change any column that contains mints and or another word, and the. Produce a like column for both? Hopefully this makes sense 😂

[@datauntelligence]

Hey, it's a bit of a tougher formula, but I would use a nested formula to get the result. The more OR's and AND's would require longer formula. In the examples below, I am searching "choc" and "mints", and looking in cell B4... In each formula, you can replace the search words, or reference a cell instead of "choc" or "mints" contains(choc AND mints) = IF(ISERROR(IF(AND(SEARCH("mints",B4)>0,SEARCH("choc",B4)>0),0,0)),"","choc & mints") contains(choc OR mints) = IF(ISERROR(SEARCH("mints",B4)),IF(ISERROR(SEARCH("choc",B4)),"","choc only"),IF(ISERROR(SEARCH("choc",B4)),"mints only","choc & mints"))

[@iitomo0807]

Thank you!

[@장상현-e6c]

유튜브에서라도 목소리 들은게 좋네 단서방

[@scienceandtechnology9228]

جميل جداً

[@datauntelligence]

Thanks for the comment

[@grantrizmo2002cb]

Total clickbait...video is 2 mins 15 seconds...😊

[@datauntelligence]

Haha, You got me!

[@MevludinShah]

Thank you!

[@datauntelligence]

No worries :)

[@JLvatron]

Thank you!

[@datauntelligence]

No problem. Glad I could help. Thanks for the comment