VLOOKUPs Gone Wild: 5 Common Mistakes (with Solutions) for Why Your VLOOKUPs Not Working

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

As someone who made their bones on the backs of countless VLOOKUPs, I can’t stress enough how important it is to master this function. But as great as it is, returning an error feels like par for the course some days. Especially when working with multiple sheets from who knows where.

So today, I’ll share the five most common mistakes that will return an error every single time, preparing you for the muddy waters of a VLOOKUP.

  1. Not Using Exact Match
  2. Not Using Absolute Cell Reference
  3. Incorrect Reference Range
  4. Incorrect Column Index
  5. Not Checking Your Lookup Value

After you learn about the errors and the mistakes that return them, you’ll see how easy they are to fix.

Understanding What a VLOOKUP is

Have you ever had to search for specific data in a large spreadsheet? It can be pretty tedious and time-consuming, right? That’s where VLOOKUP comes in handy! 

VLOOKUP is a function used in Excel and Google Sheets to search for and retrieve data from a table based on a specific search criterion.

If this is your first time experimenting with VLOOKUPs, there’s no shame in not matching your value in the first go. You might not even understand all the potential a VLOOKUP has. And even when you do, a malfunctioning formula is a common occurrence.

vlookup not working is a regular thing
Even with experience, an unfamiliar data set can easily stifle your results.

Here’s how it works: VLOOKUP stands for Vertical Lookup, which means it searches for data in a vertical column. The function requires four arguments:

1. The search criterion or value to look up 

2. The table array, which is the range of cells containing the data you want to search 

3. The column index, which is the column number in the table array containing the data you want to retrieve

4. The match type, which can be either exact or approximate 

vlookup anatomy

Once you enter these arguments, the VLOOKUP function will search for the search criterion in the leftmost column of the table array and retrieve the corresponding data from the specified column. However, entering the wrong arguments or not accounting for an imperfect data set will likely return an error.

Is VLOOKUP Different in Excel vs. Google Sheets? Errors Identified

Whether you’re an Excel truther or love the availability of Google Sheets, performing a VLOOKUP works about the same in each. Not to say there aren’t some differences. Just not enough to cover separately as far as errors go.

You still have the same four arguments needed for a successful lookup, and they all function within the same guidelines. In terms of an invalid return, four common errors are found in Excel and Google Sheets. And one other error is unique to Excel. 

These are the first clues that you may have made a mistake somewhere along the line.

Most Common Errors for VLOOKUP
There are several causes for these few errors. So get used to seeing them.

5 Most Common Mistakes When Using VLOOKUP

Hopefully, the breakdown above of the errors you’ll see in a VLOOKUP not working has your internal wheels spinning on solutions. If not, no worries. It’s a complicated business dealing with lookup formulas for the first couple of times.

Actually, that’s a lie. 

It’s always surprising you because most data sets you work with are imperfect. But that’s why I love doing it, and teaching it. This isn’t simple data entry; it’s a 1500-piece puzzle depicting a pile of autumn leaves.

So put your thinking cap on, and let’s talk about what mistake could have been made to get your lookup error.

Not Using Exact Match

One of the most common mistakes when using VLOOKUP is forgetting to use the exact match argument. The formula will return an #N/A error if the lookup value is not an exact match. 

not using exact match
Using the exact match argument should become second nature when performing VLOOKUPs.

I always see it where that last bit of the search parameter gets forgotten. And if you don’t have an exact match value in your table array, you’ll fire the #N/A error each time.

Solution

To fix this, set the last argument, the range_lookup, to FALSE or 0. You could also set it to TRUE or 1, but then you’re leaving yourself open to a less confident match. And that’s taking you into Fuzzy Lookup territory.

Not Using Absolute Cell Reference

Another mistake resulting in an #N/A error is not using absolute cell reference when selecting the table array. 

An absolute cell reference is a section of the formula pointing to a static range of cells. Suppose you don’t use an absolute cell reference in your formula. In this case, the table array will change and reference the wrong cells when you copy the formula to other areas. 

not using absolute cell reference
Locking in your cell ranges is an easy first step as you’re in the process of creating your formula.

In other words, Excel will shift the range down or to the side when you copy the formula to other cells, causing the formula to reference empty or incorrect data.

That’s a problem. But a very solvable one.

Solution

Correct this mistake by using the dollar sign ($) to lock the table array reference, disallowing any change when copying and pasting your formula. 

Take the snippet of data below; if your lookup table is in cells A1:B10, you can use the absolute cell reference “$A$1:$B$10” to ensure the formula always references those cells.

Do this effortlessly by placing your cursor on the table array section in the formula bar and hitting F4 on your keyboard to toggle your options. If using a Mac, the keyboard shortcut is fn + F4.

Do this at your formula’s first instance and repaste the new VLOOKUP to the corresponding cells.

You could also use a named range for the lookup table, making the formula easier to read and less prone to errors. 

To create a named range, select the lookup table range cells, go to the “Formulas” tab, then click “Define Name” on the ribbon to give the range a name. Or simply select the range and type the new label into the name box of your ribbon.

define the name of your range
Naming your range is a safe method to get more accurate results and save time double-checking your work.

Now you can use the named range in your VLOOKUP formula instead of the cell references.

Incorrect Reference Range

The third common mistake you’re bound to make at least once when writing a VLOOKUP is using an incorrect reference range or table array. I’m not talking about the moving formula we just touched on. This is entirely different. 

A VLOOKUP requires the lookup value to be in the first column of your reference range and the return value to be in a column to the right of the lookup column. 

Unless, of course, you’re just trying to match the lookup value. In this case, the return value will be pulled from the single-column reference range.

But whether your lookup value exists in a single-column range or a multi-column range, it must be found in the first column you reference in the formula.

incorrect reference range
The order of your range is a fundamental part of matching your target.

If you suspect your error has something to do with a mistake in your reference range, below are the troubleshooting methods to try out.

Solution

I think I’ve hammered this one home already, but checking for a lookup value in the first column of your range is a good start. If it’s not there, either it’s not contained in the data, or your VLOOKUP needs another glance.

Try rearranging the columns in the reference range so that the lookup value is in the first column.

Another solution you might try is using the INDEX/MATCH formula instead of VLOOKUP, which can handle scenarios where the lookup value is not in the first column. 

Incorrect Column Index

I should have put this mistake at the top since it’s more common than the others we’ve gone over. But here we are!

A column index number in a VLOOKUP formula is the number of the column in the reference range that contains the value you want to return. For example, if you have a table with three columns (A, B, and C) and want to look up a value in column A and return a value from column C, you would use a column index number of 3.

The problem with an incorrect column index is it doesn’t return an error if the column it points to is still in the range. You’ll get a value, just not the one you want.

incorrect column index
You got a result, so it’s all good, right? Not so fast!

For example, if you use a column index number of 2 instead of 3 in the instance above, Excel would return a value from column B instead of column C.

Solution

To avoid this mistake, double-check the column index number in your formula to ensure it corresponds to the correct column in the reference range. 

Easy, right? Yeah, in theory. But it still happens all the time.

A good tip to avoid any issues with larger data sets is to use the column headings in the reference range to replace a column index. By labeling your desired target area, as you learned earlier, you can avoid pulling in incorrect data.

Not Checking Your Lookup Value

Here’s the last of the big five mistakes I often see with VLOOKUPs. So much so that it’s the first thing I check before writing my formula. 

Not checking your lookup value for problematic irregularities is a headache waiting to happen. Ok, maybe that’s a bit dramatic. But it’s still a time waster no matter how you look at it.

When using a VLOOKUP formula, there are a few things that can go wrong with an unchecked lookup value:

    misspelled lookup value
    Alternate spelling and missing or extra characters will guarantee you don’t find your value without an extra step or two.

    Solution

    Each of these problems has its own remedy. With time, you’ll learn all of them and become accustomed to those that best serve you.

    For me, the TRIM function is a lifesaver for clearing out the blank spaces before or after a value. Incorrect spelling is easy enough to identify with the spell check feature. You’ll find the spell check under the Review tab in Excel or press F7 on your keyboard.

    If the lookup value is in the wrong format, add another formula to correct the value. 

    Suppose you’re looking up dates in a table, and the lookup value is in an incompatible date format. In that case, you can use the DATEVALUE formula to convert the cell to a date format that Excel can recognize. Or choose a formula to extract the section you need.

    added formula for date formats
    You’ll find that grouping a second formula into your VLOOKUP is often necessary.

    Similarly, you’ll want to convert the format if you’re looking up numbers in a table, and the lookup value is stored as text. Do this by placing the VALUE formula around your first argument to transform the text into a number.

    Closing The Loop

    That was a lot to cover, but all of it valuable in your quest to wield this function. VLOOKUP is a powerful tool that can help you quickly find and retrieve data from large Excel and Google Sheets tables. I don’t know what I’d do without this handy feature. But it’s not without its challenges, as you can see.

    The good news is that you now know the biggest hurdles most of us experience. And more importantly, you know how to fix them.

    So next time you get that return column stacked with #N/A’s, don’t stress. Buckle down, inspect your formula, and use those problem-solving skills you touted on your resume.

    Leave a Comment