Excel – XLOOKUP with fuzzy match

Recently, Microsoft has lunched the new Excel function XLOOKUP. Even if it is still in the Insider program, this function is becoming the most famous functions in Excel. Many articles are writing about this function so in this blog I will only talk about the match mode option.

This function has the following syntax that can give us some options by using it:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])

The fourth argument is very useful especially if we want to search by using a wildcard in the case that the lookup array has results not similar in 100% to the lookup value.

For example, in many companies the name of a customer can be writing in many ways into the database, as we know, it’s one of the main issues that we found in many CRMs. ABC Trucks can be found as:
ABC Trucks / SARL ABC Trucks / ABC Trucks SARL / ABC Truck / ABC-Trucks / ABCTrucks / A.B.C Trucks / ABC Trucks. SARL

For some of these cases we can use XLOOKUP with different wildcard and for another we cannot perform searches with XLOOKUP.

Using different syntax to get the correct result

Therefore, in this kind of search, the best way is to use fuzzy lookup. It can be done by using Power Query or the fuzzy lookup Add-In.

Doing the same search with Fuzzy-merge in Power Query with a threshold of 80% will give us the following results:

I am sure that the question in your mind is why do not add an argument to XLOOKUP with an approximate result by specifying the threshold.

I added an idea in the Excel UserVoice website, link below, to ask Excel developer team to add a fifth element to the argument Match_mode in which the user can add a value greater than 0 and smaller than 1. And we will get something like this:

That’s it! Please feel free to comment and to give your opinion about this idea, and if you agree, please vote for it by visiting the following website:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38560402-xlookup

See you next time 😉

Publicités

2 commentaires sur “Excel – XLOOKUP with fuzzy match

    1. Merci Mehdi. Oui il existe sur dans la toute dernière version d’Excel, d’ailleurs je l’ai utilisée pour mon exempla dans l’article.
      sinon, n’oublie pas de voter pour l’idée, si t’es d’accord bien sur 😉

      J'aime

Répondre

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l'aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s