Try This Instead of the XLOOKUP

[@KenjiExplains]

🚀 Take our Data Analyst Program here: www.careerprinciples.com/career-track/the-data-analyst-program

[@MagicOfMushrooms]

I'm an analyst and this doesn't sound like something I'd need to use all the time but is great to have in my back pocket when the need inevitably arises.

[@Jayeshpatil309]

Can you suggest me the perfect roadmap for excel data analyst

[@SuzLynch]

I just came across your videos and find them very useful and I've already learned a few new functions. However, for the Fuzzy Lookup video, please review the results at time 8:26-8:45, as the results don't show Age, but another column of Sales.

[@damorgman]

Very cool and well explained. With thousands of records for this example with all the name variations today I can see where this would have some data integrity problems, but still some very valid uses. Thanks!

[@laserwebz]

Thanks so much, Kenji I was doing wildcards with xlookup but no matches, fuzzy lookup is thw way to go!

[@leonmaze7369]

Most corporate offices don’t allow fuzzy add on to be installed

[@KenjiExplains]

Then using it in Power Query is the way to go :)

[@alexb9312]

Are sure about that ? Yes you will most likely need IT to install it but I think you might find that if you ask the question making it clear that you need it for your job and that is a download from Microsoft themselves that they will allow it.

[@timetriad6199]

If it’s a FREE M$ add-in… no issues.

[@Shivakrishnan2024]

wow this is completely amazing video and one thing I noticed was it was some fundamentals of SQL

[@Supplychainoptimieren-yk5df]

Very cool. Thanks for sharing

[@godfreymusyimi9025]

Thank you for such great content . However , issues of accessing datasets for practice has always been a problem to me .

[@miggzmejia]

Just google sample datasets

[@Waheed1736]

Not a preferable way, as it is more time consuming and Xlookup still stands a king for lookup formulas. But still this trick is useful for large data sets where it is repetitive to do such lookup.

[@shabchique7149]

Great video Kenji and thank you! Bummer I could not follow along because my 365 version did not suggest the number of matches after I enter threshold values .8 or .5, and on top of that, I was unable to click the OK button to proceed.

[@BadhonKarim]

The Age column is wrong

[@AkbarAazra]

as a Database developer,,, I would prohibit user to enter incorrect data ,,, at first place

[@herherher925]

Actually, you can take advantage of regex within the XLOOKUP functions by using the new [match_mode] = 3 and a regex pattern as the lookup_value

[@dameskytower1]

Great video! I always wondered about Fuzzy logic. I have a question if you would allow it. I have a spreadsheet that uses some scenario analysis. I want to query the results for each scenario (base, best & worst) automatically and simultaneously and have a transpose function render the output stacked on top of each other(base, best & worst). Currently, this is a manual process where I have to manually change the scenario and then use the transpose function to capture the data and then copy and paste the static values thus overwriting the transpose function. Is it possible to run the transpose function based on a scenario. BTW the scenarios are selected by data validation list. Thanks in advance for your feedback.

[@pluto_dp]

You never disappoint, Kenji 🙏🏻

[@KenjiExplains]

Thank you! Trying my best :)

[@preetshah3793]

Please make a video on customer churn analysis

[@jakubjanicki5931]

8:26 didn't it output wrong Age? Any way to fix it?

[@faizujutt4942]

Sir i,m big fan for you🥰

[@MrAszpic]

Does the fuzzy lookup (wither the add-in or in PQ) get the "closest option" when there are multiple matching alternatives? Or just the first one it finds on the list from top to bottom?

[@arielzlatkovski8636]

It's not the first match that it sees like vlookup. I believe it's comparing characters between the two values (one from each table) that you have told it to use as a link, and then assigning the "Similarity" value between 0 and 1. If you let it do multiple matches it'll do multiple rows for each of the left-table values. For example, if you have "Chris" in your left table and in the right table there are "Christopher" "Chris" "Charles" and "Steven" - and you tell it to do 3 matches with similarity threshhold of 0 (ie any match will do) - then it will generate 3 rows with matches for "Chris" from left table. It'll have something like "Chris" with Similarity of 1.000, "Christopher" with Similarity of .8 (no idea the actual number), and "Charles" with Similarity of .3 (again, no idea of the actual score, but it would be something less than for Christopher because there are less shared characters). Steven wouldn't be one of the 3 rows it chooses for "Chris" from the left table, because of the 4 names in right table the other 3 have more similarity in characters than "Steven" does. I hope this explanation makes sense!

[@rafayhussain4171]

Great Sir! How do you know about the changes in excel

[@KenjiExplains]

Thank you! What changes are you referring to?

[@akinsinasegun2379]

@@KenjiExplains I think he is asking about how you get info about updates in Excel

[@rafayhussain4171]

​@@KenjiExplainsall about new formulas

[@sevinthomas9496]

Hello, is there a formulae/trick to get the xlookup formulae displaying every results it finds in the table and not only the first one? Thanks

[@jakefarrugia5000]

Yes, use the filter function

[@saiabecefg]

Are the power query method or fuzzy addon available in excel for mac ??

[@stanleygadagoe3341]

Hi, please can anyone recommend any noise-cancelling software I can use during my virtuall meetings

[@camlex6310]

Great video!!

[@Weehawk]

This is very cool, thanks.

[@samuelapaguntan8658]

Brilliant..........👍

[@madhavraval238]

What if there are 3-4 Christopher, will it still work if there surnames are different?

[@KenjiExplains]

Hey depends on the exact scenario...try it out and see!

[@JoyceOnose_Official]

@madhavraval238. Yes it would work, because every other names after Chris was ignored in the vedio.. Else And also you have to change the relationship depend on what is on your dataset

[@AbdulRahman-yp6oy]

Excellent Kenji ❤ But you have already tell this tip in your previous video. What happened ? Why are you not uploading a new feature?

[@KenjiExplains]

Thank you! people wanted a more extended version of it :)

[@rajashreedas640]

Using the Fuzzy Addon in the excel, the Sales and Age output seems to be same. Please check that.

[@dineshwagle9832]

Age column showing incorrect result in the 2nd method.

[@DanielFrost-w7c]

Thanks so much

[@akinsinasegun2379]

I can't find fuzzy matching option in my Excel 2016 power Query

[@ramruttunaubeeluck9235]

Not available in excel 2016

[@vishalbharakhada3357]

thank u so much

[@AZ4ZEL666]

THANK YOU

[@KenjiExplains]

Thanks for watching :)

[@5pctLowBattery]

Thanks 5:10

[@willzinner8813]

interesting thanks

[@KenjiExplains]

You're welcome

[@MohamedAhmed-ob2nl]

unique tutorial

[@IbrarAsgharButtShorts]

Which free AI tool can be integrated with excel for natural language queries?

[@pi_xi]

As long you have an API key, you can integrate any tool using VBA. For example, you can integrate DeepL for translations.

[@WaldemardeHaas]

Nah. Just fix it in the data source itself and use keys/id’s instead for looking up data. Fuzzy matching will always be a boiling the frog method.

[@ronangaming26]

Hoping to see the amount "1,4567.00" to convert word.

[@MilMantar]

why would a company hire some fuzzy employee like this? i don't get it

[@00000raunak123456789]

😮😮😮

[@cybu6466]

I would be very cautious using this kind of function. Microsoft excel is not a sound rigourous tool. Maybe ok for prototyping but i would stop right here😂

[@ruelvaliente7265]

Bit like power BI

[@WaldemardeHaas]

In Power BI, the Power Query editor is the same actually, so in Power BI you can do the same trick, although I would never use fuzzy matching for real life scenarios.

[@how3recordings]

Simple fix the data sources and manage master data...no need for dodgy matching that's not working in the real world for anything important.

[@JoyceOnose_Official]

It does for small business sir.. 😅

[@aj_sc_]

Works for fixing manual entry data. Fixing data sources are not that simpler in some cases

[@thedeanman]

Why didn't you just use the Soundex option in XLOOKUP?

[@sbioch]

Something is wrong at 8:23. John Lee's age is 211000, as well as all the others. Seems unreliable, would not use in a professional setting.

[@yayagames97]

first

[@KenjiExplains]

Let's go!

[@sheet-chat]

This is an excellent video. Thank you for sharing it! I have developed an AI assistant that supports both Google and Microsoft platforms, providing an excellent experience. 🤖✨ I sincerely hope to collaborate with you. I look forward to your response. 😊