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
WOW , it's first time i know "VLOOKUP Cannot Look to its Left " this cause me lose more time . for years i don't know why Vlookup sometimes work and sometimes not :) .
Thanks guys
You're welcome Abdallah.
Hi, My query is i want to use vlookup in book 1 sheet 1, with book 2 to pull out the data, so in book 2 it is not taking the formula. Please help.
You should be able to set this up in the same way as if they were just in 2 different sheets.
Make sure Book1 is open when you begin the VLOOKUP in Book2 and you should be able to select Book1 and then Sheet 1 to highlight the Table Array.
The finished VLOOKUP will be as normal with the Table array showing [Book1.xlsx]Sheet1! at the start.
Hello,
I have an issue with the vlookup/match.
For my case, the lookup value is not a value that I insert as input, but a formula. For example in A1=1 and A2=A1+1.
The vlookup/match function is not behaving normal when I am looking for A2 in a table, ..sometimes it shows the N/A error and sometimes it works. If instead I write over in A2=2, then it works 100%.
Because of the large number of data, this is not preferable for my case.
Is there any solution for this issue?
Thank you!
Madalin
Its always hard to say without seeing it but I would guess it to be an issue with formatting. The formatting of the lookup value and the first column of the table array must match.
Here is a sample of my data..it is possible that is not the optimum way to do it this way :)
A1=2.9, A2=3, A3=3.1, A4-3.2, A5=3.3, B1=12.5, B2=18.2, B3=25.3, B4=33, B5=45,
A7=30
A9=MAX(IF(B1:B5<$A$7,B1:B5)) (=25.3)
A11=INDEX($A$1:$A$5,MATCH(A9,B1:B5,0)) (=3.1)
A12=A11+0.1 (=3.2)
A13=A12+0.1 (=3.3)
A15=INDEX(B1:B5,MATCH(A11,$A$1:$A$5,0)) (=25.3)
A16=INDEX(B1:B5,MATCH(A12,$A$1:$A$5,0)) (=33)
A17=INDEX(B1:B5,MATCH(A13,$A$1:$A$5,0)) (=N/A)
If I change A13=3.3, then A17=45
Thank you!
Madalin
Indeed very helpful. Thanks.
Hi Alan,
I am new to lookup function so I tried as per your instructions (even copied the exact data as yours) but it still doesn't work. What did I do wrong. Please help.
Thanks
Annie
Hi Annie,
Without seeing what you have I cannot really help with this.
It is typically one of the situations mentioned in this article, but to know exactly what is going on I would need to see the spreadsheet + formula.
Alan
Hi Alan,
One work sheet, let say there are thousand of row, lookup value is the same format cell, table array no problem, column index no problem, range lookup is "0"), but the result is different at below:-
7015028 CHUAN HUP SENG CHUAN HUP SENG
7043640 SIN NAM HONG CAFÉ #N/A
The only differences found is the lookup value cell. 7015028 has a caution mark at top left saying "The number in this cell is formatted as text or preceded by an apostrophe", where 7043640 is normal cell. Both cell format as GENERAL.
Thanks.
Hi Keith,
Ok yes, if that column is the first of the table array and being used as the lookup value, then the format of both must match up.
The one with the apostrophe is stored as text, the other is a number. Ignore the general along the top. One is text here and one is a number.
If this is the one working, then convert the others in the column to text also.
This could be done by selecting them as choosing Text from the menu that currently displays general.
If this does not work, you could use this formula in an adjacent column
=TEXT(A1,"0000000")
Then copy and paste values the results over the current table array cells.
Hope this helps
Alan
Hi Alan
Great site and thanks so much for the time you take helping us all!
My issue is I have brought in data from 2 different user's spreadsheets and the source data has leading zeros. We used a custom format o############ for both source and Col.A of Vlookup table - but the source data shows in the formula bar the leading zero whereas the vlookup col A does not show the leading zero in the formula bar and thinking this is why I keep getting N/A?
Hi Molly, thank you.
Yes the format of both columns must be the same for VLOOKUP. If the zeros are showing in the formula bar, it sounds like it is stored as text.
You could use the VALUE function on that columns data to convert it from text representing a number to a number.
Then try the VLOOKUP using that columns data. Good luck :)
This is helpful. Can you tell me how to convert from special to general without losing the leading zero's?
thank you!
Hi Cathy H,
You can try formatting the values as Text from that menu instead of General.
Or use a formula in a different column such as
=TEXT(A1, "00000")
This example assumes your number is in A1 and you need 5 numbers including leading zeros.
These results can then be copied and paste values over the current ones. This will change the format to text but keep the zeros.
Hope that helps
Alan
I have a VLOOKUP problem. I am trying to put the text relating to health hazards into a risk assessment form. So if I select say H302 it puts the text in correctly but other values (they are formatted as text) such as H302+H312 (no spaces) it returns the value for H301 not H302+H312. What am I doing wrong
Sorry should say H302 not H301
Hi Gerry,
Apologies because I am not completely sure what you mean. First thought is to check you have entered False or 0 in the last VLOOKUP argument, so it is not dependent upon order.
I hope you solve the mystery.
Alan
Alan,
Thank you, I had omitted the FALSE entry. Everything is fine now.
Gerry
Great to hear. Thanks for letting me know Gerry.
Hello Alan,
My vlookup values are too lengthy -
eg values:
1) MCDK904745/MCDK904746;ZCL_IM_CRM_ORDERADM_H_BADI IF_EX_CRM_ORDERADM_H_BADI~CRM_ORDERADM_H_MERGE
2) MCDK904884/MCDK904885;ZCL_IM_CRM_ORDERADM_I_BADI IF_EX_CRM_ORDERADM_I_BADI~CRM_ORDERADM_I_MERGE
Thus, when the file is doing a Vlookup for smaller values, they are present, but pulling #NA for these big values. These values are the result of a report so I cannot change these values. But based on these values, I will have to pull data from other columns.
Please advice; what exactly am i missing here and how to tackle this issue
Thanks a lot in advance for all your help
Best regards,
Manish
Hi Manish,
Th eproblem is that it does not like the Tile "~" in the text. You may need to find a way of removing or replacing this for the purpose of the lookup.
Don't need to remove it from the cell if important. Just temporarily for the lookup. Could use the SUBSTITUTE function in the VLOOKUP.
Alan
My vlookup is only working for the top half of my spreadsheet and then stops working on any cell past row 270. My vlookup table is a named range, first column in order by dates, returning column is to the left of the reference column and formatting matches and is not text. Anyone have a clue how to fix it?
Hi Judy, If the VLOOKUP does not look past row 270 I would check out the named range as that would seem the problem. You can edit this named range from the Formulas tab and the Name Manager.
The formatting of the lookup value and first column of the named range must be matching. And the col index num (returning column) must be to the right of the reference column.
The problem in this article on VLOOKUP cannot look to the left can help with this.
another tip: if you're looking up numbers, make sure they are not 'numbers formatted as text.' They'll break the formula.
Thanks Bill.
Yes to add to that... the formatting of the lookup value and the first column of the table array must match. Whether they both be numbers, or both be numbers formatted as text, they must be the same.
I have tried all the solutions the article mentioned, still failed then I tried this, always start the looking table with the value you are looking for in the first column e.g. VLOOKUP(sheet1!A2, sheet2!$C$2:$E$84, 1, FALSE). That is you are looking for value in column C in sheet2 that matches value in column A in sheet1. It is magic! to solve a problem of VLOOKUP that happens when we copy data from different data sources.
Thanks for the tip JimmyNZ.
Yes VLOOKUP will only return the first match. You can use a PivotTable to perform actions like show all invoice # for a particular aircraft.
Otherwise if you wish to populate a bunch of cells with this info then you are looking for a macro probably.
Hi! I worked in airline company and one task I have is to matched the invoice # to a particular aircraft
I used vlookup but it will only return the first match. Sometime an aircraft comes multiple times in a month. How do I solve this?
Yes VLOOKUP will only return the first match. You can use a PivotTable to perform actions like show all invoice # for a particular aircraft.
Otherwise if you wish to populate a bunch of cells with this info then you are looking for a macro probably.
Thank you guys, great page!
Thank you very much!! It worked. I forgot to lock my table.
columns values same but rows values change how can use vlookup formula
E.g.
Same Change Value
Name AAAA BBBB CCCC DDDD
A 15 12 16 22
B 45 26 33 99
C 88 33 78 98
B 22 34 56 37
Hi,
I’m trying to use VLOOKUP to look up values with a combination of FS500000, 1000000, F500000. The VLOOKUP is finding combination FS500000 and F500000, however fields with 1000000 are showing the #NA error.
The cells are formatted as general (also tried switching to text), i've removed "hidden" fields and inserted TRIM within the formula, but still no luck.
It’s just a simple VLOOKUP formula of =VLOOKUP(A9,Sheet2!A1:K10000,6,FALSE)
Any help would be appreciated.
Thanks.
Hi Simon,
I did suspect this would be due to the formatting. General is not adequate, Excel will still view 1000000 as a number.
Formatting as text is good. Just ensure the lookup value and the first column of the table array are the same format. So both need to be text.
Hope it works out.
Alan
I keep receiving a #REF! error when using a VLOOKUP between two workbooks on a network share.
If I have the VLOOKUP workbook open (showing the #REF! errors) and then I open up the workbook that contains the table array - then the #REF! errors disappear and the cells contain the correct data. It seems as if I must have the source workbook open in order for the link between the two to function correctly. Do you have any suggestions that I could try?
i make a table using vlookup but its not working properly.
in same row some times show result okay some times not okay. how to resolve. pls help
I can't really explain without more information. Maybe check the typing of the entries.
You say it works for some and not others. Sounds like a mis-type on those entries not working. Or maybe the second reason above.