Additionally, some lookup functions support the use of a wildcard which has helped in some situations as well. And, depending on the data set, lookup values, and sort order, this may or may not provide the expected approximate match desired. For example, when you set the 4th argument of VLOOKUP to TRUE, or the 3rd argument of MATCH to 1 or -1, it enables us to perform range lookups.
Note: some lookup functions provide an option for “approximate” match. With Power Query’s fuzzy merge option, they do: With a traditional lookup function such as VLOOKUP, they don’t match: You’ll notice there are some differences: ABC Company vs ABC Company, Inc., Micro soft vs Microsoft, and Xeon vs Xeon Products. We would like Excel to match them to this table (named CompanyList): The idea with a fuzzy lookup is to enable Excel to match them.įor example, here is a table (named CompanyName) with some company names:
But to Excel, these represents different lookup values. Now, t o us humans, these two names represent the same underlying company. When the lookup values are different, for example ABC Company and ABC Company, Inc., traditional lookup functions aren’t always reliable. There are some exceptions to this as noted below, but generally, the idea is that the lookup values are the same between both lists. The case can be different (eg, “Microsoft” and “microsoft” would match), but generally, the idea is that the text strings need to be the same.
Generally speaking, Excel lookup functions expect that the lookup values match on both lists. This post walks through the basics … and a huge THANK YOU to the Microsoft developers for this feature 🙂 Objectiveīefore we get to the mechanics, let’s back up and understand what we are trying to do and what a fuzzy match is. A few years ago, I wrote a post about how to perform fuzzy lookups using the “Fuzzy Lookup Add-In for Excel.” However, this capability is now available in Power Query!! Wait, what? Yes! Performing a fuzzy match is so much easier and far more intuitive in Power Query.
0 kommentar(er)
