lookup – HowtoExcel.net

How to Do a Fuzzy Lookup in Power Query

May 30, 2023

by howtoexcel lookup lookups power query

Power Query is a powerful data transformation tool in Excel that allows you to effortlessly connect to various data sources, cleanse and manipulate data, and unlock advanced functionalities such as fuzzy matching. By leveraging the inherent “Fuzzy Lookup” feature within Power Query, you can seamlessly compare and match similar values across columns or tables using intelligent fuzzy logic algorithms. This article will highlight how you can use this functionality to help consolidate data, improve accuracy, and correct mistakes.

What is the purpose of a fuzzy lookup in Power Query?

A fuzzy lookup in Power Query refers to a powerful feature that allows you to match similar or related records within your dataset, even if they contain variations or discrepancies. Unlike an exact match that demands identical values, a fuzzy lookup takes into account the degree of similarity between values using intelligent fuzzy logic algorithms.

Imagine you have a dataset containing customer names, and you want to compare it with another dataset to identify potential matches. However, due to typos, misspellings, or differences in formatting, the exact matches may not yield the desired results. This is where a fuzzy lookup comes to the rescue.

By using a fuzzy lookup, you can overcome these obstacles. Power Query evaluates the similarity between values based on factors such as spelling variations, phonetic similarities, transpositions, and even differences in word order. This flexible approach allows you to find connections between records that might have otherwise been missed and unmatched.

There are many benefits of fuzzy lookups. They enhance data accuracy and integrity by enabling you to identify related records that might have been entered inconsistently. They can consolidate information from different sources, harmonize data formats, and facilitate a comprehensive analysis of your datasets.

Fuzzy lookups are particularly valuable when dealing with large datasets, data integration, data cleansing, or any scenario where data inconsistencies are prevalent. They provide a robust mechanism to uncover hidden associations that might have otherwise resulted in incomplete data. Leveraging the power of fuzzy lookups in Power Query can significantly improve the quality of your data analysis.

What is the difference between an exact match and a fuzzy match?

An exact match refers to a comparison between values that must be identical in every aspect, including spelling, punctuation, and formatting. It requires an exact one-to-one correspondence between the compared values.

A fuzzy lookup, however, takes a more flexible approach by considering variations, similarities, and patterns within the values being compared. It utilizes fuzzy logic algorithms to calculate the degree of similarity between the values, allowing for differences in spelling, formatting, and other factors. Here’s an example:

Dataset 1: “Robert Johnson” Dataset 2: “Robert Johhnson”

In this example, an exact match would fail to identify a match due to the slight difference in spelling (“Johhnson” instead of “Johnson”). However, a fuzzy lookup would recognize the similarity between the values based on the fuzzy logic algorithms, identifying them as a potential match.

While an exact match demands complete identity, a fuzzy lookup offers a more lenient approach by accommodating variations, spelling differences, abbreviations, and even phonetic similarities. It enables the discovery of relationships and connections that might otherwise be missed, allowing for more comprehensive data analysis and data integration.

The limitations of a fuzzy lookup

While fuzzy lookups in Power Query offer a powerful mechanism for matching similar records, it is important to be aware of their limitations. Understanding these limitations can help you effectively address challenges and make informed decisions when utilizing fuzzy lookups in Power Query.

Performance Impact: Performing fuzzy lookups on large datasets can have an impact on performance. Fuzzy matching involves complex algorithms that analyze the similarity between values, which requires additional computational resources. When working with extensive datasets, it is advisable to consider the potential performance implications and evaluate whether optimization techniques, such as limiting the scope of matching or using more specific matching criteria, are necessary.
Configuring Fuzzy Matching Parameters: The success of a fuzzy lookup heavily relies on properly configuring the fuzzy matching parameters. Selecting the appropriate similarity threshold and adjusting other options, such as case sensitivity or accents, is crucial. However, finding the right balance can be challenging, as overly strict or lenient parameters may result in missed matches or false positives. It often requires experimentation and fine-tuning to achieve the desired level of matching accuracy.
Data Quality and Variations: Fuzzy lookups are highly dependent on the quality and consistency of the data being matched. Inaccurate or inconsistent data, such as misspellings, abbreviations, or incomplete information, can impact the effectiveness of fuzzy matching. While fuzzy lookups can handle some degree of variation, extreme discrepancies or inconsistent patterns in the data may hinder accurate matching.
Ambiguity and Multiple Matches: In certain cases, fuzzy lookups may encounter situations where multiple records match a single value, leading to ambiguity. This can occur when there are similar records or when the matching criteria are not precise enough. Dealing with such scenarios requires additional consideration and possibly manual intervention to determine the correct matches.
Sensitivity to Dataset Size and Complexity: The effectiveness of fuzzy lookups can vary depending on the size and complexity of the dataset. Extremely large datasets or datasets with high variability in the values being matched can pose challenges. It is important to assess the scale and complexity of the data and consider alternative approaches, such as data preprocessing or dividing the task into smaller subsets, to manage the impact on performance and improve matching accuracy.

While fuzzy lookups provide valuable capabilities for identifying similar records, it is essential to be mindful of these limitations. There can be a risk of relying too heavily on fuzzy matches which results in erroneous results. By understanding and addressing these limitations appropriately, you can maximize the benefits of fuzzy lookups in Power Query and make informed decisions when incorporating them into your data analysis workflow.

Steps to do a fuzzy lookup in Power Query

Here’s a detailed overview of how to perform a fuzzy lookup in Excel:

Step 1: Load your datasets into Power Query. Open Excel and go to the Data tab. Click on “Get Data” and select the appropriate option to load your datasets into Power Query. This could be from a file, a database, or any other supported data source. In this example, I have a couple of tables. One for the data entry, that contains misspellings. And another for the available values that users should have entered:

Step 2: On the data-entry table, select the Home tab and click on Merge Queries.

The merge queries window in Power Query.

Step 3: Select the other table and highlight the fields to merge. Leave the default join as a Left Outer and below it, select the option to Use fuzzy matching to perform the merge. Upon doing this, you should see Power Query indicate that it has found more matches.

You can also open up the fuzzy matching options to select whether you want the matches to be case-sensitive, and if you want to allow it to match by combining different text parts together. You can also limit the number of matches and set the similarity threshold.

Specifying the fuzzy matching options in Power Query.

Once you’re okay with the selections, you can click on OK.

Step 4: Now, open up the and expand the table that has been merged. This will retrieve the matched values.

A Power Query table after merging queries.

If everything is matched correctly, you can go ahead and click Close & Load to get the data back into Excel. If there are issues, you may want to go the previous steps to check your fuzzy matching rules, and perhaps adjust the sensitivity of the matches.

A Power Query table after merging and expanding the values.

Using a Transformational Table to help fuzzy matching

Fuzzy matches don’t always work. In some cases, you’ll need to create a transformational table to help guide Power Query. Here’s an example of when a fuzzy lookup won’t work:

Two tables in Excel with similar values.

These names are similar looking and there is a big opportunity for overlap. Even when using a low sensitivity threshold, it only matches 3 of the 6 names:

Power Query's fuzzy matching only finds three out of six possible values.

The one way to definitively fix this is to create a Transformational Table for Power Query. What this does is create mapping rules. The table needs to include a ‘From’ column and a ‘To’ column such as this:

A transformational table in Power Query.

Now, when you go back to the Merge Queries dialog and adjust the Fuzzy matching options, you can specify that this is the table you want to use:

Using a transformational table in Power Query when specifying the fuzzy matching rules.

Having this table will help Power Query understand which values are related to one another. It eliminates the guesswork and can ensure everything is mapping properly. It requires a bit of extra work but it can save time in the long run. Now when using this transformational table, it matches all of the values correctly:

Power Query matches all the values after using a transformational table.

If you liked this post on How to Do a Fuzzy Lookup in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

15 Excel Functions Accountants Should Know

March 28, 2023

by howtoexcel accounting advanced average averages beginner count countif dates functions index intermediate lookup lookups match sum sumif sumifs unique vlookup

If you’re an accountant, you know that working with large amounts of data can be a daunting task. But with Excel, that work can get a whole lot easier and more efficient. Understanding Excel’s advanced features and functions can improve productivity, reduce errors, make your work more accurate, and most importantly — save you time. Below, I’ll go over some of the most important Excel functions that accountants should know, and provide examples of how to use them. For this example, I’ll use the following spreadsheet. Feel free to download it and follow along with the calculations.

1. SUM

The SUM function is a basic but essential function in Excel. It allows you to add up a range of values, which is helpful when calculating totals, such as revenue, expenses, and profits. Suppose you have a spreadsheet with sales data. In the above example, the total sales are in column G. If you wanted to sum up the entire column, the formula would be as follows: =SUM(G:G)

2. AVERAGE

The AVERAGE function calculates the average of a range of values. It is useful when analyzing data and preparing financial statements. In the above example, suppose you wanted to calculate what the average sale was. To do this, you can just use the AVERAGE function on column G, similar to the SUM function before. Here’s the formula: =AVERAGE(G:G)

3. IF

The IF function allows you to test a condition and return one value if the condition is true and another value if the condition is false. This can be useful because it can send your formulas to the next level. By knowing to use the IF function, you could also use SUMIF, AVERAGEIF, and many other functions that involve an if statement. In the above example, let’s say you only wanted to know if a value in cell M2 was part of the Motorcycles product line. The formula would be as follows: =IF(M2=”Motorcycles”,1,2). If it is part of Motorcycles, you would have a value of 1, otherwise, it would be 2.

4. SUMIF

By knowing the SUM and IF functions, you can combine them together with SUMIF, which is an incredibly popular function. It gives you a quick way to tally up the totals that meet a criteria. For example, let’s say you want all sales that relate to the Motorcycles category. The formula for that would be as follows: =SUMIF(M:M,”Motorcycles”,G:G). If the criteria is met in column M, then the formula will sum up the corresponding values in column G. There’s also the super-powered SUMIFS function, which allows you to combine multiple criteria.

5. EOMONTH

The EOMONTH function calculates the last day of the month for a specified number of months in the future or past. It is useful when working with data that is organized by date. For accountants, this can be useful when you’re calculating when something is due. Let’s say in this example, we need to calculate the date orders need to go out on, and that needs to be the end of the next month. Using the ORDERDATE field in column H, here’s how that calculation would look in the first cell, which would then be copied down for the rest: =EOMONTH(H2,1)

6. TODAY

The TODAY function is helpful for accountants in calculating deadlines and knowing how many days are remaining or past a certain date. Suppose that you wanted to know how many days have past since the ORDER DUE DATE that was calculated in the previous example. Rather than entering in a static date that every day you would need to change, you can just use the TODAY function. Here’s how a formula calculating the days since the deadline for the first cell would look like, assuming the due date is in column N: =TODAY()-N2. The next day you open up the workbook, the calculations will update to reflect the current date; there’s no need to make any changes. There are many more date calculations you can do in Excel.

7. FV

The FV function calculates the future value of an investment based on a fixed interest rate and a regular payment schedule. You can use it to calculate the future value of an investment or savings account. Let’s say that you wanted to save $10,000 per year and expect to earn a return of 5% per year on that investment. Using the FV calculation, you can do that with the following formula: =FV(0.05,5,-10000). If you don’t enter a negative for the payment amount, the formula will result in a negative value. You can also specify whether payments happen at the beginning of a period (1) or end (0 — this is the default) with the last argument in the function.

8. PV

The PV function lets you do the opposite and work backwards from a future value to the present. Knowing that the calculation in example 7 returns a value of $55,256.31, that can be used in the PV calculation to check our work: =PV(0.05,5,10000,-55256.31). The formula returns a value of 0, which is correct, as there was no starting value in the FV calculation.

9. PMT

The PMT function calculates the periodic payment required to pay off a loan with a fixed interest rate over a specified period. It is helpful when determining the monthly payments required to pay off a loan or mortgage. Let’s take the example of a mortgage payment where you need to pay down $500,000 over the period of 30 years, in monthly payments. At a 5% interest rate, here’s what the payment calculation would be: =PMT(0.05/12,12*30,-500000,0). Here again the ending value needs to be a negative to avoid a negative value in the result. And since the payments are monthly, the periods need to be multiplied by 12 and the interest rate is dividend by 12.

10. VLOOKUP

The VLOOKUP function allows you to search for a value in a table and return a corresponding value from another column in the same row. It’s one of the most common Excel functions because of how useful and easy to use it is. It is helpful when working with large data sets and performing data analysis. Let’s suppose in this example that you want to find the sales related to order number 10318. The formula for that calculation might look like this: =VLOOKUP(10318,C:G,5,FALSE). In a VLOOKUP function, you need to specify the column number you want to extract from, which is what the 5 represents. If you’re using Office 365, you can also use the newer, flashier XLOOKUP function. I put VLOOKUP on this list because it’ll work on older versions of Excel — XLOOKUP won’t.

11. INDEX

The INDEX function allows you to return a value from a data set by specifying the row and column number. It’s also helpful if you just want to return data from a single row or column. For example, the sales column is in column G. If I know the order number is on row 20 (which relates to order number 10318), this formula would do the same job as the VLOOKUP in the previous example: =INDEX(G:G,20,1).

12. MATCH

The MATCH function allows you to find the position of a value within a range of cells. Oftentimes, Excel users deploy a combination of INDEX and MATCH instead of VLOOKUP due to its limitation (e.g. VLOOKUP can’t extract values to the left of the lookup field). In the previous example, you had to specify the row belonging to the order number. But if you didn’t know it, you could use the MATCH function within the INDEX function. The MATCH function would look like this: =MATCH(10318,C:C,0). Placed within an INDEX function, it can replace the argument where in the previous example, we set a value of 20: =INDEX(G:G,MATCH(10318,C:C,0),1). By doing this, you have a more flexible version of the VLOOKUP function. You can also create dynamic formulas using INDEX and MATCH that use lookups for both the column and row.

13. COUNTIF

The COUNTIF function allows you to count the number of cells in a range that meet a specified condition. Let’s count the number of values in the data set that are Motorcycles. To do this, you would enter the following formula: =COUNTIF(M:M,”Motorcycles”).

14. COUNTA

The COUNTA function is similar to the previous function, except it only counts the number of non-empty cells. With no criteria, it is helpful to just the total number of values within a range. To calculate how many cells are in this data set, you can use the following formula: =COUNTA(C:C). If there are no gaps in data, then the result should be the same regardless of which column is used. And when combined with the UNIQUE function, you can have an easy way to count the number of unique values.

15. UNIQUE

The UNIQUE function returns a list of unique values within a range, and it’s a much easier method than the old-school way of extracting unique values. If you wanted to extract all the unique product lines in column M, you would enter the following formula: =UNIQUE(M:M). If, however, you just wanted to count the number of unique values, you could embed it within the COUNTA function as follows: =COUNTA(UNIQUE(M:M)). You can adjust your range if you don’t want to include the header.

This is just a sample of some of the useful Excel functions that accountants can utilize. If you are familiar with them, you’ll put yourself in a great position to improve the efficiency of your workflow and make your spreadsheets easier to use. Plus, you can confidently say that you are highly competent with Excel, which can make your resume more attractive and make you better suited for accounting jobs that require advanced Excel skills — and there are many of them that do!.

If you liked this post on 15 Excel Functions Accountants Should Know, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Use MAXIFS and MINIFS to Find Values Within a Range

January 24, 2023

by howtoexcel lookup lookups max min Uncategorized

Whether you’re using VLOOKUP or XLOOKUP, one limitation you’re going to face with those functions is that you can’t search within a range or use multiple criteria in your lookups. For example, suppose you’re looking at a stock’s history and wanted to know the last time it fell within a certain price range. You wouldn’t be able to do that with the aforementioned functions. But there is a way to accomplish that, using either MAXIFs or MINIFs. Here’s how.

Using MAXIFs and MINIFs as a lookup

With the MAXIFs and MINIFs functions, you are extracting either the smallest or largest data point in a range. And since you can apply multiple IF statements within these functions, you have the possibility to use multiple criteria. In the following example I have a list of Alphabet’s historical stock price going back multiple years:

Let’s suppose I wanted to find the last time that the stock was trading between $70 and $80. This is how the formula would look, assuming the date is in column A and the closing price is in column B:

=MAXIFS(A:A,B:B,”>=”&startprice,B:B,”<=”&endprice)

In column B, I have two criteria, one to check if the value is greater than or equal to the startprice variable ($70), and another to see if the value is less than or equal to the endprice variable ($80). Whenever that criteria is met, the value from column A is returned. And since the function is taking the maximum of those values, it will return the latest date in column A (i.e. the most recent, or the one closest to today’s date). If the date values were sorted in descending order rather than ascending order as they are above, then I would use the MINIFS function to get the same result.

Using the formula, it tells me that the last time Alphabet’s stock price was between $70 and $80 was on Oct. 29, 2020. And when looking at the range, it’s evident that looks to be correct:

Without the use of ranges and utilizing MAXIFS, this would have been a much more difficult process. There are multiple ways to approach a lookup and it ultimately depends on the situation and what you need to accomplish. MAXIFS and MINIFS are particularly useful when working with dates. But in other situations, you may need to use a different function instead.

If you liked this post on Use MAXIFS and MINIFS to Create a Lookup With Multiple Criteria in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Do a Lookup with Multiple Criteria in Excel

November 2, 2022

by howtoexcel countif index lookup lookups match sumifs vlookup

Most Excel users likely know how to do a simple VLOOKUP and pull in data where a single field is matched. But what about when you need to match multiple fields? That can be a bit more challenging to pull off and below I’ll show you a couple of ways you can achieve this.

In the following data set, there is information on airports and delay times by specific carriers. Given that there are so many fields here, a simple lookup wouldn’t be helpful here as you need to factor in multiple criteria.

Data showing carrier delays at airports across the world.

Using a consolidated unique key

If you are able to create an additional column in your data set, then one option you have available is to create a unique identifier. For example, if I concatenate the carrier code, airport code, month, and year, I can have a key that I could use in a lookup formula. Here’s how that key could look:

The important thing to remember here is that your key should be unique enough so that there is only a single match. For example, if I didn’t include the year and my data includes multiple years, I could potentially have multiple matches for a combination of month, carrier, and airport code.

One way you can test for this is by using the COUNTIF function. This will tell you if there is more than 1 instance of a value. If my key is in column B, here is how that COUNTIF function could look:

=COUNTIF(B2,B:B)

If any of the formulas return a value of more than 1, then that will tell you there is a duplicate value:

Using the COUNTIF function to determine if a key is unique.

You can use filters to see if there are any values greater than 1 on this list. If there are, then you know you need to adjust your key to add additional criteria so that there are no duplicates. Once you have this accomplished, then you can use this within a VLOOKUP or a combination of INDEX & MATCH. You would just need to use a search criteria that follows the same construct.

Using multiple criteria in a SUMIFS function

Another way you can lookup multiple fields is by using SUMIFS. You can sum the data but you don’t have to. After all, if your criteria is unique, a SUMIFS function would only be summing a single value. And in that sense, it can work similar to a lookup. Using this approach, you don’t have to create any additional columns to make it work.

Here’s how a formula in my data set would look like if I wanted to extract the carrier_delay value for Delta Air Lines (carrier code DL) at the Atlanta airport (airport code ATL), for July 2022:

=SUMIFS(Q:Q,A:A,2022,B:B,7,C:C,"DL",E:E,"ATL")

Q is the value I’m extracting, column A relates to the year, column B to the month, column C to the carrier code, and column E to the airport code. Since there is only one corresponding value when filtering for all these combinations, I know my SUMIFS function is only pulling in a single value, and thus, working effectively as a lookup function.

With this approach there is some risk if you don’t first vet your criteria and to check for duplicates. And just to be safe, you’ll probably want to do a check for that before relying on this calculation.

If you like this post on How to Do a Lookup with Multiple Criteria in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Do a Picture Lookup in Excel

January 7, 2022

by howtoexcel with No Comment lookup lookups

A lookup is one of the more common things you can do in Excel. Whether you’re using VLOOKUP, a combination of INDEX and MATCH, or the new XLOOKUP, there are no shortage of ways to accomplish it. However, in this post, I’ll go over how you can do a lookup that involves pulling in a picture. It’s a bit more complicated to set up but once you’ve figured it out, it should be a breeze.

Step 1: Create a table of the images you would like to use

I’m going to create a tab for images that has two columns — one for the name of the image, while the other will hold the image itself. I’m going to make the rows wide, with a height of 60 just to make sure the cell can fit the entire image. In this example, I’m using some popular corporate logos:

Step 2: Setup the named ranges

Next, I’ll create named ranges in column B that match the values in column A. In the example above I don’t have any spaces but if I did, I would replace them with an underscore to make sure there are no gaps. In addition to creating a named range for each individual logo, I will also create a named range that contains all the values in column A. This way, I can use this as a dropdown later on to select which logo I want to select.

I’ll create a named range called ‘Companies’ for these options. When using data validation, I’ll just enter the following as my list options:

Data validation list using the company names as options.

I’ll add this on to another sheet. My selection here will determine which image to pull.

Step 3: Creating another image for the lookup

I also need to create a picture that will pull the desired image. To do this, I can just copy any one of the images I inserted in the first step.

Step 4: Creating a named range for the selection

I’m going to create another named range, this time, I won’t be selecting a cell but I will go through the Formulas tab and select Name Manager where I’ll see all the named ranges I have set up thus far:

Click on the New button. And here, I’ll need to use the INDIRECT function to reference the cell that contains the company value that was selected through the dropdown. In my example, that is cell H8. My named range, which I’ll call, ‘CompanySelected’ will look as follows:

Now, for the picture that is acting as your lookup, select it, and set the cell equal to the named range of ‘CompanySelected’ :

I can adjust the size as large as necessary. And now, when I change my dropdown option, the image will automatically update:

If you liked this post on How to Do a Picture Lookup in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Use VLOOKUP With Multiple Sheets

October 20, 2021

by howtoexcel with No Comment indirect lookup lookups vlookup

VLOOKUP is a popular function in Excel because of how powerful and easy it is to use. You can even use it to look up values on different sheets. And in this post, I’ll show you how you can do so dynamically so that you don’t always need to be adjusting your formula.

Why you might want to use multiple sheets in the first place

There are good reasons to use multiple sheets in your workbook. The first is that it makes it easier to organize your data. The second is that it can make your formulas more efficient. For example, running calculations on a tab where you have tens of thousands of rows would not be optimal and if you can split that up into smaller worksheets, you can make your formulas smaller in scope.

In my example, I’ve downloaded historical unemployment numbers by country. And rather than putting that data all into one sheet, I’ve created multiple tabs for countries. Not all of them, but just a few that I want to do lookups on:

Each tab is named after the country abbreviation in the data to make it easy to know what’s in each sheet. And inside each sheet is data that is formatted in the same way:

Historical unemployment data for the United States.

Creating the formula

If I just wanted to lookup the value for the United States’ unemployment rate from 1955, my formula would look as follows:

=VLOOKUP(1955,USA!D:E,2,FALSE)

I could replace 1955 with a cell reference. But other than that, this is in essence what the formula in its simplest form would look like. I’m looking up the USA tab as indicated by the ! symbol that comes after the sheet name. You don’t actually need to enter the ! mark. You can just type in the formula and then when you get to the lookup range, jump over to that tab and select your range — Excel will automatically add the exclamation mark for you.

While this formula works, it isn’t versatile. If I wanted to look up a different tab, I would need to change the reference, since it is hardcoded.

Making the formula dynamic

I have created named ranges for the country and year values:

What I want to be able to do is change any one of them and for my lookup formula to extract the correct value. The key to making this work is by including the INDIRECT function. With that, I can reference the specific range I need and use a dynamic tab name. Inside the INDIRECT function, I can concatenate the country value with the range:

INDIRECT(Country&”!D:E”)

But this on its own only specifies a range. I need to include it in the lookup formula for it to work:

=VLOOKUP(Year,INDIRECT(Country&”!D:E”),2,FALSE)

‘Year’ and ‘Country’ are the named ranges that I have used above. The key thing to remember is the exclamation mark that comes afterward and the range. By doing this, now I can change my formula to automatically pull from the correct tab while also looking up the year. It avoids me having to change the formula manually every time I want to use different tabs. It returns the same value as if I were to enter it myself:

If you liked this post on How to Use VLOOKUP With Multiple Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Use the New XLOOKUP Function

October 15, 2019

by howtoexcel with One Comment functions index intermediate lookup lookups match vlookup

Forget using VLOOKUP or even INDEX and MATCH, Excel users can now use XLOOKUP!

Knowing how to use VLOOKUP has become almost a basic skill for Excel users. If you’re an intermediate or advanced user, you probably use INDEX & MATCH because of the limitations that are inherent with just using VLOOKUP. Since VLOOKUP can only return values to the right of what the value that you’ve found, it’s a less-than-optimal formula. You can either re-arrange your data, or you can use INDEX & MATCH. It’s a more flexible solution, but it’s also not ideal. After all, you’re now combining multiple Excel functions into one.

Enter: XLOOKUP

XLOOKUP is the solution that Excel users have been looking for…for decades. What the function does is allow you to do what was possible with INDEX & MATCH all in one simple formula.

Let’s go over it with some sample data on the world’s largest cities:

list of the largest cities in the world — Data courtesy of Wikipedia

Doing a regular lookup vs XLOOKUP

Here’s how my formulas would look like if I wanted to return the Country using a value from the City field:

vlookup and index match doing a regular lookup

In the above example, E5 refers to the capital city value. While the INDEX & MATCH combination works, it may not be the easiest for novice users who aren’t comfortable with nesting functions. Here’s how the same calculation would look using XLOOKUP:

It’s a much simpler solution. The first argument takes the value you want to look for, followed by the range where you want to search for it, and then the range that you want to extract the corresponding value from. There’s no need to enter a column number the way you do with VLOOKUP, nor is there a need to add another function.

There are optional arguments you can use including how you want to match (see the next section). You can also choose the direction that the lookup goes, in case you don’t want to look in the same order as your data:

Using wildcards in XLOOKUP

Like with the other functions, you can also incorporate wildcards into XLOOKUP as well. Wildcards work similarly among all three formulas, but the key difference is that XLOOKUP has multiple arguments for its fourth (optional) argument which dictates how you want the data extracted. Entering ‘2’ will tell the function that you want to use a wildcard. Below are the options for the match_mode argument (optional):

0: exact match
-1: exact match or next smaller item
1: exact match or next larger item
2: wildcard character match

Here’s a comparison of how you’d get the same result using all three functions using a wildcard:

using wildcards in xlookup vlookup and index match

The logic is the same in the sense that you’ll want to use a wildcard character like * around the term you’re trying to find a match of. In the above example, I used the * around the entire wildcard, and it returned the population for New Delhi in that example.

XLOOKUP here is actually a bit more complicated as with the other functions you didn’t need to specify that you were using a wildcard. Taking out the ‘2’ from the argument would result in XLOOKUP yielding an #N/A error. However, it could be that doing this will make it more efficient.

Finding the closest matches

One of the other options for the matches mentioned above were finding the next smaller or next larger matching items if an exact one wasn’t found. A good example of this is where you’re looking for something like a tax rate where you won’t find every possible income level that someone might enter and you need to ensure that it falls into the correct range.

Here are some sample categories:

If I entered an amount of $17,000, it should put me in Tier 3, since that would be the threshold I would have reached under this hierarchy. Here’s an example of how this would be calculated in the three functions:

doing a lookup for tax brackets using xlookup vlookup and index match

All three formulas were able to return the same tier correctly, however, INDEX & MATCH is a bit more cumbersome again due to having multiple functions within it.

The advantage that XLOOKUP has here is that I can select the category that’s either directly below or above the amount I enter, effectively rounding up or down, simply by changing the fourth argument between a ‘1’ (exact or next largest item) to a ‘-1’ (exact or next smallest item).

This is not possible with VLOOKUP, and in order for this to be able to work with INDEX & MATCH, I’d have to change the order from ascending to descending. But what’s impressive is that XLOOKUP is able to find the correct category even if the values are not in any sort of order at all.

Have a look at what happens when I try to completely destroy any sort of hierarchy:

This is an absolutely dreadful hierarchy that’s not consistent in any way possible. Do the formulas have any chance of getting it right? Here’s how the results looked:

xlookup index and match doing a lookup for next smallest category

Both the INDEX & MATCH as well as the XLOOKUP formulas were looking for the closest matches. INDEX & MATCH returned the lowest tier, which technically was incorrect since $17,000 came in higher than $10,000, which was Tier 2. And XLOOKUP, despite the mess of a hierarchy, was still able to pull out the correct group.

Ultimately, you never want to organize your data in such a horrible way, but this helps demonstrate just how strong XLOOKUP is, to be able to still come out with the correct calculation.

And just for fun, let’s flip the formulas around, this time looking for an exact match or the next largest category:

xlookup index and match doing a lookup for next largest category

There wasn’t a Tier 3 in my incomplete table, but XLOOKUP still found the next largest Tier which was at $25,000 – Tier 5. INDEX & Match found its way into Tier 7.

Creating a dynamic formula

One of the great things about INDEX & MATCH is that you can index an entire database and then dynamically change which column you want to extract from based on a selection and not have to update the range in the formula. For example:

Why would you want to do this? The beauty of it is that you can change what value you extract based on your selection. Since you’re doing a match, it will look for that field and adjust the column accordingly using the OFFSET function:

You can do this in XLOOKUP as well, and here’s how that formula would compare to index and match:

The XLOOKUP formula is a bit more complicated as it needs two ranges, and thus, two OFFSET functions are needed. In the INDEX & MATCH combination, only one OFFSET function is needed as it only requires a column number for one of its arguments. Either way, you still need to be familiar with using OFFSET so it’s probably not a dealbreaker if XLOOKUP is a bit longer.

Great, so how do I get XLOOKUP?

There are two things you need to be able to get access to XLOOKUP:

Office 365
Enrollment in the Office 365 Insiders Program

It’s not an exclusive club or anything, all you have to do is to follow the steps outlined here. By selecting the ‘Insider’ option rather than Monthly, you’ll get more frequent updates and changes. Once you’ve got it set up, then it’s just a matter of waiting for the updates to roll out to you. There’s, unfortunately, no notification, I’d just suggest checking every now and then to see if XLOOKUP shows up in your functions list.

Caveat

One of the things you should remember, however, is that while it may be great to use XLOOKUP, old versions of Excel won’t have access to this flashy new function. And so it’s important to still be familiar with using VLOOKUP and INDEX and MATCH.

If you liked this post on How to Use the New XLOOKUP Function, please give the site a like on Facebook. Also be sure to check out our templates section. You can also follow us on Twitter and YouTube.

Custom Function That Does Multiple Lookups

June 20, 2016

by admin with No Comment functions lookup vba

MatchThis function

This function works as if you were doing multiple lookup statements in one formula. The example I’m going to use is if you wanted to look at your credit card statement and from the description determine what vendor it is.

For this function to work I need to create a named range of all the values (e.g. possible vendors) I want to cycle through to compare the string (e.g. credit card data) against. The named range needs to be called LookupList. Below you will see the LookupList I created. (For more on named ranges, see this post)

I’ve added a header but that is not necessary. As long as the list is a named range called LookupList. The adjacent column is the value that will be returned if the value in the LookupList is found. You need to ensure this column is also filled in or else the result of the formula will be blank, regardless if there is a match.

When I run the custom function, the function will cycle through the LookupList from top to bottom to see if one of those values is in the cell I am using the formula on and if so, return the related result. For that reason, the LookupList also needs to be in descending order, to avoid a premature match (e.g. finding Store A before the function finds Store ABC)

Column A is an example of data from a credit card or other source that may have various characters before and after what you are looking for. You could use the MID function to extract that data but that will only work if that data is consistently arranged the same way. It might be, but using this function it won’t matter and it will just look if any of the values in the LookupList are contained in the string, regardless any other characters before and after.

Column B is the MatchThis function. And since the LookupList is already defined the only argument is the data that you want to look at, which in this case is column A. In column B2 the formula is simply =matchthis(A2). Because it matches Store A, it returns the value A (from the results column).

Below is the code for this function:

————————————————————————————————————————————–

Function MatchThis(matchcell As Range)

Application.Volatile

Application.Calculate

Dim LookupList As Range

Dim c As Range

‘Identify the range of cells you want to compare against. The lookuplist is what will be compared against and the column to the right of it will be the output

Set LookupList = Range(“LookupList”)

‘Go through each of the cells looking for the criteria in cell c, and if it matches, pull the value from the next column

For Each c In LookupList

If InStr(1, matchcell, c, vbTextCompare) > 0 Then

MatchThis = c.Offset(0, 1)

Exit Function

End If

Next c

End Function

————————————————————————————————————————————–