23Oct 2013 by Charlie Nichols 2 Comments

pwrVLOOKUP. Just like VLOOKUP, only Better.

You can do a VLOOKUP operation in Excel and get a fuzzy match instead of being limited to the exact match function built into Excel. To do a fuzzy VLOOKUP you need to use the pwrVLOOKUP function that is part of the Excel PowerUps Premium Suite add-in which is available as a free download on this site.

Usage of the pwrVLOOKUP function is very similar to the built-in VLOOKUP function. The first 3 parameters are the same (lookup value, lookup range, and column offset). The difference starts in the fourth parameter. In pwrVLOOKUP, the fourth parameter is a strength-of-match value. It is a number between 0 and 100. If you do not provide this value, an exact match (strenth-of-match = 100) is performed.

By varying the strength-of-match parameter you can control the degree to which your lookup matches against your column of data. A lower value will give a less precise match. You are able to specify the degree of fuzziness you need in your worksheet.

Fuzzy VLOOKUP Example

In the image below I’m doing a fuzzy VLOOKUP match against a table that contains names and ages. You can see the name I’m matching against is not exactly in the list. There is a similar variant in row number 14, which the pwrVLOOKUP function matches.

You can see that the formula I used was the following.

pwrVLOOKUP(E2,Table1,2,50)

I chose 50 as the strength-of-match value as it seemed to be forgiving enough to allow for a fuzzy match in Excel for the following strings. You can tighten the match by using a number closer to 100. If you use 100, it will do an exact match — essentially it will be the same as passing “FALSE” in the normal VLOOKUP function.

Kenneth Mcintyre

Ken Macintyre

Note that all of the names in this example were randomly generated using the pwrRANDOMNAME function. Any similarity to an actual name or person is coincidental.

Similar functions are available for pwrHLOOKUP and pwrMATCH so you can apply the fuzzy search capability to the HLOOKUP and MATCH scenarios as well.

If you are accustomed to using INDEX+MATCH instead of VLOOKUP you can now get the power of the INDEX+pwrMATCH combo!

Finally, if you don’t need to perform a lookup and instead just need to do a fuzzy compare of two values in Excel, see my post about Fuzzy Search in Excel to Find Similar Text Values in Excel.

There you go.