7 Reasons Why VLOOKUP Cannot Find the Right Value

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:

List of car 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:


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


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:


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


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.


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:


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:

Multiple vlookup formulas.

The lookup values are correct but if they get re-sorted, they’re now referencing the wrong values and the lookup results are incorrect:

Lookup values are incorrect after sorting.

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:


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.


Deleting a Formula in Excel? Do This First

When you’re dealing with complex spreadsheets in Excel, it can sometimes be difficult to tell which cells are safe to delete and which ones you need to keep to ensure everything is working properly. Even cells that look empty could contain formulas. And deleting them can cause problems and wreak your spreadsheet. Before you delete a formula, there’s one thing you can do to prevent that mistake:

Check for dependent cells

If you’re not sure if a cell is okay to delete and if it has any other cells that depend on it, you can check for dependents. Before deleting a cell, you can click on CTRL + ] which will highlight any cells that use the active cell in a formula (on the current sheet). Here’s a sample spreadsheet that lists price, quantity, and multiples them to get to a total price:

Spreadsheet that calculates the total by multiplying price by quantity.

The formula in column D multiples the value in B by C. So that means the value in D depends on the values in C and B (the exception is the subtotal, which depends on the values above it). If I select cell C2 and click on CTRL+], it takes me to cell D2:

The dependent cell is highlighted.

If there is more than one cell that depends on the active cell, then Excel will highlight all of them.

What if there aren’t any dependent cells? In that case, you’ll get the following message:

Message box saying no cells were found.

If you get this message, that means you’re safe to delete the current cell as nothing in the current sheet links to it. However, the one limitation of using the shortcut is that it may not be easy to see all the cells that depend on that one cell. It also won’t tell you if there is a cell on another sheet that uses it.

What you can do is use the Trace Dependents button in Excel, which is on the Formulas tab:

Trace Dependents button.

By clicking on this button, arrows will now show up telling me exactly where the dependent cells are:

Arrow showing the dependent cell.

In this situation, the arrow clearly shows an arrow pointing to cell D2. Let’s say I also use the cell in a formula in some place far off in a the same sheet:

Arrows showing multiple dependent cells.

Another line will point to the other cell. If you have a large data model that goes on for many rows and columns, it may not be obvious where the dependent cells are if you use the shortcut key. Using the shortcut can be helpful as a quick check but if you actually want to see all the cells that use the active cell, you’re better off clicking the Trace Dependents button.

Next, let’s go to the subtotal. Here, let’s assume I’m using this total somewhere on another sheet. Using CTRL+] won’t help me much in this case as it will tell me no cells were found (assuming no cells on the current sheet link to it). But if I click on Trace Dependents, it will show that there is a dependent cell on another sheet:

Dependent cell is on another sheet.

If you double-click on the dotted line (the portion that’s within the cell), the following box will pop up:

Go to box showing where the dependent cell is located.

This tells us that there is a dependent cell on Sheet2, cell B1. I can go there manually or I can click on the selection and then press OK. Then it will take me directly to the cell:

Dependent cell that links to another sheet.

This isn’t practical on a wide scale as you would have to go one by one and you could have arrows going all over the place. But if you’re not sure about a certain cell, using the Trace Dependents button can be a quick way to see if it’s safe to delete the cell.

If you liked this post on 1 mistake to avoid when deleting formulas 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 Find Circular References in Excel

If you’re getting an error message in your Excel spreadsheet that there’s a circular reference, that means that at least one of your calculations is referencing back to itself in one way or another. It doesn’t always have to be that two cells are referencing one another. Even indirectly referencing back to a cell can cause a circular reference error.

First thing’s first though, you have to find out where your circular references are before you can fix them, which isn’t always easy. Below, I’ll show you how to find circular references in Excel using multiple approaches.

Finding circular references

Let’s look at a very simple example where cells A1 and B1 are just referencing one another. This is what the error would look like on the two cells:

The blue arrows pointing at one another indicate that the cells are referencing each other. If the circular reference involves another worksheet, you’ll see something like this:

If you double-click on that line it’ll bring up the Go To dialog box which will show you which cell it’s referencing on another sheet:

From here you can click on the item in the Go To box and click OK and it’ll take you to that cell.

If you’re able to see blue arrows or the link to another spreadsheet, then it’s easy to trace where the circular references are . But if you’ve got a large spreadsheet, it may not be so easy spotting where the circular references are, which is why you may need to look at other methods.

One way is by looking at the bottom left corner of Excel, where you’ll see it mention circular references if there is an error:

And if you’re on the same sheet that the circular reference is on, you’ll also see which cell is causing it:

But it won’t be able to show you all of the circular references if there are more than one here as there’s obviously limited space to do that.

Another way that you can look for circular references is by going to the Formulas tab and then clicking on the down arrow next to the Error Checking button:

Then, click on the Circular References button and you’ll see a list of all the circular references in the workbook:

You’ll notice this way it will also show you which sheets have circular references.

Fixing circular references

Once you’ve identified which cells need to be fixed, then what’s left to do is adjust the cells. If you’ve just made an error then all this may involve is just deleting the contents of the cell entirely or just removing the reference to the cell in order to remove the circular reference.

But in many cases, it’ll involve a formula. And this is where it can get a little tricky as you may need to restructure your formula to ensure it’s not circling back in some way to the original cell. One possible solution can be to add another field as an intermediary to ensure you aren’t trying to do too much in just one formula.

If you liked this post on how to find circular references 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.

hiding values in excel

How to Hide Zero Values in Excel

If you’ve got a big spreadsheet with lots of numbers to look at, it can sometimes be a bit difficult to look at a large chunk of data. That’s where knowing how to hide zero values in Excel can be helpful in reading and analyzing data in Excel. By not seeing the zero values, you can easily focus your eyes on the more important numbers that may need more analysis.

However, it doesn’t have to be just zero values that you hide. Any number that’s insignificant for your analysis can also be hidden. For instance, you can be analyzing a company’s financial performance and choose to hide any movements that are less than 5%. The same principles apply as you would use to hiding zero values. Below, I’ll show you how you can hide not just zero values but any values that you don’t want showing up in your data while still factoring them into totals and any other calculations. You won’t be deleting anything, just masking the information.

First, let’s take a look at how to hide a potentially even bigger nuisance: errors.

How to hide errors on a spreadsheet

To help illustrate how to hide zeo values in Excel as well as other numbers, I’m going to use some real-world data — Amazon’s most recent annual earnings report, which the company released last month. Here’s the company’s income statement from the past three years:

Amazon's income statement over the past three years in Excel format.

If you’d like to follow along, you can download the data from the SEC. First up, I’ll add a few columns showing the change from 2018 to 2019 and from 2017 to 2019. Here’s how it looks just copying the formulas straight down:

Analyzing Amazon's income statement using Excel with error values showing.

I have divide by zero errors as there are rows that have no data. I could just remove these cells but as with anything in Excel, it’s good to be consistent. Rather than deleting those error values, I can get rid of the errors in one of two ways.

The first is by using an IF statement to say that if the denominator is 0 or blank, to ignore the calculation. The second is just to use an IFERROR statement.

Here’s what my formula looks like for the 2018 to 2019 change:


Where C5 is the 2019 data and D5 the 2018 numbers. I don’t need any parenthesis as order of operations ensures the formula will calculate properly. However, it doesn’t prevent me from getting a divide by zero error. Since the numerator can be blank or zero, what I’ll want to focus on is fixing the denominator in D5. To do this, I can add an IF function that looks at whether the denominator is a number. Here’s what my formula will need to look like to remove that error:


The formula now checks to see whether D5 is either a zero or blank, and if it is, it returns a blank value. Otherwise, it calculates as normal. Now I can copy this formula down and get rid of the error values.

An alternative way to fix this is by using IFERROR. Introduced in Excel 2007, the function can be an easy way to replace errors on your spreadsheet with another value. In this example, I’m going to use empty quotes (“”). The benefit is obvious: it’s a lot easier to use IFERROR than an IF statement, especially combined with an OR function as well. Here’s what my formula would look like with IFERROR:


It’s a whole lot easier and quicker. I don’t have to worry about the logic and all the reasons why the formula might error out. However, it’s not a perfect solution and here’s why: it will correct errors, but it’s possible they’ll be errors you’re not expecting. For instance, if I copied the data wrong or keyed something over and put text in a field where it should be a number, the IFERROR will correct that and you won’t be able to tell whether it’s blank because it is a divide by error problem or something else. That’s where it can be a little dangerous in using this one-size-fits-all approach to fixing error values. As long as you’re okay with that, it’s a perfectly good approach to fixing the divide by zero errors.

Here’s the data now that it’s been cleared of errors:

Analyzing Amazon's income statement using Excel after error values have been hidden.

That looks a lot better but the problem is that it’s still a lot of percentages to look at and it’s difficult at a glance to see what are the big changes are from the prior year. This is where it’s also important to hide zero values in Excel, as well as low values that aren’t useful for analysis.

How to hide zero values in Excel and other numbers that you don’t want or need to see

In order to hide data, it’s useful to use conditional formatting. If you’re not familiar with how to use conditional formatting, check out this post. Conditional formatting won’t remove or erase any data, which makes it a good solution that will keep all the data and calculations intact.

In the Amazon example, there are some pretty large-moving items in the list. Removing zero values won’t do anything and the threshold needs to be big for it to be helpful in hiding the lower values. Let’s start by removing the percentages that are less than 20%. Here’s the formula that I will use in the conditional formatting to accomplish this:


Column F is where the % Change from 2018 values are. I need to use the AND function because if I just look at anything that’s less than 20% this will also capture negative movements that are more than 20%. And for now, I want to keep those. I want to remove anything that’s between -20% and +20%, which is what the above formula will capture. If I was only looking to remove the zero values then the formula would be as simple as F5=0.

The next step is to adjust the formatting so that the cell font is white. Changing the color is an easy way to hide a cell’s value if it’s on a white background. While that data is still there, it won’t be visible:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

It creates a lot more white space, allowing me to see a lot more of the bigger values. The problem that I notice, however, is that there are some low values that are creating big movements in percentage. I can go a step further and create another conditional formatting rule that will also ignore the percent change for any item in 2019 that was less than $1 billion (1,000). This is how that formula will look:


I need to freeze column C because the conditional formatting will be used for the other change column as well and I don’t want the reference to move. Now, with this adjustment, it makes a much bigger difference and helps me narrow in on fewer items:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

I can see that the significant changes from 2018, besides the totals, were in sales, technology and content, and marketing. However, since the growth rate from 2017 is even higher, I’ll need to adjust those percentages to also ignore anything that’s not at least a 50% improvement. Here’s how that formula will look (note that I’ll only apply it for the % Change from 2017 column):


Remember, since I’m analyzing percentages, these figures need to be in decimal point. Otherwise, I would be using whole numbers. With those changes, this is how my data looks:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

Now I’ve also got a reasonable amount of items I can focus on for the % Change from 2017 column. In addition to the same items increasing from 2018, I notice that fulfillment costs have also shown a significant increase over two years.

The conditional formatting works great in clearing out numbers that I don’t want to see. However, there’s just one small problem…

Analyzing Amazon's income statement using Excel with hidden values showing on a dark background.

If I change the color to anything that isn’t white, those numbers that I hid become visible again. That leads me to another all-important section:

How to hide values in Excel that are on different background colors

You can create conditional formatting rules to address other background colors but that’s just not practical. If you use lots of colors on your spreadsheet the last thing you want to do is create a rule for every different color and make sure the cells are hidden in the same font color. There’s also a problem if someone changes the color too.

That’s why using font color to hide values in Excel isn’t a good idea. The good news is that there’s a much easier way to hide values that doesn’t involve you having to try and match up the color.

Rather than changing the color, what you should do is use a custom number format. Simply use three semi-colons and that will do the trick:

Choosing a customer number format to hide zero values in Excel.

Without going into the details of the different formats you can use, by using three semi-colons you’re telling Excel that you want no formatting to be used whether the amount is positive or negative. Now, my hidden data remains hidden regardless of the background color:

Analyzing Amazon's income statement using Excel with hidden values no longer showing on a dark background.

Now you don’t have to worry about background colors and can easily hide your data in any context.

If you liked this post on how to hide zero values in Excel as well as other values, 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.


Formatting and Removing Errors and Zero Values

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.


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:
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 “”. 

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.

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.
Inventory count sheet showing data by product number.

How to Sort Data in Excel Without Messing Up Formulas

If you have a formula that involves multiple sheets and you later sort that data you may notice your cell references are now out of order and need to be corrected. It can be frustrating and dangerous because you may not realize your formulas are now calculating different cells.

For example, I have created a sheet called InventoryCount and another called Total. The InventoryCount sheet acts as a tally of all the locations a certain product number is found:

Inventory count sheet showing data by product number.

On the totals sheet, I have a summary of these product numbers using a SUMIF formula that multiplies by the price.

A summary of the inventory values.

The formula in the highlighted cell above is as follows:


The formula in the price column for product 1 is:


Both formulas are referencing the InventoryCount sheet. The formulas in the value column are correct. But let’s say that I want to sort that column by values. If I sort in descending order, this is what I get:

Inventory data after applying a sort.

The values are not only not correctly sorted  (product 1 is lower than product 2’s value) but the values have changed. If you look at the values before the sort product 3 dropped from 10,956.16 to 4,547.84. There is nothing glaringly obvious that the calculation is now completely incorrect so you can imagine the danger when dealing with lots of data that such a sort could make your data get altered. The formula for the value in product 1 now looks like this:


It is still referencing A2. So technically the formula is still correct, the problem instead is product 1 is no longer on row 2, it is on row 3. The assumption that the value in row 3 relates to the product on row 3 will now be incorrect.

So why did this happen? Notice that the price has not changed for any of these products, Product 1 was $3.45 before the sort, as well as after. This column still is correct in relation to its corresponding products.

If you go back and look at the two formulas (price and value) you will notice on key difference: the cell referenced on the current sheet (Total) for the price does not mention the sheet name, however on the value formula it does.

This is the original formula to calculate the value:


The Total! reference is causing the sorting issue and needs to be removed. The updated formula becomes:


Now if I apply the same logic to the other formulas in the value calculation and sort the data in descending order, below is the result I get:

Inventory data after applying a sort.

The values are back to what they were at the start and product 3 is the highest as it was initially. Except now they are correctly sorted in descending order with the product number correctly being referenced in column A.

So the lesson here is that if you are using formulas with multiple sheets get rid of the sheet reference when referencing a cell on the same sheet as the formula is on (Excel inserts this reference automatically as you switch from one sheet to the next). Otherwise, if you or anyone else sorts the data the calculations will not be on the correct rows.

If you liked this post on How to Sort Data in Excel Without Messing Up Formulas, 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.