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.
Lookup_Value
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(“Dog”,DogTable,2,0)
Or
=VLOOKUP(A2,DogTable,2,0) where A2 = Dog
Table_Array
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:
=VLOOKUP(C1,DogTable,2,0)
Or
=VLOOKUP(C1,A3:C7,2,0)
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.
Col_Index_Num
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.
Range_Lookup
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.
