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
my vlookup function returns the cell value in the row above the answer cell...
Dim area, pin As String
area = InputBox("Enter Area", "Area Name")
pin = Application.VLookup(Trim(area), Sheet1.Range("A1:B154800"), 2, True)
ActiveCell.FormulaR1C1 = pin
pl help me
My vlookup works if the cell CF2 does not have a formula in it.
=VLOOKUP(CF2,Sheet2!A1:B921,2) my CF2 cell is =LEFT(M2,3).
I want to look at a zip code, then take the 3 digit code (=LEFT(M2,3) and then vlookup that 3 digit value to find the shipping zone 1-9 in my range.
Hello Eric!
I’m sorry but your task is not entirely clear to me.
For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.
I only need the first 3 digits of the zip code so I used =LEFT(M2,3) M is the customer zip code. My vlookup formula works if I manually enter a 3 digit code in cell CF2 =VLOOKUP(CF2,Sheet2!A1:B921,2,false) if the cell cf2 contains =LEFT(M2,3) then I get #N/A
Hello Eric!
Use the function inside the VLOOKUP function
=VLOOKUP(LEFT(M2,3),Sheet2!A1:B921,2,false)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you so much that worked like a charm!
Hi Eric,
The LEFT function will still return text so needs VALUE around it. US Zip Code are numeric so I believe the formatting to be your problem. you can use this;
=VLOOKUP(VALUE(LEFT(M2,3)),Sheet2!A1:B921,2,false)
Alan
Hi Eric,
It seems as though the issue would be with the VLOOKUP needed a 0 for the last argument. If you are looking for a Zip Code you will want an exact match, and you are currently performing a range lookup.
Failing that, the issue would be with the data and probably the Zip Codes because aside from the missing 0 or False on the end the formulas look great.
Alan
I only need the first 3 digits of the zip code so I used =LEFT(M2,3) M is the customer zip code. My vlookup formula works if I manually enter a 3 digit code in cell CF2 =VLOOKUP(CF2,Sheet2!A1:B921,2,false) if the cell cf2 contains =LEFT(M2,3) then I get #N/A
The other issue then may be formatting. LEFT is a text function so if the value you are looking for is numeric it won't work.
Try wrapping the VALUE function around LEFT. So use =VALUE(LEFT(M2,3))
Alan
Brillant! I could not for the life of me figure this out. Thank you for your help!
You're welcome Eric.
In my formula I've run across another issue that is stumping me.
=VLOOKUP(VALUE(LEFT(M51,3)),Sheet2!A50:B970,2,false) returns a value of 010 (Massachusetts 3 digit zip code) error code #N/A. Did not find value '10' in vlookup evaluation. I've tried every combination of formatting to get the leading zeros in a number. In my zip column M and on the vlookup page. Custom format 00000, 000, automatic number, plain text... Nothing is working for a 3 digit code that starts with a zero.
Message sent, thanks.
I have a large Workbook comprising over 110 worksheets (37mb) and VLOOKUP is used extensively to mine the data into a 'Front Page'. In one part I use two columns to convert Time to a 'Tide Time' annotation;e.g. 10:33:00 is +4.33. In an array of 8 rows, the VLOOKUP correctly presents the correct 'Tide Time' in 7 out of 8 rows but one row falls short by one viz: 10:33:00 is displayed at +4.32. If I isolated the two columns into a separate workbook of course it works correctly but as part of the full workbook this error occurs. The column is simple values, not generated from other calculations but the columns are used by a large number of separate Named Cells. But I cannot identify why this one value is wrong.
Hello Roger!
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
I wondered why vlookup wasnt resolving - I mean it just stayed verbatim even after attempting to resolve - for example "=VLOOKUP(E3,Sheet1!$A$2:$B$2168,2,FALSE)". I discovered the cell was locked - unlocking it fixed the issue.
Excellent work Alex.
I have a column of cells with lookup formulas all drawing data from the same table in another Excel worksheet.
In fact, the cells have been copied down, so they’re identical, except for the relative referenced cell value I want them to lookup. In other words the look up table is locked into the copied formulas.
All the cells with the vlookup formulas work fine, except one. It returns a 0.00. That is the correct format of the cell, but it will not grab the value from the lookup table.
Any thoughts. I’ve tried everything I can think of.
I would need to see it Bob. There will be a reason it is returning this value, or no value.
Hi Alan,
Assigning Range_lookup to False solved my problem.
Thank you.
Best Regards,
Hari
Awesome! Nice work Harinath.
None of the above was the issue in my case. The issue was-
The column where I was doing the VLOOKUP was formatted as “Text” changing it to “General” made it work.
Bless you! This information is EXACTLY what I needed. I was so perplexed why my vlookup wasn't working and it turns out that the Index function with the match is what I needed.
Excellent! Glad to hear that it worked out.
My VLOOKUP is working fine if I unhide the table_Arrays referred but not when those are hidden
when those are hidden, it displays #N/A. its quiet urgent if somebody can help me. It will be great help
I omitted False in the equation so I got a mismatch. Big thanks!
You're welcome Penn.
I too got bitten by "VLOOKUP Cannot Look to its Left". I also could not get "LOOKUP" to work either for my dataset. The above solution is what finally did work for me, crazy that I can't do that in one command. Thanks so much!
You're welcome Gregory. Thank you.
make sure the calculation in the formula bar is on "automatic" rather than "manual"
Nice comment dick.
Great article! - don't forgot that the tabel must be sorted in order
Yes, when performing approximate match VLOOKUPs.
Another mistake is to check your reference list for duplicates. If you think the lookup value is only in there once, you may be mystified as to why the correct value is not being returned, but Vlookup is bringing back the first instance it finds.
Thank you for your comment Julian.
Thank you so much, you have saved me of worries. May you live long brother
Thank you Paul.
You're welcome Paola.
You are awesome! Thank you.
Hi
I'm trying to do a basic V-look up and once I've entered it, and hit return on the first cell, all the cell displays is the formula, it doesn't pull any information. I've had this before but can't remember the fix.
So instead of showing the result I'm looking for, my cell displays as follows:
=VLOOKUP(C2,Sheet2!A:B,2,0)
Any ideas please?
Thanks
Sally
Hi Sally,
It could be that the cell containing the formula is formatted as text. Check the drop down halfway on the Home tab.
Or maybe formulas are set to be displayed. Check the Show Formulas button on the Formulas tab.
Alan
Dear Sir,
I am working with vlookup function since a long time,but nowadays I got my results with some N/A results as unique value is common in both tables and resulting data also presents there but also results N/A for some rows.Please suggest..
Sorry Saroj, I don't understand your question.
The #N/A error will be present if it cannot find what you are looking for. It may be that the formatting of what you are looking for and where you are looking do not match.