Do you want to be able to use a VLOOKUP with a pivot table? While there isn’t a way to natively do so, there is a way you can make it look as though your pivot table has a lookup function within there, and make it so that it expands along with your data. Suppose you have the following pivot table, which shows employee spending:
Let’s say we want to look up the department that the employee belongs to, based on the following lookup table:
We can’t create a field that does a lookup within a pivot table, but we can make it look as if that’s what we are doing.
Copy your pivot table formatting to make it look as though you’ve added another field
I can create a field called ‘Department’ directly next to my pivot table. And what I can do to make it look as though it’s a continuation of my pivot table is to use the Format Painter so that I can copy the formatting over. To do this, simply select the formatting for the pivot table header, click Format Painter, and then click on the new field. Now it looks as though it’s the same format as your pivot table:
The one drawback is that if you adjust your pivot table, you’ll need to update the formatting. You’ll also want to make sure you don’t expect your pivot table to expand — i.e. you won’t be adding any more fields to expand it horizontally. If you do so, you’ll encounter an error saying that there isn’t enough room for your pivot table. In that case, you can insert a column. But ideally, you would set this additional field once you’ve added all the fields you plan to use in your pivot table.
Using the VLOOKUP function next to your pivot table
The next step is to use the VLOOKUP function the way your normally would. With the employee name in cell A2, and my lookup table in columns F:G, I can set my formula up as follows:
=VLOOKUP(A2,F:G,2,FALSE)
But this is still not ideal as copying this formula down to far will show errors for both grand totals and blank values:
The solution here is to add an IF statement before the VLOOKUP function. In the below example, my formula is checking for both a blank value and a ‘Grand Total’ value. If either criteria is met, it returns a blank:
Now I can copy my formula down and the formula won’t return a value when the value in column A is blank or is a grand total:
Now it appears as though my lookup function is dynamic and automatically adjusting based on my pivot table selections.
Adding the field to the data set is the ideal solution
Creating a field by adding a formula next to your pivot table can work if your table never expands. But if it might need to, a more versatile option is to simply add the field into your original data set and do the lookup there.
In this data set, I’m missing the department field. But if I add the VLOOKUP formula here, I can pull in the department values right in there. The formula is setup the same and by doing it this way, I can add the field directly to my data set:
Now, when I update my pivot table I can directly add the department field right into the Rows section:
Then, my pivot table shows the additional field, and I won’t run into any issues whether I need to add rows or columns:
In some cases, you might just want a quick way to do a lookup and not adjust the data set, in which case the first method can be preferable. But if you are able to add the field directly into your data set, that is the ideal approach.
If you like this post on How to Use VLOOKUP with Pivot Tables, 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.
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.
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.
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:
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.
A VLOOKUP function is simple: you enter criteria and select a range that it should extract values from. However, there are multiple reasons why your VLOOKUP cannot find the correct value. Below are seven common reasons your formula may not be working as you expect it to.
In this example, I’m going to use the following list of automobile makes and models:
I’m going to use a lookup formula to find a model and identify the make. The model and make values are in columns A and B on my sheet, respectively. And my lookup value is cell D2. The correct formula would be as follows:
=VLOOKUP(D2,A:B,2,false)
There are four arguments, and here are some of the common ways you could mess this formula up:
1. You didn’t enter the correct range
=VLOOKUP(D2,A1:B100,2,false)
A common error is that you enter a range that doesn’t cover the area that you need. For example, in the above example, the formula goes only to row 100. But if the value you want is on row 101, the lookup formula won’t work and you’ll get an #N/A error.
Another issue could be the following:
=VLOOKUP(D2,B:C,2,false)
In this situation, the formula is starting at column B but the model list is in column A. That all but guarantees that it won’t find the right value. In a VLOOKUP formula, you are looking up the leftmost column in your range. If the model values are in column A, that’s where the formula needs to start from. In the above formula, it will be looking for the values in column B, which isn’t correct.
2. You are extracting values from the wrong column
=VLOOKUP(D2,A:B,3,false)
The range is fixed in this situation but the problem here is that you’re looking for the value in the third column. There are only two that are in the formula. In this instance, you’ll get an error because you’re trying to access a value that’s outside of the range you provided.
=VLOOKUP(D2,A:B,1,false)
The range is correct but here the problem is now you’re referencing the first column. Although you’ll get a value, it will be the same one you input, since the formula is looking at column 1.
One of the common issues with lookup formulas is that people are referencing column numbers that can change over time as they expand their data set. Those numbers won’t automatically adjust when you insert new columns.
There are a couple of workarounds for this. One is to use convert your data set to a table and reference an actual table column. Another is to use the MATCH function to find the column number that you’re looking for. Alternatively, you could use a combination of INDEX & MATCH.
3. You misspelled the value you’re looking up
One of the easier mistakes to spot is when you’ve misspelled the name of what you’re looking up. If in your lookup formula you want to find “Accord” but instead type in “Accorrd” then you’ll end up with another #N/A error. However, if you have a data set where the lookup values could be similar, the danger there is that you could potentially not get an error and instead return the value that relates to a different lookup value. The best way around this is to avoid hardcoding your lookup values. That way, it can be easier to spot errors and it’ll be easier to adjust them.
The reverse is also a problem: if your lookup column contains a misspelling. In that situation, even though the value you’ve looked up is spelled correctly, your lookup could still fail.
4. Your value has extra spaces
One of the trickiest mistakes is where your data isn’t misspelled but contains an extra space somewhere. Just by looking at a cell, you may be able to spot when there’s a leading space. But if there’s a trailing space, that’s tougher and you may not notice until you actually go in and try to edit the value. Whether it’s an extra space or the value is misspelled, that can impact your ability to find a match.
The way to check your data is by using the RIGHT function (or the LEFT function if you want to confirm the first character). If you enter the following formula to reference D2 (the lookup value), it will return the last character in the cell:
=RIGHT(D2)
If it returns a blank value, you’ve found your problem. Similarly, you can use this formula on your lookup list to see if any values have extra spaces. This is something you’ll want to do before creating your lookup formula. Making sure your data is good to go and clean with no trailing spaces can save you from encountering these issues later on.
Removing blank spaces can be easy but sometimes it can be tricky as not all blank values are the same.
5. Your value is reading as the wrong data type
In this example, the lookup is a text value. However, one potential error happens when you’re looking up a number that is stored as text. That can also result in no match being found. A good way to spot this error is to see if your data aligns to the left or the right by default. If you have no formatting applied, text should align to the left, while numbers will shift to the right. Another way you can check if something is reading as text or a number is to use the ISTEXT function. To convert a number stored as text into a number, multiply it by 1.
6. You search for an approximate match when you want an exact match
In most cases, you’ll probably want an exact match from your lookup formula (i.e. you’ll set the last argument to FALSE). The one exception I’ve found to be most useful is when you’re dealing with numbers and ranges. With tax brackets, for example, you’d be looking to see what range a value falls into versus an exact match.
In error #1 on this list, if you set the last argument to TRUE and looked for an approximate match, you would get a result, it just may not be the one you were hoping for.
7. You’ve sorted your formulas and they’re not correct anymore
A frustrating problem can be when you’ve entered your formula correctly but when you sort your formulas, they’re now referencing the wrong cells. In this case, you’re dealing with multiple lookups at a time. For example:
The lookup values are correct but if they get re-sorted, they’re now referencing the wrong values and the lookup results are incorrect:
After sorting column D in ascending order, the formula is incorrectly saying that Acura makes the Pilot and that there’s a BMW Cherokee. Here’s what the formula looks like in cell E2 before sorting:
=VLOOKUP(Sheet1!D2,A:B,2,FALSE)
The error is in the sheet reference. Since it’s including Sheet1!D2 instead of just D2, the value isn’t automatically updating when resorting. Excel automatically inserts the sheet referencing if you’re editing a formula and jumping from one sheet to another. The formula is locking that value in place, even when you sort. Getting rid of the sheet references fixes the error.
If you liked this post on 7 Reasons Why VLOOKUP Cannot Find the Right Value, 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.
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:
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.
VLOOKUP is a powerful function for extracting data from another sheet. And while most users will use it simply for pulling just one field, it can do a lot more than just that. Below, I’ll show you how you can extract multiple columns from just a single vlookup formula, potentially saving you from having to repeat the same formula over and over when you need more than one field.
Let’s start with the basics
First, I’ll setup a regular vlookup formula and then show you how, with a simple adjustment, you can pull a lot more data into your spreadsheet.
For this example, I’m going to use data from NationMaster, showing the number of vehicles in use by country. In addition to raw numbers, the data set also shows the year-over-year growth and the five-year compounded annual growth rate (CAGR). Here’s what the data looks like in my Excel sheet:
In a normal vlookup formula, you might have something like this setup if you wanted to extract all of the fields:
Cell H4 is where I’ve entered the country name. The vlookup works just fine if you want to pull data from the vehicles column. And if you want to grab the other fields you can just repeat the formula for the YOY% and 5-Year CAGR fields and just change the column number. However, there’s a much easier way to extract all those fields using just one formula.
Modifying the VLOOKUP formula
All I need to do to make this formula accommodate multiple columns is to change the column number. Rather than this:
=VLOOKUP($H$4,$A:$D,2,0)
I’ll enter in this:
=VLOOKUP($H$4,$A:$D,{2,3,4},0)
Using the curly braces, you can specify the different column numbers that you want to extract. Since this is an array formula, on older versions of Excel you may need to enter ALT+SHIFT+ENTER for the calculation to work properly.
Here’s the difference in formulas:
The columns you want to extract also don’t need to be sequential. You can extract columns 2 and 4 rather than all three. All you need to do is separate the column numbers that you want with a comma.
Retrieving the fields vertically instead of horizontally
Depending on how you’ve got your sheet set up, you may prefer for the data to come back in rows rather than columns. This too, is an easy fix.
All that you need to do is wrap your existing formula within the TRANSPOSE function. Here’s what the updated formula looks like:
=TRANSPOSE(VLOOKUP($H$4,$A:$D,{2,3,4},0))
This again is an array formula so you may need to use CTRL+SHIFT+ENTER on older versions of Excel. But doing it will allow you to retrieve the values vertically:
There’s a lot of flexibility with how you can use vlookup to extract data that can allow you to not only simplify your spreadsheet but also result in having to create fewer formulas.
If you liked this post on how to extract multiple columns from vlookup, 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.
Many people will tell you that you should use INDEX/MATCH instead of VLOOKUP or that you should use a new function like XLOOKUP instead. But you shouldn’t be so quick to ditch arguably the most popular function on Excel as it’s still very useful. Below are just three reasons why VLOOKUP is still incredibly valuable:
1. It’s really quick to set up
If you’re using a combination of INDEX/MATCH, you’re going to have to use two functions, correctly set them up and nest one inside the other. Especially if you’re not used to it, it can take some time to set it up. Sure, it’s not like it’s going to take hours or even minutes to do, but if you need a quick lookup and VLOOKUP can do the job, why not just use it? Here’s how quickly it takes to set it up:
In the above example, I do a VLOOKUP in about five seconds. If you’re setting up INDEX/MATCH, you might still be trying to figure out which column to use for your MATCH argument. Being able to do VLOOKUP without almost thinking is what makes it such a great function, its speed is through the roof. Since you know the first column of your range is where you’re looking up values, it simplifies the process of selecting the columns and then you’re just counting how many columns over you’re extracting data from.
A couple of ways I expedited the formula above is by not typing out the entire function name (just entering VL and then tab to autocomplete the name), using 0/1 instead of typing out True/False and by not closing the last “)” as Excel will automatically do this for you.
Sure, it won’t work in all scenarios such as if you need to go left, that’s a well-known limitation of VLOOKUP. But as long as that’s not the case, there’s really no reason you need to bother with INDEX/MATCH when VLOOKUP will do the job. I’ve been using Excel for decades and I still love to use it when I can because it’s so easy to set up.
2. VLOOKUP is very versatile and will work on old versions of Excel
VLOOKUP may not be able to go left, but it can do wildcard searches and it can work if you need to pull the closest value — this is really useful if you’re dealing with tax brackets or anywhere that you’re looking for the closest value without going over (e.g. where you set the last argument to TRUE to look for approximate matches). While many people may use it strictly for exact matches, VLOOKUP is much more powerful.
And here again, using VLOOKUP in these situations is likely going to be no more difficult than the alternatives. While the temptation may be to use an exciting new function like XLOOKUP, the one big disadvantage is that it’s not available on older versions of Excel. With VLOOKUP, even if you’re working on a version that’s 20 years old you won’t have to worry about whether the formula will work.
3. Ease of use makes it ideal for training novice users and making templates with
Not only is VLOOKUP easy to set up, but it’s easy to understand compared to other, more complicated functions. If you’re making a template or need to train users, you don’t want to worry about them knowing complex formulas, especially when it involves nesting functions. Or telling them about a formula that may not work on their version of Excel. VLOOKUP’s also a good stepping stone for beginners to get them accustomed to how Excel formulas work.
Complex formulas are easy to break and harder for inexperienced users to fix. That’s why VLOOKUP’s ease of use is a key reason it’s worth using. If you’ve ever had to fix someone else’s formulas, you can definitely appreciate that keeping formulas as simple as they need to be can go a long way in making it easy to maintain and fix a spreadsheet.
If you liked this post on why you should still use VLOOKUP, 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.
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:
Here’s how my formulas would look like if I wanted to return the Country using a value from the City field:
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:
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:
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:
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:
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.
Often times in a data set you’ll have to handle with errors that can wreck your data, especially if you need to do any analysis on it. There are several ways that you can handle errors so that they don’t show up in your data.
IFERROR
The first method is by using the IFERROR function, which allows you to easily replace the error with whatever you want in its place. If you want a numerical value, you may want to put in a 0, otherwise you can just leave it blank.
I’ve purposely added various types of errors to my data set:
Here is one of the formulas that’s causing an error:
=VLOOKUP(D7,L:L,1,FALSE)
In the above example, I could use the formula =IFERROR(VLOOKUP(D7,L:L,1,FALSE),””) to replace the error with a blank. I could also put a 0 in its place instead of the “”.
ISERROR
In older versions of Excel (2003 and earlier), the IFERROR function is not available. However, what you can use is a combination of the IF and ISERROR functions. To recreate the same formula as above, we can use the following:
The disadvantage of this method is you have to repeat your original argument. First, you are checking if the value is an error, if it isn’t, then you have to repeat the formula again to save the value. It’s not terribly efficient, and likely why we saw the IFERROR function introduced in newer versions of Excel.
Using the IFERROR or IF(ISERROR()) functions can be useful for eliminating errors, but sometimes it may not be helpful for dealing with specific ones. For example, if your cell is blank or it has an error and is made to look blank, you won’t be able to tell the difference just by looking at it. The danger is that you may assume it’s a different type of error.
ISNA
What you can also use is the ISNA() function, which can tell you if the cell returns the #N/A error. This way you can trap this error specifically, rather than everything that can be captured by the ISERROR() function.
Getting Rid of Zeros
If you’ve used a lot of error-handling functions and replace your errors with zeros, you could up with a lot of zero values on your spreadsheet:
The problem if you have a lot of zeros on your spreadsheet, is it can sometimes be a distraction away from what you really want to see – the non-zero values. There are two ways you can get rid of the eyesore:
1. Change the format to Accounting. Doing this will remove the zero values and replace them with a dash, which makes it a bit easier to skip over when doing a review:
However, you may not want to use the Accounting format, and that leads me to the other option:
2. Conditional Formatting. Refer to this post on how to setup rules for this. What I normally do in these cases is set the zero value cells to a light gray color font so that they do not attract your attention:
Common Types of Errors
Here’a list of some of the common types of errors you’ll find in Excel:
#REF: This is an error that you’ll incur if your range doesn’t go far enough and the error relates to your reference. For example, consider a VLOOKUP formula that extracts from column number five but you only specified a range that had four columns, that would result in a REF# error.
#N/A: You’ll get this error if your VLOOKUP or MATCH formula is correct, but the value you’re looking up isn’t found, and hence, not available. However, there’s other contexts it can apply to, and it just means that it wasn’t able to find the value you were looking for.
#VALUE: This error normally shows up when there is an issue with your actual calculation. For instance, if you’re trying to multiply a number by a field that has text.
These are just a few examples of the errors that you’ll encounter, but these are also likely the most common that you’ll come across.
If you’re looking up data, often times just using a VLOOKUP function can be enough to get you your desired result. Sometimes, however, it doesn’t do enough, especially if you’re looking for a partial match.
While you can set VLOOKUP to pull an approximate match rather than an exact match, that may not provide you with the desired results, especially if you’re using text.
Consider the following situation where you’ve got a series of charges from your credit card statement and want to find a particular vendor:
In the above example, let’s assume I’m looking for McDonald’s and do a regular VLOOKUP and set the approximate match argument to true, my formula looks like this:
=VLOOKUP(“MCDONALD’S”,B:B,1,TRUE)
And the result I get is this
As you can see, it’s not what I was hoping for. Excel can’t figure out that I’m looking for the second result, and simply gives me the last one.
Using Numbers with the Approximate Match
The approximate match isn’t useless in VLOOKUP, in fact, when it comes to numbers, it can be very accurate.
Consider the following example:
If I want to find out the tax bracket that a given income level relates to, I’ll use this formula
=VLOOKUP(G11,D:E,2,TRUE)
Assume column D is the taxable income and column E is the tax bracket %, with the taxable income I input being in cell G11.
Using this formula, if I put an income of $0 in G11, my tax bracket is correctly returned as 0%. It’s not until I enter $25,000 that it will return 10%.
Excel understands numbers better than words, and so it knows that since $24,999 is not greater than or equal to $25,000, that it still belongs in the first tax bracket, the one that was 0% and started at $0.
If I enter $105,000 as my income amount, then it also correctly knows that I’m in the 25% tax bracket since that is the highest bracket in the list.
If, however, I don’t put the brackets in the correct order my results won’t be the same. In order for this type of calculation to work, you need to start from the lowest value to the highest.
How Can We Get Text to Work?
If you want your partial text matches to work, you’ll want to use wildcards. What you can do is add an asterisk before and after your search term, which will then return even a partial match. Here’s an example of how the updated formula might look from the first example:
=VLOOKUP(“*MCDONALD’S*”,B:B,1,FALSE)
This might look a bit confusing since now I’m actually not looking for an approximate match, but rather an exact one, as indicated by the FALSE argument at the end.
But because the asterisks will grab everything before and after my text, technically I do want it to match exactly, since it’ll search for my string as well as anything before and after it.
The result:
*Note that the formula is not case sensitive. Whether I type in MCDONALD’S or mcdonald’s, it would have no impact on my result.
As you can see, now I get the partial match that I was looking for. The danger, however, is that your partial string isn’t unique enough. If I were to use the word STORE as my string, I would get the first result that is a match, and in this case that would not be what I want.
Because VLOOKUP will return the value for the first time there was a result, you want to ensure its not a common string that will be found more than once.
