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.
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:
See you next time 😉