Excel IF Function with PARTIAL Text Match (IF with Wildcards)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/if-wildcard-file

[@jays9591]

I had to check 7,000 entries of comments (long'ish text) from customers, we were interested in finding a particular word in the comments. Your tutorial here just saved me days of boring 'eye-balling' work, Wonderful! Thank you. BTW, I prefer your style of teaching than many others - it's always clear, no rambling and intuitive. Many thanks again.

[@BrianMegilligan]

I appreciate that you take us through the steps of things that won't work and explain why. This makes it easy to follow and is useful for cases where those functions that don't work in this case might be useful for something else we encounter in the future. Thank you for your work on this!

[@LeilaGharani]

You're very welcome Brian. I'm really glad if I can provide some useful alternatives.

[@andrew3amb]

@@LeilaGharani THANKS! But there is other variant how to say "contains" : =IF(COUNTIF(A5;"*at*"); ... ; ... ) - BEST CHOICE FOR THIS TYPE OF TASKS!

[@jdpalm1981]

Holy crap!!! That was way more informative than searching through stackoverflow!

[@mach489i]

This does the job as well: =IF(COUNTIF(A5,"*AT*")>0,"AT","")

[@Lunatyk001]

This one is amazing, because it's works also with "?" wildcard. Thank you for this! :)

[@sachinrv1]

This is Good one 👍

[@LeilaGharani]

I LIKE this! Thank you for sharing : )

[@luisparra2740]

Nice. It works like this as well: =IF(COUNTIF(A5,"*AT*"),"AT","")

[@merbouni]

Sincerely, the method of leila is better than your method...!! with your formula, you can not search for a number in a series of digits, unless the cell contains a combination of numbers and text

[@sZenji]

i was looking over 4 weeks for this formula in the internet and suddenly youtuve recomends me this video and you gave me the solution i finnaly needed thank you so much

[@LeilaGharani]

Great, I'm glad I was able to help with that Sven!

[@MeliPatoco]

That awesome moment you come to see a Leila video because the title tells you this is going to be useful, and it is a formula that you use almost daily at your job. Feels amazing

[@LeilaGharani]

Happy to hear that! Thank you for your kind comment Meli.

[@francodoshe6408]

This is my online lecturer of all time...🙏 Be blessed teacher.

[@abubakarshehuabubakar1082]

WOW! knowing this channel is like seeing water in the mids of the hot desert. Thank you, Laila. Much love!

[@LeilaGharani]

Thank you very much for the kind words Abubakar! I'm glad you like the videos.

[@664196wassouf]

You have saved my life in excel so many times. Thank you

[@peteringvorsen890]

Thanks Leila😊 Once again, you show a learning video that's easy to understand for everyone👍

[@LeilaGharani]

You're very welcome Peter! Glad it comes across like that.

[@alexkim7270]

When I saw this video few months ago, I didn't remember much of it. Until I had to do something similar in DAX. Now when I check your video out again, I just realized whatever I watched had entered my subconscious! Yay. Keep posting these great contents, Leila.

[@LeilaGharani]

Oh wow, I didn't know my videos had such an effect :) Glad you find the tutorials helpful Alex.

[@Siddharth-pw8mz]

@@LeilaGharani ma'am I'm from India and you

[@prashant333kumar]

Leila you are my best excel teacher that i have ever met

[@LeilaGharani]

Wow, thank you!

[@rajasekharreddy2331]

Wow......I am very thankful to you Leila........I have applied the same to my worksheet and it really works........your tutorials save lot of man hours........thanks once again.........

[@natraj5689]

Hey Leila have been following you regularly, your online classes made me look like a king before my colleagues at office, thanks a lot!

[@AnuroopBangalore]

I had to display list of values based on partial match of text in the previous column cells. This helped me resolve an issue which I was struggling for days. You are awesome. Thanks ! For anyone who is new in Excel, this channel is good info here.

[@z058261]

I really get the excel formula thing here. Straight to the point not complicated and I understand it to express it in my own words. I have been to the the other guru sites as known - but here I have more satisfaction in learning, I should have come here first!

[@LeilaGharani]

Thank you for the kind feedback. I'm glad you like the style of the videos.

[@TheVampireviolet]

I have been looking for something like this for probably over an hour, and you managed to solve my problem within the first 11 seconds of this video. I literally cannot thank you enough TuT

[@LeilaGharani]

Glad I could help

[@brianfields7519]

I find your explanations and applications to be the best. Even when I delve into some obscure situations you have answers that work. Thank you so much for your postings.

[@WaqqasAzeem]

Thank you... I searched everywhere but no solution... You're a life saver.

[@LeilaGharani]

Glad to help :)

[@LuisFernando-yd3mx]

Hey Leila. I've used the isnumber function for this too but I think you can also do this with the If function along with a match since the match allows for the asterisks wildcards. Then if the match works you can use the if conditionals.

[@taymaateyah1757]

You saved my life.... THANK YOU!! I was looking for a formula like this for soooo long.

[@beardpower8670]

I thought I was going crazy, and didn't understand wild cards in Excel. Thank you for the help.👍

[@hanifsanatkhani4587]

Every time I stuck why is it always your videos that is most useful? :)) thanks soo much.

[@SteveGoebler]

This is something I've been trying to work out for some time now. Thank you!!!

[@cajatinaswani609]

Every time I learn something new on this channel. Great work Leila 👍

[@michaelcarman5521]

Is there a way to use {…,…} in place of typing out the search function twice? The only thing that is really different is the criteria.

[@satyenpurohit3947]

I used search with wildcards and its results are magic... Good to see the same in your video...

[@LeilaGharani]

Agreed, it can really be helpful sometimes.

[@satyenpurohit3947]

@@LeilaGharani i used it creating reminders, will share with you once its done.

[@LeilaGharani]

Great! Looking forward to it.

[@age_of_reason]

This was exactly what I was looking for and I was able to write my formula with IF + wildcards and it solved my problem. Thank you!

[@rudi-gruber]

Never used wildcards before in a formula. Nice video again. Thank you

[@LeilaGharani]

Can be quite helpful sometimes. Glad you like it Rudolf.

[@georgewhite3379]

I freaking love you!! I consider myself excel literate but sometimes I struggle trying to make an argument work. So its like 'Hey, I wonder if Leila knows.' BAM! There it is. I am subscribing to everything you do. :)

[@amarnath_85]

Mam I had solved many Excel problems with your Excel tutoring, I had started to learn Excel formula with your Excel tutoring , Thank You for that Now I had a problem that I can not use the index and match function Merged cell please give me a solution to this Mam at first I was entertaining to the company I don't know anything about Excel Formulas, I had started learning Excel Formulas with your Excel tutoring and then I was shining in my company Thank You for that Mam I am expecting you may give me a very good solution to this problem Thank you Mam

[@wayneedmondson1065]

Hi Leila.. cool solution. First I used IF with SEARCH and then wrapped the IF with IFERROR which trapped for #VALUE! when no match was found by SEARCH. Your method using ISNUMBER is more logical and I prefer it. Thanks for the insights on how to do partial match with IF using ISNUMBER, SEARCH and OR.. great stuff! Thumbs up!! PS - I also like the COUNTIF solution below by Alpha State and others.. always nice to see multiple ways to solve the problem.

[@LeilaGharani]

Exactly, that's why I really appreciate comments :) Thanks for the thumbs up!

[@terrymadeley]

Very helpful, but so too was that little reminder about filling without formatting at 4:44 - I hadn't realised you could do that. So thanks twice!

[@LeilaGharani]

You're very welcome Terry. I'm glad it was useful twice :)

[@jervahnmorgan]

You do a phenomenal job at explaining how to use Excel Leila. Please keep up the good work!

[@LeilaGharani]

Thank you for the kind feedback. I'm glad the tutorials are helpful.

[@jansentanguilan1739]

I never tought to use the isnumber function. Awesome Leila. Thank you.

[@LeilaGharani]

You're very welcome. Glad you like it!

[@blankmedia01]

You have some of my favourite content in the world. Thank you

[@mysarababekir569]

I watch your video because you are very nice☺ keep going

[@hafeezAhmed001]

Thankyou, I was looking for this formula.

[@Titina7183]

Thank you Leila for these videos. Your explanations are so clear. 😊

[@AntonyGugenberger]

Fantastic - elegant solution.

[@LeilaGharani]

Thank you! Cheers!

[@RoseAnnRabe]

Thank you so much for this!

[@edwilliamkidayan6773]

This is Awesome! Thank you Leila!

[@shabbirkanchwala-abwaab6263]

Gr8 You are a SOLUTION to any issue in Excel. Tons of Permutation n c Combination can be worked with your this invention. Thnx

[@LeilaGharani]

Thanks for the kind words Shabbir. Glad you like it!

[@AkshayKadam94]

I used countif instead of using isnumber and search. Gave same results.

[@Softwaretrain]

Last day someone ask me something like this, I gave her a way almost same as your solution but a little easier for two criteria. It will be smaller formula. =IF(OR(ISNUMBER(SEARCH({"DE","AT"},A5))),"Europe","") Thanks for your training. Mahmoud Baniasadi , from Iran

[@kurapikanostrad4592]

How do I carry over the exceeding value or the remaining one? For example I have to pay $10,000 but my salary is only $2,100 January is 0 all used and going vacation on february. February is 0 because of vacation and will only receive after I come back (weird company policy) March is 2,100 + 2,100 (paid leave from previous month) + 2,600 (Paid ticket) - 500 (expenses) = $6,300 April 2,100 so on and so forth so 5% of 2100 is 100 for savings. 25% of 2,000 is 500 for daily expenses. 1,500 is can Pay for remaning loan. if 10,000 - 6,300 = 3,700. How can I carry this remaining value to other cell. So that the next month which is 1,500 salary will be deducted.??? Hope you get my explaination.

[@Softwaretrain]

@@kurapikanostrad4592 put your input in an excel sheet and send it to me. [e-mail]

[@GrahamSchroder]

Saved me hours once again - thanks heaps!

[@sriesinger]

Beautifully explained Long since I was wondering why the * function doesnt work to retrieve a particular letter while using IF Awesome piece of information

[@terrycline8689]

and if you are doing the same basic thing but within VB, you can use InStr (and InStrRev), which also returns the location in the string if a match is found.

[@michaelthomashill]

I thoroughly enjoy watching your videos. Very informative and practical. This is stuff you can actually use in real-world applications. Thanks!

[@LeilaGharani]

Really glad to hear that Mike. Thanks for the feedback.

[@peternganga9371]

Thanks Leila. Another great one.

[@elzar760]

Been looking for something like this. This is awesome. Thank you!

[@LeilaGharani]

You're very welcome!

[@NeLzKieTV]

Thank you so much for your tutorials Leila❤

[@kadhumalasadi5861]

Thanks Leila from Iraq

[@tamekabailey405]

Great video, what about if you had different kinds of fruits listed and want to know which fruit had a quantity sold is less than half of its initial quantity

[@tica5791]

You are a life saver Leila

[@LeilaGharani]

I'm really glad to hear that :)

[@salehmohammad5170]

Very useful.. But little bit difficult! love you Leila 😍

[@alinaa3408]

Leila, thank you so much for all your tutorials. You explain it better than anyone else. Quick question-in your last example-on min 6:12, the formula that you have on top works for first part, but what if the statement is not true and I want to use again an "IF", so the logic can look for another "or" match. It does not seem to work for me. Thank you =IF(OR(ISNUMBER(SEARCH(122.5,C4)),ISNUMBER(SEARCH(150,C4))),"SMALL",IF(OR(ISNUMBER(SEARCH(150,C4)),ISNUMBER(SEARCH(224,C4)))),"MEDIUM",IF(ISNUMBER(SEARCH(315,C4))),"LARGE","")

[@samanthadaroga4811]

Really nice video. Quite helpful.

[@shootbaaz1754]

Awesome Ma'am, Thank you so much.

[@ChriseanKim]

This is really I was looking for. Thank you.

[@LeilaGharani]

Glad I could help!

[@ChriseanKim]

@@LeilaGharani Other you tube tutorials was difficult to understand or sounds was not clear to hear. Your lesson is easy to understand, very comfortable and very practical. I subscribed you and start watching every video. Really appreciate your effort and reply.

[@j.fabricioelias227]

Thanks for the trick!

[@LeilaGharani]

Our pleasure!

[@carlosmariopinzon1439]

Always you save my projects, Leila ❤

[@LeilaGharani]

😊

[@kaaa3485]

Awesome video and a good tip of the IF function. Thanks Leila we appreciate you so much. please, keep up with the great work.

[@LeilaGharani]

You're very welcome. It can come in quite handy sometimes.

[@smoreiram]

It was simple and exactly what I was looking for, even the control + enter was handy!

[@gtitman2]

This was an incredibly helpful video. Thank you!

[@JoaoSantos-jb7ul]

Another excellent video, Leila! Thank you!

[@ExcelHubPro]

How can we do if value find DE should return DE if At Should return AT?

[@sarfarajkureshi6569]

Nice mam good teaching god bless you...

[@LeilaGharani]

I'm glad you like it.

[@DustinMedure]

Leila - Thank you for all the Excel videos. They are all so helpful and I have enjoyed every one so far. Please don't stop.

[@LeilaGharani]

I'm glad to hear that and don't worry Dustin. I'll keep them coming :)

[@otienoluke5668]

Thank you Leila. I have been stuck for more than a month now trying to configure how to handle this...can i get the formula version for power query?

[@tiberiusiulius2088]

AH :D that last part is what i really needed. I kept getting the N/A error until i watched the video. Still can't figure out why, but i also tried with IFS and got N/A, so thanks for the lesson !

[@sivakarthikmba2011]

Madam y have teaching very well ...some times more difficult understand your example because of old version and new version.. am using 2016 version in this version not working unique function but xlookup its working how to unique function give me solution

[@raeaustin]

What if you have two columns of data and you want to determine a partial match for each column by row?

[@eminentramos9080]

I am a professionally data analyzer. Most of the time i got solution from your channel. Thank you I have a problem i have searched on internet several times but i didn't get any solution My problem is There are 3 columns with same data but it shuffled and some data are missing i want to take the all data such as parallel could you please help me

[@saeidlatifi9930]

Thank you, Leila 🙏 What if is search for a date instead "AT"?

[@monifataylor.realestate]

You are great at making complicated things simple. Thanks for this video . It is exactly what I'm looking for except I need it in Google Sheets. What tweaks should I make for it to work in Google Sheets?

[@soranhusain2958]

this video was so useful and already I started to use it in my report.

[@allforedandtech]

Amazing Video and also replies from viewers

[@richardbroadhurst2541]

What if I have a list of words (like AT and DE but more, maybe 10) and I want to search a column and every time it finds one of those words in the list it will return that word (like AT or DE) Also your videos are great, very clear. Thank you :)

[@waiyantun8629]

You just saved my day. Thank you so so much!!!!

[@behnamebrahimi1625]

Thanks Leila👍👌👏. Love you from Iran 😊

[@economia-apoio300]

Thanks, it helped me a lot!

[@satviksatvik3033]

Thank you. You made my task really easy.

[@LeilaGharani]

Great!

[@USAF_Vet_]

I also like using this with a named range of values as an array formula as well when I want to search job titles within my dataset. For example, I would create a range list called "titles" and in it I would have things like CSR, Call Center, Customer Service, etc....so my formula would be {=if(isnumber(search([titles],[cell to search within])),1,0)} I put the 1 and 0 so I can quickly filter to just the "1's" and see all of those titles to ensure they were mapped to a specific division correctly.

[@LeilaGharani]

That's a good use case! Thank you for sharing Nigel.

[@pratyushnigam8956]

Thanks mam you have just made it simple.....I wasn't having answer to this question why wildcard isn't working after if.....now I can die peacefully...😆.... thank you so much mam.

[@mariatomova9379]

You are an absolute magician!!!!!

[@LeilaGharani]

Excel witchcraft :)

[@akhibadak]

Wonderful tips.

[@LeilaGharani]

Glad you like it Muhammad.

[@lorimiller623]

Thank you, thank you, thank you!

[@kemle]

I considered subscribing to this channel

[@LeilaGharani]

I hope you do. We'd be happy to have you here.

[@krn14242]

Very smart. Thanks Leila

[@LeilaGharani]

You're very welcome. Glad you like it.

[@tunneloflight]

I would approach it this way: =SUM((IFERROR(SEARCH({"AT","BD"},”TestCell”),0)>0)*1); where AT and BD were the two searches, and “TestCell” is the cell being searched. It using your case =IF(SUM((IFERROR(SEARCH({"AT","BD"},”TestCell”),0)>0)*1),"Europe",""). It is necessary to use the Boolean evaluation (*1) to assess as a numerical result rather than a logical one, and the sum to avoid the #SPILL error in a table. The advantage this has in a formula is that it puts all of the wildcards together, making it easier to maintain, and also easier to use as a Lambda function.

[@markparry1922]

Nice technique, Leila. That will be very useful.

[@LeilaGharani]

Glad you find it useful Mark!

[@tristanchisholm5857]

Perfection, thank you!

[@willgreen2284]

Very clever. Nice work Leila.

[@LeilaGharani]

Glad you like it Will.

[@dhiren527]

Is it possible to do this with a range/array of strings, As in, instead of 2 (De or At), could we have excel search through hundreds of cells in a column, and return a value if isnumber returns true?

[@scarlet5322]

Hello your videos are helpful the video only shoes for 2 criteria how more than 2? hope youll notice.

[@zahmed87]

Thanks Leila, You have excellence in Excel. Make a video on Pivot Table and how to make as good as report from it.

[@LeilaGharani]

You're very welcome. I cover Pivot Tables in my Advanced Course. I will add more videos on KZbin too. Thank you for your suggestion.

[@hemmojito]

Could you return whatever is after AT? So dependant on each cell there would be a different result? Thank you in advance.

[@goodgollywally]

Another great video.

[@LeilaGharani]

Glad you like it Walter.