Try This Instead of the XLOOKUP

Try the Fuzzy Lookup in Excel instead of the XLOOKUP function.

Fuzzy lookup tutorial in Excel. The XLOOKUP is arguably the most powerful function in Excel, but there's a better alternative for matching values called fuzzy lookup. In this tutorial I show you how to use the xlookup matching for exact matches and partial matches with the wildcard feature, as well as the xlookup limitations. Once we understand that, we go over the solution of using fuzzy lookup in power query, as well as installing it as a separate add-in inside of Excel.

LEARN:

SOCIALS:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ XLOOKUP Exact Match
0:53​ - XLOOKUP Partial Match
2:04 - Problem with XLOOKUP
2:40​ - Fuzzy Lookup
5:15​ - Fuzzy Lookup in Excel
8:46​ - Power Query vs Excel Version

[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.

[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!

[leonmaze]

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

[laserwebz]

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

[AkbarAazra]

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

[Waheed]

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.

[Shivakrishnan]

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

[jakubjanicki]

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

[dameskytower]

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.

[herherher]

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

[shabchique]

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.

[saiabecefg]

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

[preetshah]

Please make a video on customer churn analysis

[rafayhussain]

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

[godfreymusyimi]

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

[madhavraval]

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

[stanleygadagoe]

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

[sevinthomas]

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

[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?