Topics: 1. (00:00) Introduction. 2. (00:10) Understand the two different Fuzzy Lookup, or Partial Text Lookup situations. Which one do you have? 3. (00:42) XLOOKUP Function Spilled Array Formula. Find “Coca Cola” in “Coca Cola Inc.” Microsoft 365 Excel. Any Version of Excel. 4. (02:52) Spilling Formulas in Microsoft 365 Excel. 5. (03:22) VLOOKUP and IFNA functions. Find “Coca Cola” in “Coca Cola Inc.” 6. (04:24) FILTER, SEARCH and ISNUMBER functions. Find “Coca Cola Inc.” in “Coca”. Microsoft 365 Excel. 7. (06:37) XLOOKUP, SEARCH and ISNUMBER functions Find “Coca Cola Inc.” in “Coca”. Microsoft 365 Excel. 8. (07:12) INDEX, MATCH, SEARCH, ISNUMBER, IF and COUNT functions. Find “Coca Cola Inc.” in “Coca”. Any Version of Excel. 9. (07:47) When you should NOT use IFERROR or IFNA. 10. (09:12) Summary, Closing and Video Links

Simply amazing after all these years. Tomorrow i'll hold my first Excel-training in my company - that wouldn't be possible without Excel is fun! Thank you, Mike! greetings from Germany

Lovely amazing... Salute to you, sir...

Another fantastic video Mike, thank you! I must keep reminding myself NOT to give up on learning complicated formulas!

I cann't forget "Isnumber and search " compo ... you learned me earlier .... Many thanks MIKE

SuperB as always!

Thank you. You know your stuff!

As always, you are a great source of information and help, great video easy to follow and to the point. Thank you.

Hi, Sir Tremendous and outstanding knowledge of you. Every examples are outstanding. I am big FAN of you sir.

Wow...love it Mike. I had a list of 20,000 rows of data in a column to find a match from 30,000 rows of data in another column. I found a solution in PQ but this formula solution of yours would sure have helped me more 😊

Great of you boss, your explanation is unique from others. Stay blessed

Thank you for the great look-up video Mike. Get back to your book :) Can't wait to see what you publish!

Magical lookups 🤩🤩🤩💥💥💥

So glad you explained how to “fuzzy lookup”, I run into this problem almost daily.

Thank you so much Mr Mike .. you are amazing. Your follower from saudi Arabia.

Splendid explanation Mike. As brilliant as ever. Thanks for sharing!

Hello Mike big thank you!! Have been following your videos ...they are super helpful in my daily office work 😀

Awesome! Thanks a lot for sharing.

Hi Mike, When I watch Your Videos, I am in a Constant State of Admiration. Salute! I see a lot of Comments from My Fellow Indians. Love & Respect from India!

That's was a great refresher on some classics. Thanks Mike

Thanks Mike. All Formulas were Great!! : ) Loved it!!

Thank you for sharing old school method as well, that's very helpful...!!!

really useful, thanks for spending time to make this,

Thanks again!! You also had a great formula that used the Lookup with a return vector and search function which is similar to the method shown here. I use it quite a lot and has never let me down also seems much shorter than this.

Thank you

Perfect as always Mike, appreciate it ☑️

Brilliant video Mike. Xlookup is way too smart and exhaustive 👍 Cheers

Thanks, it's really helpful.

Boom!Really Great Formulas...Thank You Mike :)

Always amazing Mike thanks so much

You are absolutely mind-blowing... Salute to you Sir

WoW amazing Mike with EXCELlent video about lookup. Thanks for the share.

Hi Mike, You can use OR() instead of the COUNT() function in E17 cell. 👍

All great methods to achieve the results

Another great video; it does seem the fuzzy lookup in Power Query is as troublesome when using it on a project recently. Thanks, Mike as ever!.

Fascinating. Thanks for sharing

Very Nice and Informative Video.

Great video and I really like the old school method.

Excellent Video, Thanks.

Great video, as always!

Hi Mike. Excellent.. as always! Love the creative ways to lookup FullTextString into SubTextString with SEARCH. Only problem is you do get a Male match on Dino Johnson which should technically be Not Found. I know that is a fuzzy match issue. Could be solved maybe by altering the formula to do the match on only the first name, assuming you could clean the data of the prefixes of Mr., Mrs., etc. Always a challenge or compromise when the data is not clean and or uniform. Great stuff! Lots of good learning here :)) Thanks and Thumbs up!!

Great use of wild character , remembered the old tilde trick learned from you for wild characters!!!!!😊 For the second part came with a single cell. Dnt know if it's the shortest but it's the first that came to my mind and follows a straight reasoning. =LET(fts,B17:B21,sts,G19:G21,g,H19:H21,rws,ROWS(sts), srcharr,ISNUMBER(SEARCH(sts,TRANSPOSE(fts)))*SEQUENCE(rws), mm,MMULT(SEQUENCE(,rws,1,0),srcharr), rs,INDEX(g,mm),rsf,IF(mm=0,"Not Found",rs), TRANSPOSE(rsf))

Wow, very cool Mike!!

Good one 👍

Liked Sirji..... Superb...👍 Wild Card...👌 Every time u come up with something new and unique Learning... Thank You Sirji for everything you do for us to grow our knowledge....🙏

Thanks

Excellent Man, You nailed it. Can you please explain about power query in excel.

Nice one. Spill array for FullTextStringLookup =IFERROR(INDEX(H19:H21,1/(1/MMULT(--ISNUMBER(SEARCH(TRANSPOSE(G19:G21),B17:B21)),SEQUENCE(ROWS(G19:G21))))),"Not Found")

Awesome :)

amazing!!

This is great!I learned something about xlookup. I was hoping to see fuzzy match options and power query under combine merge queries. That would make a good video too.

Excellent!! I wonder if it's worth getting this fuzzy lookup addin from microsoft labs

Great...

Excel genius

Hello Sir Good morning! How are you? I really like your style and the way to convey the information. You are so calm while solving the tricky question. I have seen your many videos and learning advanced excel. Thank you so much Sir 😍 I have only 1 request. Why don't you start the interview questions series. You are my first online teacher. GBU 🙏

I enjoyed this video ......👍 I think flash fill will also work ..... If there tables are adjusted......

awesome

Thanks Mike for lots of amazing partial text search solutions !!! From me, not obvious use of COUNTIF function (as an old school method) ;-) =IF(SUM(COUNTIF(B17,"*"&$G$19:$G$21&"*")),INDEX($H$19:$H$21,MATCH(1,COUNTIF(B17,"*"&$G$19:$G$21&"*"),0)),"Not Found")

Super

hello, thanks for your sharing, so that does the formula work only on microsoft 365 or any version of Microsoft office ?

Hi Mike, Thanks for the best tutorials How to implement index Show Values As similar to PivotTable in Power BI?

Mike, thanks for sharing! In FullTextStringLookup I like the second method best :) It's brilliant! And I'm still thinking what to do with Dino JOHNson? He is male, obvioulsy, but what about Tina Johnson? I don't have a clue...

Thank you @ExcellsFun. This's the best excel tutorial that I've ever seen. Kudos for your great work.

👍

Thanks Mike... signed... WRH :)

In the first method there's two words in a cell (Coca Cola), but in the second, you said the formula would be able to look up only one word. My problem is that I want to do the second method, but the look up cell has two words (or more) like Coca cola. What do I do? Do you have another video about it?

4. (02:52) Spilling Formulas in Microsoft 365 Excel.

Old School 4 Life

For some reason my SEARCH function results in a #VALUE and doesn't return any coordinates... any ideas?

Hello Mr Excel ! Could you help us with a solution to a pivot issue in excel? I have instead of “0”s , dashes and this looks so bad in my graphs. I was wondering if there is any solution to dynamically update the pivot table with the 0-es. Many thanks

Xlook up Won for me even though it is Not available on Mobile Excel.

what’s to do w F9 ?

Nice formulas, BUT, in the FullTextStringLookup you may run into a bit of trouble using the ISNUMBER(SEARCH) function inside the FILTER formula. If, for instance one of the females had a last name that also includes the search criteria (i.e. John), you would get a #SPILL! error.

Is that really fuzzy lookup? I dont see how/that it would work if there ar misspellings in the lookup table (or database) that with some certainty should produce a match.

Noticed in FullTextStringLook that the forth name, Dino Johnson, came in as male. I am assuming that is because 'John"son matched John in the SubTextString. But what if it was Sara Johnson - then the gender would be incorrect. Is there a solution?

I have a bit of an issue (very minor and easily fixed) with the solutions for the full to partial lookup. Dino Johnson shows up as Male, when in reality it should be a "Not Found" -- what if it was Bertha Johnson? So, I think all the solutions should be changed to search like the modified Filter formula below (best solution -- thanks I learned a lot). FILTER($H$19:$H$21,ISNUMBER(SEARCH($G$19:$G$21&" ",B17)),"Not Found") The weakness with the above solution is that if you have just the name "John" in the full text column it won't work and that will work with the original solution but I think that is a less likely occurrence than the "Bertha Johnson" problem. Of course, you can incorporate the full name match with an if statement wrapper in the above solution and using a LET could make that less painful. I don't have LET yet so I can't tell. Just so people are aware, it is easy to spill the VLOOKUP solution in the first part. Finally, I came up with an efficient match solution, which I will illustrate with an index function. =IFNA(INDEX($G$19:$H$21,MATCH(1,SEARCH($G$19:$G$21&" ",$B17),-1),2),"Not Found") Thanks again.

Thank you