VLOOKUP Not Working? 8 Possible Reasons and Solutions

From this tutorial, you will know possible reasons why a VLOOKUP formula is not working and how to fix them.

One of the most popular functions in excel is probably VLOOKUP. When we use VLOOKUP to get us particular data from a cell range, we may sometimes find that our VLOOKUP formula produces a wrong result or even an error.

The wrong result or the error makes us need to fix our VLOOKUP formula so it can finally give us the result we want. However, to know what we need to do to fix it, we should first know the reason why it produces its current result. This tutorial will help you to find why your VLOOKUP isn’t working as it supposes to be and tell you what you need to do to solve the problem.

Want to know what are the possible reasons for a dysfunctional VLOOKUP and the solution for each of those reasons? Let’s get into it right away!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

Reason for VLOOKUP Not Working 1: You Give a Wrong Last VLOOKUP Input

We will discuss eight possible reasons here that can cause a VLOOKUP formula to not work as it should be. The first possible reason is that you give a wrong last (fourth) input for your VLOOKUP formula.

That last VLOOKUP input determines the search mode that your VLOOKUP will assume when it tries to find your reference value in its reference cell range. The input can be TRUE or FALSE and it is an optional input.

If you input TRUE, that means you tell VLOOKUP that it can find an approximate match to your reference value if it doesn’t find its exact match. An approximate match here means the smaller nearest value from your reference value. If you input FALSE, you tell VLOOKUP that it can only find the exact match of your reference value. If it doesn’t find it, VLOOKUP will produce an #N/A error.

If you don’t give any input, then VLOOKUP will assume you give TRUE as your last input. Thus, if you only want an exact match to your reference value, you should make sure that you give FALSE as your last VLOOKUP input. On the other hand, if you allow an approximate match, you shouldn’t give any input or give TRUE for that last input.

To better understand the difference between TRUE and FALSE impact as the last input of a VLOOKUP formula, take a look at the screenshots below.





As you can see from the screenshots above, if we use FALSE as our fourth VLOOKUP input, we will only get a result if there is an exact match for our reference value in the first column of our reference cell range. Otherwise, we will get an #N/A error.

If we use TRUE, we will get these kinds of results.





If we use TRUE, if there is any exact match of our reference value, VLOOKUP will get its result in that exact match row. Otherwise, it will try to get the smaller nearest match of our reference value to get its result (in the example above since there is no 72 in the first column of our reference cell range, our VLOOKUP will get its result from the smaller nearest value from 72 there, which is 65. That makes us get the 65 letter, which is C).

So, they give us a different result, don’t they? Thus, you might find yourself get a wrong result from your VLOOKUP because of this last input that you give into your VLOOKUP formula.

Solution: Check again your VLOOKUP formula if you suspect this is the reason why your VLOOKUP doesn’t work as intended. If you want to only get an exact match of your reference value, input FALSE as your fourth VLOOKUP input. If you are okay with the approximate match (the smaller nearest value of your reference value), input TRUE or don’t input anything at all as your fourth VLOOKUP input.

Reason for VLOOKUP Not Working 2: You Use an Approximate Match VLOOKUP but the First Column of Your Reference Cell Range Hasn’t Been Sorted Ascendingly

Did you see the previous screenshots of the VLOOKUP implementation example with TRUE as its fourth input? Do you see any pattern in the data of the “Minimum Score” column of the table there?

The pattern there is that the data is sorted in ascending order. Now, what if the data hasn’t been sorted like that? The answer for this question is you might get a wrong result or even an error from your VLOOKUP formula, which uses TRUE as its fourth input.



Take a look again at the data in the aforementioned screenshot.



If the data in the “Minimum Score” column isn’t sorted in ascending order, the VLOOKUP result can become wrong like this.



The result can get wrong even if there is an exact match for the reference value in the “Minimum Score” column.



So, do you use TRUE as your last VLOOKUP input (or you don’t input anything at all there which means VLOOKUP uses its default last input, TRUE)? If you do, have you put the first column of your reference cell range in ascending order?

Solution: It is simple. If this is the cause of your VLOOKUP error, think again about the matching needs of your VLOOKUP reference value.

Do you need the approximate match of it if VLOOKUP cannot find its exact match or is it okay to just stick to the exact match? If you need the approximate match in case there is no exact match, sort your VLOOKUP reference cell range in ascending order with the first column of the cell range as the sorting base. If not, just use FALSE as your last VLOOKUP input.

With FALSE as your last VLOOKUP input, you don’t need to sort the first column of your cell range, as you can see in the screenshot below.



A VLOOKUP formula with FALSE as its last input can still produce the right result even though the first column of its reference cell range isn’t sorted in ascending order. Therefore, use this FALSE input if it suits your VLOOKUP needs.

Reason for VLOOKUP Not Working 3: You Use VLOOKUP to Look Up Data on the Left

Look up for data on the left here means the column which contains the data you look for in your VLOOKUP reference cell range is on the left of the column that contains your VLOOKUP reference value. VLOOKUP cannot do this kind of data lookup process since it can only look for its reference value on the first column of its reference cell range, which is the far left column there.

Looking up for data on the left of the column of your reference value with VLOOKUP will only confuse you and make you get a wrong result or, most likely, an #N/A or #REF error.

Solution: There are two possible solutions you can try here. Either you move the column of your reference cell range that contains your VLOOKUP reference value to the left of the column that contains your VLOOKUP result (and reinput the reference cell range in your VLOOKUP formula after that) or you use INDEX MATCH instead.

INDEX MATCH is much more flexible than VLOOKUP when it comes to the data lookup process. To learn how to use INDEX MATCH in excel, you can visit this INDEX MATCH tutorial.

Reason for VLOOKUP Not Working 4: You Input a Reference Value that is in a Column Other than the First Column of Your Reference Cell Range

As previously discussed, VLOOKUP will only look for your reference value in the first column of the cell range you input as its second input. If it is in another column beside that first one, VLOOKUP cannot and will not find it. As a result, it will give you an #N/A error instead.

Solution: Reinput the second input of your VLOOKUP so your VLOOKUP reference cell range will have your reference value in its first column, not in the other column.

Reason for VLOOKUP Not Working 5: You Input a Reference Value that Doesn’t Exist at All in Your Reference Cell Range

This might be even worse than the reason before. You either input the wrong reference value, forget to input a data entry with your reference value in your reference cell range, or the reference value is inputted wrongly so VLOOKUP cannot detect it (it has redundant spaces, typos, etc). Since VLOOKUP cannot find your reference value, it will give you an #N/A error.

Solution: Check your reference value existence by using the Find and Replace feature in excel. If the feature cannot find it, make a data entry that contains the reference value or edit an existing one. You can also change the reference value input of your VLOOKUP to the one that exists in the first column of your reference cell range.

Reason for VLOOKUP Not Working 6: You Have Duplicates of Your Reference Value in the First Column of Your VLOOKUP Reference Cell Range

By default, VLOOKUP will only recognize the first match of your reference value. If there is more than one match of your reference value in the first column of your reference cell range, VLOOKUP will ignore them other than the first match.

This will be a problem for you if the data you intend to find is in the row position of the second, third, or further match of your reference value in your reference cell range. Because of the default VLOOKUP process, you will get the wrong result from your VLOOKUP formula.

Solution: If you cannot delete or edit those duplicates of the reference value matches, then you should create helper columns to help VLOOKUP differentiate those reference value matches in your reference cell range. You can find the detailed tutorial on how to implement this solution here.

Reason for VLOOKUP Not Working 7: You Use VLOOKUP to Look Up Data Horizontally

VLOOKUP can only look up data vertically/by columns as the name of this function suggests (VLOOKUP or Vertical LOOKUP). To look up data horizontally or in a cell range that categorizes its data per row, you should use another excel function.

Solution: You can use HLOOKUP to help you look up data horizontally. Read our detailed HLOOKUP tutorial if you want to learn more about how to use this function.

If you know how to use INDEX MATCH, you can also use it to look up data horizontally. You can read our INDEX MATCH tutorial to understand more about the INDEX MATCH utilization methods in excel.

Reason for VLOOKUP Not Working 8: You Input a Wrong Column Index Number

The column index number input (the third input) in VLOOKUP determines from which column VLOOKUP is going to get its result. Wrong input here can make VLOOKUP produces a wrong result or even an error if you input a non-number or a number greater than the number of columns available in your reference cell range.

Even your previously correct VLOOKUP can produce a wrong result later because of this reason. For example, you might unknowingly insert or delete a column or two in your reference cell range that causes the column index number you should input in your VLOOKUP formula to change.

Solution: Check your VLOOKUP column index number again and make sure you input the right number to get your intended VLOOKUP result.

If you often need to change the column structure of your reference cell range, you might need a more dynamic VLOOKUP that can adapt its column number index input with those changes. For that, you might need to combine VLOOKUP with other functions in your formula. You can learn how to do the combination here, here, or here. Pick the option that suits your needs better.

If you are still confused about this column number index input and want to learn more about it, read our VLOOKUP column index number tutorial.

Additional Note

#N/A error from VLOOKUP generally means VLOOKUP cannot find the reference value you specify in it while #REF error generally means VLOOKUP cannot find the reference cell range or result column that you input in it.



Related tutorials you should learn too: