by The VLOOKUP function is the most popular lookup and reference function in Excel. It is also one of the trickiest and the dreaded #N/A error message can be a common sight.
This article will look at the 6 most common reasons why your VLOOKUP is not working.
You Need an Exact Match
The last argument of the VLOOKUP function, known as range_lookup, asks if you would like an approximate or an exact match.
In most cases people are looking for a particular product, order, employee or customer and therefore require an exact match. When looking for a unique value, FALSE should be entered for the range_lookup argument.
This argument is optional, but if left empty, the TRUE value is used. The TRUE value relies on your data being sorted in ascending order to work.
The image below shows a VLOOKUP with the range_lookup argument omitted, and the incorrect value being returned.
Solution
If looking for a unique value, enter FALSE for the last argument. The VLOOKUP above should be entered as =VLOOKUP(H3,B3:F11,2,FALSE).
Lock the Table Reference
Maybe you are looking to use multiple VLOOKUPs to return different information about a record. If you are planning to copy your VLOOKUP to multiple cells, you will need to lock your table.
The image below shows a VLOOKUP entered incorrectly. The wrong cell ranges are being referenced for the lookup_value and table array.
Solution
The table that the VLOOKUP function uses to look for and return information from is known as the table_array. This will need to be referenced absolutely to copy your VLOOKUP.
Click on the references within the formula and press the F4 key on the keyboard to change the reference from relative to absolute. The formula should be entered as =VLOOKUP($H$3,$B$3:$F$11,4,FALSE).
In this example both the lookup_value and table_array references were made absolute. Typically it may be just the table_array that needs locking.
A Column Has Been Inserted
The column index number, or col_index_num, is used by the VLOOKUP function to enter what information to return about a record.
Because this is entered as an index number, it is not very durable. If a new column is inserted into the table, it could stop your VLOOKUP from working. The image below shows such a scenario.
The quantity was in column 3, but after a new column was inserted it became column 4. However the VLOOKUP has not automatically updated.
Solution 1
One solution might be to protect the worksheet so that users cannot insert columns. If users will need to be able to do this, then it is not a viable solution.
Solution 2
Another option would be to insert the MATCH function into the col_index_num argument of VLOOKUP.
The MATCH function can be used to look for and return the required column number. This makes the col_index_num dynamic so inserted columns will no longer affect the VLOOKUP.
The formula below could be entered in this example to prevent the problem demonstrated above.
The Table has got Bigger
As more rows are added to the table, the VLOOKUP may need to be updated to ensure that these extra rows are included. The image below shows a VLOOKUP that does not check the entire table for the item of fruit.
Solution
Consider formatting the range as a table (Excel 2007+), or as a dynamic range name. These techniques will ensure that your VLOOKUP function will always be checking the entire table.
To format the range as a table, select the range of cells you want to use for the table_array and click Home > Format as Table and select a style from the gallery. Click the Design tab under Table Tools and change the table name in the box provided.
The VLOOKUP below shows a table named FruitList being used.
VLOOKUP Cannot Look to its Left
A limitation of the VLOOKUP function is that it cannot look to its left. It will look down the leftmost column of a table and return information from the right.
Solution
The solution to this involves not using VLOOKUP at all. Using a combination of the INDEX and MATCH functions of Excel is a common alternative to VLOOKUP. It is far more versatile.
The example below shows it being used to return information to the left of the column you are looking in.
Your Table Contains Duplicates
The VLOOKUP function can only return one record. It will return the first record that matches the value you looked for.
If your table contains duplicates then VLOOKUP will not be up to the task.
Solution 1
Should your list have duplicates? If not consider removing them. A quick way to do this is to select the table and click the Removes Duplicates button on the Data tab.
Check out the AbleBits Duplicate Remover for a more complete tool for handling duplicates in your Excel tables.
Solution 2
Ok, so your list should have duplicates. In this case a VLOOKUP is not what you need. A PivotTable would be perfect to select a value and list the results instead.
The table below is a list of orders. Let's say you want to return all the orders for a particular fruit.
A PivotTable has been used to enable a user to select a Fruit ID from the report filter and a list of all the orders appears.
Trouble Free VLOOKUPs
This article demonstrated a solution to the 6 most common reasons a VLOOKUP function is not working. Armed with this information you should enjoy a less troublesome future with this awesome Excel function.
About the Author
Alan Murray is an IT Trainer and the founder of Computergaga. He offers online training and the latest tips and tricks in Excel, Word, PowerPoint and Project.
223 comments
You might be able to concatenate a string. Wrap the * in "" to indicate that you want to refer to that character literally and not as a wildcard.
Otherwise you might be able to use the EXACT function with INDEX and MATCH instead of VLOOKUP. Information on this can be found below.
http://www.computergaga.com/tips/lookup_formulas/case_sensitive_lookup.html
I have not tested this, but think it should work. This function does what its name suggest and ensure the strings match.
That might be. My ERP system only allows * as special characters so all my item number data exports have * in them. Do you know of a way to prevent this?
I am trying to write a VLookup formula to bring on hand data into a sheet that details sales volume. My formula is:: =VLOOKUP(A2,AA$2:AB$100,2,FALSE)
Item B1007080SHF3MDO2BS***GG is not present on the target range; Item B1007080SHF3MDO2BSR**GG is there, with a required response value of 409.
When I apply the VLookup to both fields, I get the value of 409 for BOTH, so the 'exact value' part of the formuala does not seem to be working. I have both lists sorted A-Z. It does not seem to matter if I format both columns as text or numbers, same issue (the full data set column does have values that are numbers only & alpha-numeric in both the search criteria and the target range).
It looks like the VLOOKUP is using the ** characters as a wildcard.
Hard to say exactly without seeing the file. Check that there is definitely a match, so no spaces after the number. And also check the formatting of both the lookup_value and on the table_array to check they are the same.
i am doing a very simple lookup and I am getting the #n/a. Trying to pull a number into my master. The first column on both is my ref. the formula is =vlookup(A2,PLANC,2,false). Very very simple. I checked to make sure there was a match and there was and no duplicates were found. Help!
I was having the same issue and just figured it out! (getting #N/A when I know there's an exact match)
If you see the green triangle on the cell, hover over the yellow exclamation point "Number stored as text". From the dropdown select "Convert to Number" and it will fix it immediately. Good luck!
Well done, Jen.
Yes, the two values must be the same format.
Thanks for sharing. I had some hidden columns so my VLOOKUP was not working! Fixed it after readong Point 3. on your list.
VLOOKUP won't be able to. It will only return the first name from the sheet.
You will need more than 1 VLOOKUP. Or a macro would be needed for multiple occurrances of a name.
can any help me put
i had a sheet of name of the employs and their working hours, here the problem is in my sheet i had two names similar...(EX: mahesh as two time in the sheet )
when i use vlookup in the table for my sheet i am not getting the second name
how can i get the second name in the sheet........by using vlookup
Thank you, thank you, thank you for the manual - automatic guide.
Hi Don,
Having calculation set from manual to automatic is very helpful. Save lots of time.
Thank you very much.
Andy
thank you
Hard to say why without seeing the spreadsheet but would expect there to be an error in the lookup value argument. I would check the reference.
Hopefully someone can help me. My vlookup is returning the correct values in my columns but it returns the same value until it comes across another non-zero value. For example the correct value of row 10 is 259 but it will return 259 for rows 11, 12, and 13. Row 14 will correctly show 864 but then rows 15 and 16 will too.
Has anyone come across this?
thanks,
Have the same issue. VLOOKUP is used only for values in ASCENDING order! Try Index Match combination instead.
thanks a lot for this information
Thanks a lot! very helpful :)
Sometimes the error may be because it has not calculated properly. If you have calculation set to manual rather than automatic, this can also cause an error when using vlookup or index match. Once the worksheet calculation is set to automatic, it works again (set it to automatic by going to the Formulas tab and then Calculation Operations and set to Automatic).
Dan,
THANK YOU!!! Hours wasted, but many more hours saved. The Automatic setting in Calculation Operations did it. Karma credits in the heapful submitted your way.
Thank u Dan.
I was trying to copy formula and wasted hours. ur just one line comment solved my problem in a tick
:)
Thank you so much Dan! This annoying setting has wasted me hours before!
This has saved my hours of hair-pulling ! I have been working with vlookup for a long time now but had not encountered this problem before. Many thanks !
OMG.. thanks so much .. was trying a few times why my excel keep seem to be repeating the same value.
Massive thanks for this tip! I couldn't figure it out!!!
You're welcome, Anna.
when we using vlookup it is show is same number in all colons
Me too is facing a similiar error.
Have you got any solution for this.please let me know
I'm not sure what you need. Do you mean all columns?
You would need a VLOOKUP in each. A formula can only return to one cell.
save the sheet, it will run the lookup
Thank you, it was helpful.
The trapfall of VLOOKUP being not able to "look to right" wasn't known to me before. Very helpful!
Thank you, it was helpful.