How to handle Excel VLOOKUP #REF! Error

We’ve all at some point looked at the VLOOKUP function and thought we were looking at another language. If you haven’t worked with the VLOOKUP function before it can be a little daunting and often lead to frustration when issues arise.

Understanding VLOOKUP arguments

Before we discuss the various solutions to handling VLOOKUP, it’s important to understand what VLOOKUP does and how it functions. Let’s take a quick look and break down the function.


This is the value in which you are wanting to look up. This can be a cell value or a static value you provide in the formula.



=VLOOKUP(A2,DogTable,2,0) where A2 = Dog


This is the range in which you want to find your Lookup_Value. Note: The value you are looking for has to be in the furthest left column of the range.

Assuming that the table above is named “DogTable”. This named table represents the range A3:C7.

So, our formula could be:




Both formulas work however using named ranges and table ranges for your Table_Array are more dynamic and versatile. We recommend this rather than absolute ranges.


The Column Index Number is the column in which you want to retrieve data if your value is found in the Table_Array.

If you want to find the value of “Dog” in the DogTable and return its size, you will specify the column number starting from the first column of the range.

So, if the further leftmost column is Animal, and the next column is Size, your value would be 2. It’s the 2nd column over from where the Lookup_Value could be found. If the table above were Animal, Cost and then Size the value would be 3.


Default for range_lookup will always be 1 if omitted. This will find a relative match and is generally not very accurate for most purposes. It is recommended to search for an exact match using 0 or FALSE.

VLOOKUP is generating a #REF! Error

This will happen from time to time, and can be frustrating to track down if you have complex formulas involved with VLOOKUPs. Let’s take a look at the below example and see what the issue is and how to resolve it.

In the below example, we have another set of data where we want to find the cost of the animal. So, we are going to use VLOOKUP to reference our database table “DogTable” and retrieve the pricing information. As seen below, we are using =VLOOKUP(S10,DogTable,3,0). S10 holds the value of Bird. S9 holds the value of Dog.

If you look under “Cost” you will see we are getting a #REF! Error message. The formula appears to be correct though. Well, if you look closer you will see that we made a mistake when making our Table. We did not expand the range to include the “Cost” Column.

Although the VLOOKUP function is finding the value “Dog” in our table, we are asking it to return the 3rd column value. That’s strange but our table only consists of two columns. In this case, we need to expand the range of our Table_Array to include the “Cost” column. Once this is done our #REF! error message will disappear.


Kevin Arrows

Kevin Arrows is a highly experienced and knowledgeable technology specialist with over a decade of industry experience. He holds a Microsoft Certified Technology Specialist (MCTS) certification and has a deep passion for staying up-to-date on the latest tech developments. Kevin has written extensively on a wide range of tech-related topics, showcasing his expertise and knowledge in areas such as software development, cybersecurity, and cloud computing. His contributions to the tech field have been widely recognized and respected by his peers, and he is highly regarded for his ability to explain complex technical concepts in a clear and concise manner.