Excel Partial Text Lookup Formulas. Top 5 methods for Fuzzy Lookup. Excel Magic Trick 1691.
HTML-код
- Опубликовано: 9 июл 2024
- Download Excel File: excelisfun.net/files/EMT1691....
Learn how to perform a partial text lookup; Finding “Coca Cola” in “Coca Cola Inc.” or the reverse, find “Coca Cola Inc.” in “Coca”. See lookup formulas in Microsoft 365 Excel and any other version of Excel.
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
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
Old School or New School, Excel is so much FUN. :)
Wondering if you would make a video for doing it with Power Query Merge?
@@wmfexcel I tried the fuzzy match and it is similarly unreliable in Power Query too.
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
You are welcome! Good luck tomorrow : )
Another fantastic video Mike, thank you! I must keep reminding myself NOT to give up on learning complicated formulas!
SuperB as always!
: )
I cann't forget "Isnumber and search " compo ... you learned me earlier .... Many thanks MIKE
You are welcome!!!
So glad you explained how to “fuzzy lookup”, I run into this problem almost daily.
Glad to help! Since it is not 100% a guarantee to work, what are your situations that you run into almost daily?
Thank you. You know your stuff!
If I know, than I share : )
Lovely amazing... Salute to you, sir...
Glad it helps!
Magical lookups 🤩🤩🤩💥💥💥
Glad you like the magic, nirmal!!!!
Yes ofcourse...!!!
Thank you so much Mr Mike .. you are amazing.
Your follower from saudi Arabia.
You are welcome so much!!!!
really useful, thanks for spending time to make this,
Hi, Sir
Tremendous and outstanding knowledge of you.
Every examples are outstanding.
I am big FAN of you sir.
Glad you liker them, A b h i s h e k ! ! ! ! ! ! ! !
Thank you for the great look-up video Mike. Get back to your book :) Can't wait to see what you publish!
It should be a very detailed book about much pf what i know. But it sure is going slow... : (
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!
Thanks for the love and respect from India : ) : )
That's was a great refresher on some classics. Thanks Mike
You are welcome, Matt!
Thanks, it's really helpful.
Awesome! Thanks a lot for sharing.
You are welcome for the shares : )
Great of you boss, your explanation is unique from others.
Stay blessed
As always, you are a great source of information and help, great video easy to follow and to the point. Thank you.
You are welcome, Dave : )
Splendid explanation Mike. As brilliant as ever. Thanks for sharing!
You are welcome for the share!!!!
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 😊
Always glad to help! But, fuzzy match is always not 100% sure...
Perfect as always Mike, appreciate it ☑️
You are welcome, Mehran!! But remember, Fuzzy Lookup can never be 100% sure because of the wild cards which could be anything.
Thanks Mike. All Formulas were Great!! : ) Loved it!!
Thanks for the love, John!!!!
Thank you for sharing old school method as well, that's very helpful...!!!
Yes, Old School will be for us for a while, even though the new Excel is miles and miles better than old Excel.
Boom!Really Great Formulas...Thank You Mike :)
Boom, Boom, Boom!!!!! Thanks Darryl : ) : ) : )
Wow, very cool Mike!!
Glad you like it, Chris M : ) : ) : )
Always amazing Mike thanks so much
You are welcome so much!
Great video, as always!
Glad you like it, kostas, even though with fuzzy match, it does not work 100% of time...
Brilliant video Mike. Xlookup is way too smart and exhaustive 👍 Cheers
Thanks for the cheers : )
Hello Mike big thank you!!
Have been following your videos ...they are super helpful in my daily office work 😀
Big You are welcome, Rashmi!!!!
Awesome :)
Excellent Video, Thanks.
Glad it was EXCELlent for you : )
WoW amazing Mike with EXCELlent video about lookup. Thanks for the share.
You are welcome for the share!
Great video and I really like the old school method.
Yes, Old School is fun : )
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))
Awesome Single Cell Formula, cr gr0912!!!!
I am going to add this to the download workbook so our other Teammates can see too : ) : )
Thank you
You are welcome!
Fascinating. Thanks for sharing
You are welcome for the share!
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.
Yes, for Approximate Match lookup , LOOKUP is great : )
Very Nice and Informative Video.
Glad you like it!
All great methods to achieve the results
Thank, Vida : )
amazing!!
Glad you like it : ) : )
You are absolutely mind-blowing...
Salute to you Sir
Glad to help, Mido : )
Thanks
Hi Mike,
You can use OR() instead of the COUNT() function in E17 cell. 👍
LOVE that, Predrag!!!!!
Good one 👍
Glad you like it, Excel (A-Z)!!!!
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!.
I almost never use fuzzy match in any tool because it is not reliable. But looking at how people use Excel through out its history, people do a lot of fuzzy lookups... So people seem to use it.
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!!
Yah, I almost never use Fuzzy Lookup because I do not trust it. But lots of people do, so in this video i wanted to show the topic from both directions. However, I do not think I did a good job becasue I did not emphasize the fact that it is not 100%...
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")
LOVE it!!!!!!! So many ways to have fun 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")
The MMULT never ceases to amaze me!!!! Thanks Excel Wizard!!!!
I am going to add this to the download workbook so our other Teammates can see too : ) : )
@@excelisfun Thank you. 😍
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....🙏
You are welcome for the growth and knowledge : )
Excellent!! I wonder if it's worth getting this fuzzy lookup addin from microsoft labs
Great...
Glad it is great for you!
hello, thanks for your sharing, so that does the formula work only on microsoft 365 or any version of Microsoft office ?
awesome
Glad you like it!!!
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.
Glad it is great for you, C Jimmer!
As I mentioned in another comment, fuzzy match can never be perfect. There is always a chance of an error. When I tried Power Query fuzzy match, it was hard to be consistent.
Excel genius
Thanks a lot!
Excellent Man, You nailed it. Can you please explain about power query in excel.
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...
You can add a space to end to find just John, but fuzzy lookup is never 100%... : (
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 🙏
You are welcome for the information.
Hi Mike, Thanks for the best tutorials
How to implement index Show Values As similar to PivotTable in Power BI?
I do not have a video on that. Sorry about that : (
Super
Glad you like it!
Thank you @ExcellsFun. This's the best excel tutorial that I've ever seen.
Kudos for your great work.
You are welcome, Nhan Nguyen Duc!!!!!
I enjoyed this video ......👍
I think flash fill will also work .....
If there tables are adjusted......
Glad you enjoyed it : )
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?
For some reason my SEARCH function results in a #VALUE and doesn't return any coordinates... any ideas?
Thanks Mike... signed... WRH :)
WRH!?!?!?! krn14242? Is that you?????
@@excelisfun Yep, changed my username recently. k stands for Kevin.
@@krn14242 krn14242!!!!! WRH!!!!! or just plain kevin!!!!!!
@@krn14242 I'll always know you as krn14242 - THE original at excelisfun : )
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
I am excelisfun. Bill Mr Excel Jelen is Mr Excel : ) You can use NA() function for zeroes in graphs. Maybe this: ruclips.net/video/3AokhwA7q7M/видео.html
Xlook up Won for me even though it is Not available on Mobile Excel.
Yes, XLOOKUP is really good : )
👍
Thanks : ) : )
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.
Yes, it is fuzzy lookup, but fuzzy lookup is always problematic and not 100%. You can never be sure if it is working correctly unless you manually check.
Old School 4 Life
Cool!!!!!
what’s to do w F9 ?
F9 evaluates and shows what the individual formula elements evaluates to - that way you can see how the different formula elements contribute to the final solution. If it is not helpful for you, it may be that you are very smart and can understand the whole formula without it ; )
4. (02:52) Spilling Formulas in Microsoft 365 Excel.
: ) : )
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 noticed that as well. I haven't tried this but I wonder if adding a space before and after the SubTextString would avoid finding embedded matches in the FullTextString? In mean adding spaces within the functions not the table itself.
I should have said more about that. But Fuzzy Match is never 100%. Although I made this video becasue people ask for this, I do not use Fuzzy match, just becasue there is always room for error like this.
@@excelisfun Thank you for the clarification. I was hoping you had overcome that problem. I rarely looked at anything but exact matches because of the possibilities of errors and had never considered Fuzzy Logic.
@@dennisd5776 As a few people commented, you can add a space to the lookup value to enforce finding just the part, but even that is not 100%.
@@excelisfun Cannot agree more. Whenever I was asked to perform fuzzy lookup, I always emphasize the fact that the result may not be used directly. We still need to spot all the outlines manually... which is ineffective when dealing with large amount of data. :(
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.
As others metioned and I metioned in other comments, Fuzzy Match can never be 100% sure, and in fact I almost never use it because it is too unreliable. But someone asked about it, so I wanted to show both directions of this sort of lookup.
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.
Yes, but Fuzzy match is always a solution with a chance for errors. As I mentioned in some other comments, I never use it my self, of fuzzy match in Power Query for this reason. But people asked about it, and people commonly do it. So I wanted to show how to do it with both directions.
Love this: =IFNA(INDEX($G$19:$H$21,MATCH(1,SEARCH($G$19:$G$21&" ",$B20),-1),2),"Not Found")
I am going to add this to the download workbook so our other Teammates can see too : ) : )
@@excelisfun thanks. I really enjoy your channel. I am learning a ton!
@@t.pigeon2384 It is amazing Team!!! Thanks for your contributions : )
Thank you
You are welcome, mazar!!!