How to use the xlookup function in microsoft excel

The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. LOOKUP’s default behavior makes it useful for solving certain problems in Excel.

• lookup_value – The value to search for.
• lookup_vector – The one-row, or one-column range to search.
• result_vector – [optional] The one-row, or one-column range of results.

Use the LOOKUP function to look up a value in a one-column or one-row range, and retrieve a value from the same position in another one-column or one-row range. The lookup function has two forms, vector and array. This majority of this article describes the vector form, but the last example below illustrates the array form.

The LOOKUP function accepts three arguments: lookup_value, lookup_vector, and result_vector. The first argument, lookup_value, is the value to look for. The second argument, lookup_vector, is a one-row, or one-column range to search. LOOKUP assumes that lookup_vector is sorted in ascending order. The third argument , result_vector, is a one-row, or one-column range of results. Result_vector is optional. When result_vector is provided, LOOKUP locates a match in the lookup_vector, and returns the corresponding value from result_vector. If result_vector is not provided, LOOKUP returns the value of the match found in lookup_vector.

LOOKUP has default behaviors that make it useful when solving certain problems. For example, LOOKUP can be used to retrieve an approximate-matched value instead of a position, and to find the last value in a row or column. LOOKUP assumes that values in lookup_vector are sorted in ascending order and always performs an approximate match. When LOOKUP can’t find a match, it will match the next smallest value.

Example #1 – basic usage

In the example shown above, the formula in cell F5 returns the value of the match found in column B. Note that result_vector is not provided:

The formula in cell F6 returns the corresponding Tier value from column C. Notice in this case, both lookup_vector and result_vector are provided:

In both formulas, LOOKUP automatically performs an approximate match and it is therefore important that lookup_vector is sorted in ascending order.

Example #2 – last non-empty cell

LOOKUP can be used to get the value of the last filled (non-empty) cell in a column. In the screen below, the formula in F6 is:

Note the use of a full column reference. This is not an intuitive formula, but it works well. The key to understanding this formula is to recognize that the lookup_value of 2 is deliberately larger than any values that will appear in the lookup_vector. Detailed explanation here.

Example #3 – latest price

Similar to the above example, the lookup function can be used to look up the latest price in data sorted in ascending order by dat. In the screen below, the formula in G5 is:

where item (B5:B12) and price (D5:D12) are named ranges.

When lookup_value is greater than all values in lookup_array, default behavior is to “fall back” to the previous value. This formula exploits this behavior by creating an array that contains only 1s and errors, then deliberately looking for the value 2, which will never be found. More details here.

Example #4 – array form

The LOOKUP function has an array form as well. In the array configuration, LOOKUP takes just two arguments: the lookup_value, and a single two-dimensional array:

In the array form, LOOKUP evaluates the array and automatically changes behavior based on the array dimensions. If the array is wider than tall, LOOKUP looks for the lookup value in the first row of the array (like HLOOKUP). If the array is taller than wide (or square), LOOKUP looks for the lookup value in the first column (like VLOOKUP). In either case, LOOKUP returns a value at the same position from the last row or column in the array. The example below shows how the array form works. The formula in F5 is configured to use a vertical array and the formula in F6 is configured to use a horizontal array:

The vertical and horizontal arrays contain the same values; only the orientation is different.

Note: Microsoft discourages the use of the array form and suggests VLOOKUP and HLOOKUP as better options.

In this course:

• Overview of formulas in Excel
  Article
• XLOOKUP
  Article
• VLOOKUP
  Article
• SUM function
  Article
• COUNTIF function
  Article
• IF function
  Article
• IFS
  Article
• SUMIF
  Article
• SUMIFS
  Article
• MATCH
  Article

Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

Syntax

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

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

The value to search for

*If omitted, XLOOKUP returns blank cells it finds in lookup_array.

The array or range to search

The array or range to return

Where a valid match is not found, return the [if_not_found] text you supply.

If a valid match is not found, and [if_not_found] is missing, #N/A is returned.

Specify the match type:

0 – Exact match. If none found, return #N/A. This is the default.

-1 – Exact match. If none found, return the next smaller item.

1 – Exact match. If none found, return the next larger item.

2 – A wildcard match where *, ?, and

Specify the search mode to use:

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Examples

Example 1 uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It doesn’t include the match_mode argument, as XLOOKUP produces an exact match by default.

Note: XLOOKUP uses a lookup array and a return array, whereas VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)

Example 2 looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, so a single formula can return both employee name and department from cells C5:D14.

Example 3 adds an if_not_found argument to the preceding example.

Example 4 looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if_not_found argument to return 0 (zero) if nothing is found. The match_mode argument is set to 1 , which means the function will look for an exact match, and if it can’t find one, it returns the next larger item. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last.

Note: XARRAY’s lookup_array column is to the right of the return_array column, whereas VLOOKUP can only look from left-to-right.

Example 5 uses a nested XLOOKUP function to perform both a vertical and horizontal match. It first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range C5:F5), and finally returns the value at the intersection of the two. This is similar to using the INDEX and MATCH functions together.

Tip: You can also use XLOOKUP to replace the HLOOKUP function.

Note: The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

Example 6 uses the SUM function, and two nested XLOOKUP functions, to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two.

The formula in cell E3 is: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($E$7:$E$9) . You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then select Formulas > Formula Auditing > Evaluate Formula, and then select Evaluate to step through the calculation.

Note: Thanks to Microsoft Excel MVP, Bill Jelen, for suggesting this example.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges.

• lookup – The lookup value.
• lookup_array – The array or range to search.
• return_array – The array or range to return.
• not_found – [optional] Value to return if no match found.
• match_mode – [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
• search_mode – [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

XLOOKUP is a modern replacement for the VLOOKUP function. It is a flexible and versatile function that can be used in a wide variety of situations.

XLOOKUP can find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (see match type and search mode details below). Compared to older functions like VLOOKUP, HLOOKUP, and LOOKUP, XLOOKUP offers several key advantages.

Not found message

When XLOOKUP can’t find a match, it returns the #N/A error, like other match functions in Excel. Unlike the other match functions, XLOOKUP supports an optional argument called not_found that can be used to overide the #N/A error when it would otherwise appear. Typical values for not_found might be “Not found”, “No match”, “No result”, etc. When providing a value for not_found, enclose the text in double quotes (“”).

Note: Be careful if you supply an empty string (“”) for not_found. If no match is found, XLOOKUP will display nothing instead of #N/A. If you want to see the #N/A error when a match isn’t found, omit the argument entirely.

Match type

By default, XLOOKUP will perform an exact match. Match behavior is controlled by an optional argument called match_type, which has the following options:

Search mode

By default, XLOOKUP will start matching from the first data value. Search behavior is controlled by an optional argument called search_mode, which provides the following options:

Binary searches are very fast, but data must be sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.

Example #1 – basic exact match

By default, XLOOKUP will perform an exact match. In the example below, XLOOKUP is used to retrieve Sales based on an exact match on Movie. The formula in H5 is:

Example #2 – basic approximate match

To enable an approximate match, provide a value for the “match_mode” argument. In the example below, XLOOKUP is used to calculate a discount based on quantity, which requires an approximate match. The formula in F5 supplies -1 for match_mode to enable approximate match with “exact match or next smallest” behavior:

Example #3 – multiple values

XLOOKUP can return more than one value at the same time for the same match. The example below shows how XLOOKUP can be configured to return three matchging values with a single formula. The formula in C5 is:

Notice the return array (C8:E15) includes 3 columns: First, Last, Department. All three values are returned and spill into the range C5:E5.

Example #4 – two-way lookup

XLOOKUP can be used to perform a two-way lookup, by nesting one XLOOKUP inside another. In the example below, the “inner” XLOOKUP retrieves an entire row (all values for Glass), which is handed off to the “outer” XLOOKUP as the return array. The outer XLOOKUP finds the appropriate group (B) and returns the corresponding value (17.25) as the final result.

Example #5 – not found message

Like other lookup functions, if XLOOKUP does not find a value, it returns the #N/A error. To display a custom message instead of #N/A, provide a value for the optional “not found” argument, enclosed in double quotes (“”). For example, to display “Not found” when no matching movie is found, based on the worksheet below, use:

You can customize this message as you like: “No match”, “Movie not found”, etc.

Example #6 – complex criteria

With the ability to handle arrays natively, XLOOKUP can be used with complex criteria. In the example below, XLOOKUP is matching the first record where: account begins with “x” and region is “east” and month is not April:

XLOOKUP benefits

XLOOKUP offers several important advantages, especially compared to VLOOKUP:

• XLOOKUP can lookup data to the right or left of lookup values
• XLOOKUP can return multiple results (example #3 above)
• XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate)
• XLOOKUP can work with vertical and horizontal data
• XLOOKUP can perform a reverse search (last to first)
• XLOOKUP can return entire rows or columns, not just one value
• XLOOKUP can work with arrays natively to apply complex criteria

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Microsoft has just announced the new ‘XLOOKUP’ function in Excel.

Rumor has it that this new function is more powerful and robust than ‘VLOOKUP’ and ‘HLOOKUP’ . Some say that this function is an ‘INDEX’ + ‘MATCH’ killer.

But how does ‘XLOOKUP’ really stand against these functions?

In this article, we’ll explore the new ‘XLOOKUP’ function as well as teach you how to use it.

Let’s get into it! 🔍

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Table of Content

Introduction to XLOOKUP

The ‘XLOOKUP’ function is the successor to the old functions ‘VLOOKUP’ and ‘HLOOKUP’.

Like its predecessors, ‘XLOOKUP’ performs lookups .

But this time, it can go vertical or horizontal.

Some of its advantages against the older functions include:

• Returning results from more than 1 column
• Default match type is exact (that means, you can leave it blank if you’re searching for a specific value)
• Can perform a lookup to the right or left of the lookup value
• Capability to return a range instead of just a single value
• As stated, can perform lookup horizontally or vertically
• Can lookup data in reverse order
• Deals with arrays natively

Here’s the caveat:

As of now, ‘XLOOKUP’ is currently a beta feature and is only available for Office Insiders at this time. But don’t worry, it’s expected to be released to all subscribers once the function is ready.

The XLOOKUP Syntax

This function’s syntax is easy to understand. It’s a bit similar to ‘HLOOKUP’ and ‘VLOOKUP’ functions’ syntax.

The ‘XLOOKUP’ syntax:

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

• ‘lookup_value’ – the value to look for
• ‘lookup_array’ – the range or array to search for the value
• ‘return_array’ – the range or array to return
• ‘match_mode’ – optional; represents the match type; default is an exact match
• ‘search_mode’ – optional; represents the search behavior; default is “search from first value”

The ‘match_mode’ argument has 4 types:

• 0 – default; exact match; return error (#N/A) if no match
• -1 – exact match or next smaller value
• 1 – exact match or next larger value
• 2 – wildcard match

The ‘search_mode’ argument also has 4 modes:

• 1: default; search from the first value
• -1: search from last value
• 2: ascending binary search values
• -2: descending binary search values

philippospan

• Home
• YouTube Channel
• Tips 4 MS Word
• SystemPlus

There are many Functions available in Microsoft Excel . There are Functions , combination of Functions , more than what we can think of. Remember when we were looking for a Function to lookup and retrieve data within a Table , and we used to use the VLOOKUP function. VLOOKUP Function that I described in a previous post of mine and you can find here. Well now the successor of the VLOOKUP Function is the XLOOKUP Function . It is named like this because it can search bother vertically and horizontally. It allows us to search for an item in a Range or Table and return the matching result.

The XLOOKUP Function has Five Arguments , where the first three are required and the last two are optional. The first three necessary arguments are the following:

In the image below, I have entered Data into a Table . This Table contains information about TV Shows . I have the Rank number of the TV Show among with the Network that has created the TV Show and the percentage of Viewers watching the TV Shows . The Rank of each TV Show depends on the percentage of Viewers . Say, now that we want to find out all the information that is related with the Show that is in the Rank 96 . Information such as the Title of the Show , the Network that created the Show and the Viewers of the Show .

In the image below, I have selected the Formulas tab, and then from the Function Library area of the ribbon, I have selected the Lookup & Reference Category as shown. Once the Lookup & Reference category is selected the drop-down menu appears with all the available Functions , which in my example is the Function XLOOKUP .

What does the XLOOKUP Function do? It Searches A Range Or An Array For A Match And Returns The Corresponding Item From A Second Range Or Array. By Default, An Exact Match Is Used.

The syntax of the XLOOKUP Functions is the below:

XLOOKUP(lookup_value;array;return_array;if_not_found;match_mode;search_mode)

Once the XLOOKUP Function is selected, the Function Arguments dialog box appears as shown in the image below:

The XLOOKUP Function is separated in the following arguments:

For my example in this post the Arguments that are been used are the following:

Once we have filled in all the Arguments , we just press the Ok button, located at the bottom right corner of the Function Arguments and see the results. In the image, according to our Arguments , the result at Rank 96 , which we were looking to find the relative Data , the Title is 48 Hours , the Network is CBS and the Viewers are 4,475 . Three Results, in one.

If we double click on the Function in the actual Cell , we can see the whole Function in the Formula Bar as shown in the image below.

Below you can check out the video describing How To Use The XLOOKUP Function in Microsoft Excel?

One of the more exciting software features to be released in Microsoft Excel is the XLOOKUP function. Just looking at the name, you might think I did a typo and meant VLOOKUP. Actually, this is an entirely new spreadsheet function that makes looking up data easier with fewer limitations. In this tutorial, I’ll show you how to use XLOOKUP. (Includes example spreadsheet.)

In fact, Microsoft recommends that you use it over VLOOKUP or HLOOKUP. The catch is that this lookup function is not available to all Excel users.

How to Get XLOOKUP

To start, this new lookup function is only available with Microsoft 365 (Previously known as Office 365). So if you use one of the non-subscription versions of Microsoft Office or Excel, such as Excel 2019, you’re out of luck.

To check your availability, go to your Formulas tab and click Insert Function. The Insert Function dialog will open with all your functions. XLOOKUP should show under the Lookup & Reference category between VLOOKUP and another new function called VMATCH.

Why is XLOOKUP Better?

The question on most peoples’ minds is, what’s better? After all, who wants to learn something that doesn’t add value for Excel users. So here’s a shortlist I can see from playing around with various lookup scenarios:

• This function can work either vertically or horizontally. This is in contrast to VLOOKUP and HLOOKUP, which are mostly locked to their orientation.
• You’re not dependent on a sorted list.
• You don’t have to worry about the function breaking if you add or remove columns.
• It defaults to an exact match. If this is your preferred match type, then it’s one less argument you have to state.
• You’re not dependent on the first column.
• Your return array can be to the left of your lookup column.

There are more advantages in using wildcards, different search modes, and match modes like approximate matches. However, those are not covered in this introductory tutorial.

About This Example & Formula

For this XLOOKUP tutorial, I’ll be using a simple Excel workbook with 2 sheets. You can download it from the Additional Resources section.

My goal is to populate Column C with the correct Periodic Table number that will be returned from Column H.

By default, XLOOKUP will perform an exact match mode on my stated lookup value, the element name. To do this, I’ll search off the common field “Name,” which appears in both ranges.

I’ve also added a second tab to the workbook called “Horizontal.” It contains the same information, but the data is laid out differently so you can see the function’s additional versatility. This worksheet also has the formula added.

You’ll also note that I’m adding the formula using Insert Function and the Function Arguments dialog. I find this method easier when you’re learning a new function because it’s less likely to produce formula errors in Excel. But, there is nothing to prevent power users from using the formula bar and entering in a string like:

Breaking Down the XLOOKUP Function

The function has 5 arguments, but the last two are optional. These optional arguments are for “Match Mode” and “Search Mode.” Excel notes this by making the mandatory arguments in a bold typeface.

[A] The Lookup_value represents the cell Excel will use to find a corresponding value from the Lookup_array. For example, in this screenshot, we’ll be looking for “Californium” from cell B2. It is also noted in quotes to the right of the textbox.

[B] The Lookup_array represents the cell range a matching Lookup_value can be found. In this example, we’re telling the application to lookup Californium from cell B2 and find the same value in column I. I added the $ signs to make an absolute range.

[C] The Return_array represents where our answers can be found. We’re telling Excel that once it finds the matching Californium from column I to go across and find the value from column H. In this example, I’m looking in a column to the left. I’ve highlighted the match in yellow.

Once Excel matched Californium, it then looked up the Periodic No. The query returned 98, which shows at the bottom of the argument list and the Formula result = line at the bottom.

How to Use XLOOKUP

For this tutorial, I’ll be using Microsoft 365 and the xlookup-elements-examples.xlsx spreadsheet. You can find it in the Additional Resources section. The workbook has 2 sheets. One example is a common lookup, whereas the second one includes a horizontal lookup and the formula.

1. Download the spreadsheet from the Additional Resources section.
2. Select the Vertical worksheet.
3. Click in cell C2. This is where we will put the lookup formula.
4. Click the Formulas tab.
5. Click Insert Function. The Insert Function dialog appears.
6. In the Search for a function: textbox, type xlookup.

Pin

1. Click Go.
2. Click OK to accept XLOOKUP. A blank Function Arguments dialog appears.

✪ If XLOOKUP doesn’t appear, then you don’t have the function yet.

1. With your cursor in the Lookup_value field box, click cell B2. This is the field we want to match.
2. Tab to advance to Lookup_array. This is the cell range that has matching values for column B.
3. Select cells I2 through I25.
4. Press F4 to make an absolute range.
5. Tab to advance to Return_array. This is the range that contains our answers.
6. Select cells H2 through H25.
7. Press F4 to make an absolute range. You should see a result in the Formula result = line.
8. Click OK. You should see 98 in cell C2.

Pin

1. With your cursor in cell C2, drag the fill handle to copy the XLOOKUP formula down the column.

And don’t worry if you still want to use the VLOOKUP and HLOOKUP functions. Microsoft doesn’t have any plans to retire these functions. However, I’d be willing to wager that you’ll seldom use the other functions now that you know how to use XLOOKUP in Excel.

To use XLOOKUP with multiple criteria, you can concatenate lookup values and lookup arrays directly in the formula. In the example shown, the formula in H8 is:

XLOOKUP returns $17.00, the price for a Large Red T-shirt.

Note: XLOOKUP can handle arrays natively; there is no need to enter with control + shift + enter.

One of the nice advantages of XLOOKUP over VLOOKUP is that XLOOKUP can work with arrays directly, instead of requiring ranges on a worksheet. This makes it possible to assemble arrays in the formula, and push these into the function.

Working one argument at a time, the lookup value is created by joining H5, H6, and H7 using concatenation:

This results in the string “T-shirtLargeRed”.

The lookup array is created in a similar way, except we are now joining ranges:

The return array is supplied as a normal range:, E5:E14:

In essense, we are looking for the lookup value “T-shirtLargeRed” in data like this:

Match mode defaults to exact, and search mode defaults to first match, so XLOOKUP returns $17.00.

With boolean logic

While the syntax explained above works fine for simple “equals to” matching, you can also use boolean logic to construct a formula like this:

This is a more flexible approach because the syntax can be adjusted to use other logical operators and other functions as needed for more complex lookups.

If you have Excel 365, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

Exact Match

By default, the XLOOKUP function in Excel 365 performs an exact match.

1. The XLOOKUP function below looks up the value 53 (first argument) in the range B3:B9 (second argument).

2. Next, it simply returns the value in the same row from the range E3:E9 (third argument).

3. Here’s another example. Instead of returning the salary, the XLOOKUP function below returns the last name (replace E3:E9 with D3:D9) of ID 79.

Not Found

If the XLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the XLOOKUP function below cannot find the value 28 in the range B3:B9.

2. Use the fourth argument of the XLOOKUP function to replace the #N/A error with a friendly message.

Approximate Match

Let’s take a look at an example of the XLOOKUP function in approximate match mode.

1. The XLOOKUP function below looks up the value 85 (first argument) in the range B3:B7 (second argument). There’s just one problem. There’s no value 85 in this range.

2. Fortunately, the value -1 (fifth argument) tells the XLOOKUP function to find the next smaller value. In this example, the value 80.

3. Next, it simply returns the value in the same row from the range C3:C7 (third argument).

Note: use 1 instead of -1 for the fifth argument to find the next larger value. In this example, the value 90. The XLOOKUP function also works with unsorted data. In this example, there’s no need to sort the scores in ascending order.

Left Lookup

Instead of using INDEX and MATCH in Excel to perform a left lookup, simply use the XLOOKUP function. For example, take a look at the XLOOKUP function below.

Explanation: the XLOOKUP function looks up the last name and returns the ID.

Multiple Values

The XLOOKUP function in Excel 365 can return multiple values.

1. First, the XLOOKUP function below looks up the ID and returns the first name (nothing new).

2. Replace C6:C12 with C6:E12 to return the first name, last name and salary.

Note: the XLOOKUP function, entered into cell C3, fills multiple cells. Wow! This behavior in Excel 365 is called spilling.

Excel Fuzzy Lookup Add-In is used to match similar, but not exactly matching data. This function is often used instead of VLOOKUP, when we want to compare two columns which have very similar data, but not exactly the same. As an output, Fuzzy Lookup returns a table of matched similar data in the chosen column. In daily data manipulation, there is a common need to compare two same data sets where one of them comes from some external source and can be misspelled or typed incorrectly. In this tutorial, you will see how to install Fuzzy Lookup Add-In, prepare data and create a Fuzzy Lookup, which can be very useful in data consolidation and save a lot of time.

1. Installation of Fuzzy Lookup in Excel
2. Explanation of the function
3. Preparation of data for Fuzzy Lookup
4. Create Fuzzy Lookup

Fuzzy Lookup is not a standard Excel function, therefore you can’t find it in your standard tabs and buttons. In order to enable this function, Microsoft created an Add-In which can be downloaded from the following link:

After you have downloaded the installation file, you need to open it and install following instructions. Once you have installed the Add-In, next time when you open an Excel it will automatically import Add-In. As a result, you will get a new tab at the end of a Ribbon called “Fuzzy Lookup” and a button with the same name:

As mentioned in the intro of the article, Fuzzy Lookup is used when we want to match two sets of data (two tables), but we don’t have exactly the same values in matching fields. For example, we want to match two tables based on values in column “Name” and in a first table we have value “Michael Jackson”, while in a second table we have similar, but misspelled name “Michal Jackson”. In this case, if we use standard VLOOKUP function, it will not match this two values because it’s looking for the exact match. Using of Fuzzy Lookup solves this problem, by matching columns based on their similarity.

This is very often used when we get a table in Excel imported from some other source, or just manually copied and want to match it with another table with the same data sorted out. In most cases, a first table will have many typing mistakes and misspelled words and would first need to be cleaned manually to be able to Lookup it with our prepared table. This can be very time consuming and that’s the point where Fuzzy Lookup saves precious time. Please note that the matching columns must be formatted as text.

Before being able to do a Fuzzy Lookup, we need to format our data into tables. To do this select cells range, click on Insert tab and choose Table. After we have created two tables, they need to be named in order to be used in Fuzzy Lookup function. This is done by selecting the whole table and entering a name into a Name Box:

Now we have data ready for Fuzzy Lookup: As you can see on previous pictures, we have two tables: the first one (named Sales_Actual) contains data on actual sales per salesperson (Columns “Sales Person” and “Sales Actual”) and the second one contains data of target sales per salesperson (Columns “Sales person” and “Sales Target”).

Once we have formatted our data in Spreadsheet, we can start creating Fuzzy Lookup with two tables created. We can imagine that the first table is imported from some other data source and presents a report of sales per sales person, while the second table contains arranged table created in our Excel file which contains targeted sales for every person. In our example, we want to match these two tables based on column “Sales Person” and create a new table which will have all data aggregated (“Sales Person”, “Sales Actual”, “Sales Target”). We can see that the first table has some misspelled names and we want to match them with correct names in the second table based on their similarity.

Let’s now create an example of Fuzzy Lookup and explain how it works. First, we need to select a cell, which will be the first cell of a newly created table, then go to Fuzzy Lookup tab and click on Fuzzy Lookup button. We will get the following window opened on the right side:

To create our table, we have several steps to do:

In the first part of Fuzzy Lookup window, we need to choose two tables which will be matched. In our case left table will be “Sales_Actual” and the right table will be “Sales_Target”. After that, we need to choose the columns which we want to match and click on the button between them. In our example, we want to match tables based on similarity of columns “Sales Person”, so we will choose this column both in Left Columns and Right Columns. Once we do that, the table below will have one new row with these matching columns. In Output columns, we need to check columns that we want to be in a newly generated table: “Sales_Person”, “Sales_Actual” and “Sales_Target”.

There is also an option to choose field “FuzzyLookup.Similarity” which gives the percentage of similarity between two columns. In the end, we can choose Similarity Threshold (0-100%) which tells the function what level of similarity we want to match. After everything is set up, we can click go and get a table based on entered parameters:

As you can see in the picture, the new table is created from the first two chosen. It consists of 3 columns that we choose and column similarity which calculates the similarity of “Sales_Person” columns in the two tables in percentage. For example, “John Bryant” from the first table is matched with “John T. Bryant” from the second table as their similarity is equal to 92%. Also, names “Rachel Williams” and “Harry White” do not have similar values in the second table (based on similarity percentage – 50%), so no value is filled in “Sales Target” column for these two entries.

If we wanted to get these two values also in the matched table, we would have put smaller Similarity Threshold in Fuzzy Lookup window. In the following example, we put it at 0.2 (20%) which means that we want to match all names that have the similarity of 20% or greater. Here is the result:

The output now is the same table, but with included names “Rachel Williams” and “Harry White” because their similarity to “Jason J. Williams” and “Harrison L. White” is greater than 20%.

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.