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.
150 comments
iii. In total your formula will look like very similar to this: =IF(ISNA(VLOOKUP(H2, ‘Quarter 4 MRNs’!$B2:$B$45, 1, FALSE)), “Not Duplicate”,””)
iv. Type in =IF(ISNA(VLOOKUP( then select the cell H2
v. Continue the formula by inserting a comma, then click on the Quarter 4 MRNs tab select B2 and scroll down to include all of your previous weeks MRNs.
vi. To complete the formula, type in the following , 1, FALSE)), “Not Duplicate”,””).
vii. To save time click on that formula value and adjust the following B2:B45 to $B$2: $B$45. Now instead of retyping this formula for each MRN listed, you can scroll to the green box, click on it, and scroll down to the end of your list.
viii. Hit Return.
ix. Your column will list the value “Not Duplicate” when the patient’s MRN is not found in any previous weeks; if the cell is left blank you can eliminate that patient from the list because their information will already have been provided to the screeners previously.
So this formula works, I delete MRN's that show up again in a 6 month period of time. The change is now I have to pull the data using a new tool, long story short, it exports data originally as a CSV file, I then save it as an excel file, and move it to my "Eligibility List" excel file. Since then, the formula will not recognize the Quarter 4 MRN's tab, even though it is now a part of that document. Help?
Hi! If you delete a worksheet and then create a new worksheet with the same name, your formulas will not work. You get #REF! error. Also, the references in your formulas will change if you delete the rows or columns they refer to.
if you are forced to delete a workbook and remake it. Highlight the column(s) then press ctrl F & use the replace function. search for #REF! and replace ALL with '[workbookname.xlsm]worksheetname'!
And another. Your lookup table reference is larger than your table. As my lookup table gets larger, daily, I am forced to increase my lookup reference size. This created an issue. It would be nice to not have to manually change the lookup reference but......
Hi!
If your data range changes dimensions, we recommend using an structured references in Excel tables or a dynamic named range. For more information, read Excel dynamic named range: how to create and use.
Let me add a seventh reason. Your excel calculation is set to Manual rather than automatic so if you go in the search bar and type in "Calculation" you will be able to see what your setting is for that excel workbook.
You just saved my life!
Thanks a lot for this!!!!
Hi,
I have an issue with items having "~" symbol (For example, "18 KT GOLD JEWELLERY ~ GVACW"). In this case neither "VLOOKUP" nor "INDEX/MATCH" are working.
Request your suggestion on the same.
Hi!
The following tutorial should help: Excel wildcard: find and replace, filter, use in formulas.
For example, to match "abc~" use double tilde
=MATCH("abc~~",A1:A10,0)
Is it possible to use vlookup for 4+ sheets? I was following the instructions and trying to connect 4 sheets but to no luck.
Hello!
Here is the article that may be helpful to you: VLOOKUP across multiple sheets in Excel with examples. Hope this is what you need.
Hello :) I have a question. why when I search my value with VLOOKUP like this:
=VLOOKUP(E8,car_rental!$D$10:$BC$106,52,0) everything works but when I want to insert the VLOOKUP to an if statement like so:
=if(VLOOKUP(E8,car_rental!B10:E10,1,0)="",VLOOKUP(E8,car_rental!$D$8:$BC$103,52,0),"")
the very same VLOOKUP doesn't work reason: Did not find value 'value' in VLOOKUP evaluation.
what am I missing? it's the same range the vale is in column D
thanks in advance
Hello!
I'm assuming you're using a condition when the VLOOKUP function doesn't find anything. I recommend reading this guide: Nest IFERROR within VLOOKUP to always find something. I hope it’ll be helpful.
When I apply VLOOKUP showing this dialogue!
"Invalid reference. This file version can only have formulas that reference cells within a worksheet size of 256 columns (column IW or higher) or 65536 rows."
Please help me.
Hi!
Save the file in xlsx format.
In Excel 2007 and higher maximum are 1,048,576 rows and 16,384 columns.
(Sorry for my english) Hello, my Vlookup is not working, I realize that is because the sheet people send me to work on has a different format for the lookup_value. This is the formula I use:
=VLOOKUP($A146;SHEET2!$A$1:$S$216;2;0)
For $A146 this is an example, if I have "prednisone 20 mg" in the sheet I want the data and "PREDNISONE 20mg" the formula gives me N/A.
Is there a way to use VLOOKUP with lookup_value that matches just the text and not the exact format of cell and text?
Thanks in advance for the help.
Hello!
You can chance all letters to lower case using the LOWER function.
=VLOOKUP(LOWER($A146),LOWER(Sheet2!$A$1:$S$216),2,0)
But in your issue, it won't help as your values differ by spaces.
Have an issue with lookup, quite some time spent on searches, hope will get info here :)
so the problem is like that, the search criteria for the lookup is a cell that uses concatenate, when constructing the formula it the search criteria resolves, but when vlookup is actually trying to find it, it fails. typing in the concatenate result works fine, same is even if i just reference the cell using =
Hi Tadas, it is difficult to fully understand the what is happening from the text. I would need to see the data to understand the issue.
Thanks for the tips! My issue is, I kept getting "#N/A", despite ruling out all the potentials as advised above, and I think it has something to do with the format or type of the data behind and between two files, though they appear to be the same. Any advise? Thanks!
Yes, this could well be a formatting issue. They need to be the same data type. It may be that you have one column with numeric values and the other with numbers stored as text.
You can check this by selecting some of the cells and looking down at the bottom right to see if they are summed in the Status Bar.
Hi
Thanks a lot for reading me
I wrote a Vlookup which take value from another sheet.
=VLOOKUP(E16,Base!E:J,4,0)
my issue is that when I copy it to next line, it keeps taking previous line value.
How could I fix this?
Thanks a lot
My guess would be that the value found is not unique. VLOOKUP will return the value for the first match it finds. So, this may be that previous line value.
Hello,
I am having the same issue and vlookup is pulling data from row 1 only and repeating same values in row 2. How to fix this?
I used offset n index and it works fine but why not using vlookup + choose.
Pls advise
Thanks
Hello!
Here is the article that may be helpful to you: How to use VLOOKUP to get 2nd, 3rd or N-th match.
I hope it’ll be helpful.
I have used Vlookup to link destination sheet to master worksheet but when am updating the master sheet, the destination sheet is not updating
Check the entry is exactly the same as the one searched for. Also check the table array argument is the correct size.
It is hard to say from here what the issue may be.
Also, when you vlookup a cell and the lookup array is a text format - it shows error. For that convert the lookup array to general - then use text-to-columns feature to apply the general condition. This was really funny for me to find out.
Glad you had fun finding that out, Jasbir.
Thanks a lot, my problem got solved. Was unaware that 'VLOOKUP Cannot Look to its Left'
You're welcome, Siddharth.
Thank you - my formula was driving me made and one little change has made it perfect. :)
Super! Nice one, Sanchia.
Thank you so mush!
my mistake was in 'VLOOKUP Cannot Look to its Left'
You're very welcome, Alyona.
My issue is that I work with duplicates that are needed.
Pivot table is recommended, however, the result in my case is text not a value so pivot table is not really suitable.
Is there any other function I could use?
Hello!
I recommend trying to use the combination of INDEX + MATCH functions.
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.
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.
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.