VLOOKUP week enters its second day, meaning that it’s time to have a look at how to problem-shoot #N/A errors. On the online forums I frequently see frustrated users asking why their VLOOKUP formula returns an #N/A error, so I hope that some of the reasons I’ve listed below will be helpful to many of you.

Exact Match #N/A

By far and away the most common reason for an exact match #N/A error is that the lookup_value doesn’t exist in the lookup column. Sometimes you can be virtually certain that it does exist, but the difference can be extremely subtle so don’t be surprised if you’ve missed it. In case you’re wondering what “lookup column” means, it was defined in the previous post.

Here are three hard-to-spot examples :

Date Mismatch

In this example we’re trying to find a date in a list and return the corresponding value in the adjacent column. As an aside, I should mention that I live in the UK so my dates are in a dd/mm/yyyy format. We can see that the date exists in the lookup column, so why is the exact match formula returning an #N/A error?

Appearances can be deceptive. The first thing to do is to check whether VLOOKUP is correct in that the two 03/01/2012 values are not the same. In a spare cell we can enter the simple formula =H2=E4 to check; it returns FALSE which confirms that the dates indeed do not match:

Cell formatting can change the appearance of a cell without changing the underlying value. If we click into the cell we can see that, in this case, the date in the lookup column also has a time stamp of 02:24:00 :

The time stamp explains why VLOOKUP can’t marry the two dates together: they’re different. The correct resolution to this problem will vary depending on your situation: most likely you need to remove the times from the lookup column, which can be done very quickly using Text To Columns.

Text Mismatch

Here’s a similar problem except that we have some sort of mismatch between two pieces of text rather than dates. They’re both spelt exactly the same, so why is VLOOKUP returning an #N/A error?

The first step is to perform a direct comparison just the same as we did last time. The formula =H2=E4 returns FALSE confirming that the two values are not the same. The next check to perform is whether or not the length of the two strings is the same. The formulas =LEN(H2) and =LEN(E4) reveal an interesting difference: the length of ADMIRAL GRP in the lookup column seems to be one character longer than the lookup_value:

This is because cell E3 has an extra space character after the word GRP. This can be clearly seen by clicking into the cell and looking at the position of the cursor which is not directly beside the letter P:

Once the extra space has been removed the two values will match.

Type Mismatch

This difference is the most subtle of all. In the below table we have a VLOOKUP formula which should return Paul, but for some reason it’s returning an #N/A error:

Again, the direct comparison formula =H2=E4 returns FALSE, confirming that the pair of 3 values are not the same. Formatting could be hiding the problem – much the same as the date mismatch formula – but clicking in both the cells confirms that there are no decimal points in this particular case. On this occasion, the difference is caused by a data type mismatch. Excel recognises several data types including Number, Text, Logical, Error and Array. The data type can be checked using the TYPE function:

The TYPE formulae tell us that the 3 in the lookup column is actually “3” stored as text, whilst the lookup_value, 3, is a number. For more information on this have a look at the TYPE worksheet function topic in the Excel help file.

VLOOKUP is type sensitive which is why it considers the two to be different. In fact, it turns out that, in this case, all the numbers in the lookup column are numbers stored as text; they can be quickly converted into number types using Text To Columns.

Approximate Match #N/A

The issues above may also apply so they should be considered but here are a few other potential problems which speak for themselves:

The lookup_value is less than the smallest value in the lookup column.

The lookup column is not sorted in ascending order. This will cause varied results: sometimes you will get the correct value, sometimes you’ll get the wrong value and sometimes you’ll get an error.

If you’ve frequently experienced an #N/A value which isn’t covered by any of the above then post a comment and let us know!

I am facing a problem with VLOOKUP, maybe you can help me out here. So the formula is simple, =VLOOKUP(A1, C2:D200, 2,0), where A1=Today(). I notice that this throws up a #N/A, because the date cell reference is a formula (Today()). Now, I need this formula in place because A2, A3, A4 would be Today()-365, Today()-182, Today()-91 respectively. Is there a way by which VLOOKUP can reference a cell which contains a formula and give me a proper result?

It should work perfectly fine with the reference cell containing a formula.The TODAY() function returns a whole number. Your VLOOKUP() formula is performing an exact match so the #N/A result tells you that it can’t find an exact match. If you check column C and find a cell which you think contains an exact match, you can then follow the steps in the blog article to identify the problem. The most likely causes are that either column C contains not just a date but also a time (ie. a decimal portion) or column C has dates (which are numbers) formatted as text.

This table acts in the same way as the original table, and gives me the same errors. I am sure that every value in the table is different.

I want to look up the lowest value for a column and return the name of the row. I have created a cell, say B2, that returns the lowest value of, say MAD (if that is my criteria), to make sure that the values are the same. I would think that the function would be:

The problem you have is that the name column is to the left of the lookup column. When you use a standard VLOOKUP() formula, the lookup column needs to be on the left, so that’s why you’re having problems.

One solution would be to move around the columns in your table, but I expect that that isn’t really an option for you. Another, very common, workaround is to use an INDEX() and MATCH() formula. This formula is a bit more complicated than a VLOOKUP() formula but it gives you the flexibility you need to lookup values “to the left”.

If we imagine that your table Array1 is in the range A1:D5 (with A1 being an empty cell) then the formula you need to find the name corresponding to the minimum MAD value is:
=INDEX(A2:A5,MATCH(MIN(B2:B5),B2:B5,0))

I am experiencing N/A error even though the value is TRUE when doing a comparison. My formula is =VLOOKUP(A2,Sheet2!A:B,2,FALSE) where I am doing this in Sheet 1. I have tried Trim, LEn and everything matches but still get this error. any help?

Hey.. I am applying Vlookup and in mostly cells i am getting values but in some cell i am getting #N/A. Rahter i chcked manually that value is there.. but still its giving me #N/A.. hope will get some inputs from you guys
Thanks

It sounds like you have written a vlookup formula in a cell and then filled it down a column or across a row. The first thing to check is that you correctly locked the row/column references of the table_array before you filled the formula. For example, if you have this formula in cell A1:=VLOOKUP(B1,E1:F20,2,0)
If you then fill that formula down to A2, it becomes:=VLOOKUP(B2,E2:F21,2,0)
The table array has changed to E2:F21 because the rows were not locked. To lock the rows you need to put dollar signs infront of the row numbers before you fill the formula down:=VLOOKUP(B1,E$1:F$20,2,0)

If that all looks okay then double check that the value you’re looking up is in the left-most column of the table_array. It must be in the first column.
If that looks correct too then you need to work through the examples on the blog post. Sometimes two values can look the same but they’re not. If your lookup value is a string then check for any extra spaces in the lookup value or the lookup table. If your lookup value is a date or number then check its data type (as shown in the blog post) in both the lookup value and in the lookup table because VLOOKUP() is data type sensitive.

Thanks – the ‘$’ fixed my issue. I was scanning barcodes, the Vlookup formula remained the same and all of a sudden the same barcode would stop working after a random number of lines. I put in the ‘$’ signs and all are now returning the info they should be. I don’t understand why they worked AT ALL for the first random number of lines (8/10 etc) and then perhaps sporadically – but thanks a lot -it seems to have fixed my issue! much appreciated.

Colin
Such a simple one but can’t get it to work. I tried the direct comparison =B2=E3, which gave me true but I still get N/A for a look up:

=VLOOKUP(B2, D2:E6, 2,FALSE)

So I have a column of letters with numbers in next cell (A…1, B….2) in column B and in E, trying to find B2 value (number 1) in D2:E6 range column 2, which is the number column, just comes up with this N/A.

A golden rule with VLOOKUP() is that the lookup column must be the first column in table_array. In your example, the first column in table_array is column D, but the column you’re trying to look up with is in column E. That’s why VLOOKUP() is returning an #N/A error. This problem happens quite often – if you look at the previous questions you’ll see that Rune had a similar situation.

There are a number of things you can do to get this to work. The first option is to switch around the data in columns D and E in your lookup table. Quite often this isn’t something one would want to do, in which case a different formula is required. The most common formula workaround is to use INDEX() and MATCH() instead of VLOOKUP(). In this case the replacement formula would be:

=INDEX(D2:D6,MATCH(B2, E2:E6,0))

So, in that formula, MATCH() finds the position of the first value (looking down the column) in E2:E6 which is equal to B2. INDEX() then returns the value in D2:D6 which is in the same position.

A less common (and slightly more complicated) formula is to use VLOOKUP() with CHOOSE().

I am having the same problem but all the tests are true. I am basically trying to VLOOKUP a text value that begin with “^”. My table is sorted, so VLOOKUP is not referencing the first entry within my table but it will reference the same entry if its entered as a duplicate. My only work around is to place a space entry within my first row of the table to force VLOOKUP to being reference from row 2. How can I fix this?

I’m sure I can help you with this, but first I need to get a better idea about what’s going on. Please would you post your VLOOKUP formula and, if possible, a few example rows from your lookup table? Or, if you’d prefer, you can send me an example file (please remove any confidential information from your workbook) – my email address is on my ‘About’ page.

I’ve simplified it both to make problem-shooting it easier and also so that everyone else can follow along.

The fourth argument is omitted which means that your VLOOKUP() formula is performing an approximate match. For VLOOKUP() to do an approximate match correctly, the data in the lookup column must be sorted ascending. I checked ‘Symbol List’!A:A and your data is sorted ascending, which is good. However, the problem is that row 1 in column A is actually a header row. The text in the row header (in this case “Currency”) isn’t part of the sorted data, so it’s messing up things. To fix it, you can delete the empty row 2 which you had inserted and then just amend your formula to start from row 2 (where the data begins). So like this:=VLOOKUP(Main!B2,'Symbol List'!A2:C30,1)

Finally, since you’re only returning the value from the 1st column of the table, you can reduce the table_array from A2:C30 to A2:A30 like so:=VLOOKUP(Main!B2,'Symbol List'!A2:A30,1)

Hi Colin,
I have a very similar issue to where I get the #N/A even though the reference collume contain the value I am looking for. However when I double click into each single cell the value will display. The length, type, and value are all true and there is no issue there. Can you advise to why I have to double click into every cell for vlookup to work. Calculations is set to Automatic. I am going nuts here. I spend most of my day searching for a solution and can’t find a fix.

Wow that sounds very frustrating indeed. It’s hard for me to identify what the problem is because I can’t see the formulas or the underlying data but it sounds like there is a calculation dependency tree issue. This could be caused by (for example) circular references or a UDF which is poorly written.

If you e-mail me a simple, example workbook which demonstrates the problem then I’d be more than happy to take a look at it for you. My email address is on my About page.

I did everything you mentioned and it did not work. But here is the trick that fixed the issue of #N/A when all the data are the same and everything is TRUE and the only fix is to click into every single cell then enter (very long process). Here is the trick:
Highlight the look up value in the array (only the value you are looking), then click on data > text to columns> next > next > finish….. BINGO…

Thank you for posting your solution. It’s a shame we didn’t get to the root cause of the problem, but I’m sure that your post will help some poor soul who’s going through the same as you did. Well done on getting it fixed!

Hi, I have also had issues with excel formatting and text to columns.
Specifically when importing data from outside applications into excel, the formatting would look correct, check out as correct but not “work”. Running text to columns seems to clean the formatting somehow. It would affect vlookups as described by the op. Horribly frustrating until you find the fix….
I cannot give an example now as I no longer work in the same position, I just stumbled across this while looking for a different excel answer…

Hi,
Really hoping you can help me with a VLOOKUP that returns a blank, which I am assuming is a #N/A in disguise. I inherited a workbook with formulas, there are 5 tabs, the formula is in one, and references another tab.
FORMULA: =IFERROR(IF(LEN($C147)<5,VLOOKUP("0"&LEFT($C147,5),'4. Static Data'!$A$2:$F$1048576,MATCH(H$2,'4. Static Data'!$A$2:$F$2,0),FALSE),VLOOKUP(LEFT($C147,5),'4. Static Data'!$A$2:$F$1048576,MATCH(H$2,'4. Static Data'!$A$2:$F$2,0),FALSE)),"")
If you need the workbook happy to send as I have been trying to figure this formula out for weeks.
I have done all of the LEN, TYPE etc formulas and they all return true. I also tried Gus's suggestion, which didn't help.

Yes, the IFERROR() function is hiding an error result there and it’s very likely that the error is an #N/A error.

To make it easier to understand I’ve factorised your formula to make it less verbose:
=IFERROR(VLOOKUP(IF(LEN($C147)<5,"0","")&LEFT($C147,5),'4. Static Data'!$A$2:$F$1048576,MATCH(H$2,'4. Static Data'!$A$2:$F$2,0),FALSE),"")

We could also get rid of the VLOOKUP() entirely and replace it with INDEX(), but that’s a story for another occasion.

There are two functions in your formula which commonly return #N/A errors: VLOOKUP() and MATCH().

The MATCH() part of your formula looks like this:
MATCH(H$2,'4. Static Data'!$A$2:$F$2,0),FALSE)

Here MATCH() is performing an exact match, so it will return #N/A if it can’t find the value in H2 in ‘4. Static Data’!$A$2:$F$2.

The VLOOKUP() part of your formula is also performing an exact match and will return #N/A if it can’t find the C147 value (preceded by a 0 if C147 is less than 5 characters) in column Static Data’!$A$2:$A$1048576.

If you’re still struggling to find the problem then you’re welcome to email a simple spreadsheet which demonstrates the problem.

The value in C36 is 16235 and it is a number data type.
If we look on the static data sheet, A66 contains the value 16235 and it is a number data type too. You did the checks I suggested on my blog correctly.

The problem is the LEFT() function in your formula returns a text data type. So although the number 16235 gets read by your formula, the LEFT() function changes it to the string "16235" before VLOOKUP() uses it. Let's prove this theory before we look at a fix.
— Open a new workbook and type 16235 into cell A1.
— In cell B1 put in the formula =TYPE(A1) and it will return 1 which tells you that A1 contains a number type.
— In cell A2 put in the formula =LEFT(A1,5) and it will return 16235.
— In cell B2 put in the formula =TYPE(A2) and it will return 2 which tells you that A2 contains a string type.

Okay, let’s put the theory to one side because now it’s time to fix your workbook. What you need to do is change the 16235 in the static data sheet so it is a string. This is easy to do: select column A on the static data sheet, press CTRL+1 to open the format cells dialog, choose Text from the category list and click on OK. Then go to cell A66, press F2 and then enter. You’ll also need to go to cell A67, press F2 and enter as well. Once you’ve done this you should see a small green triangle in each of the cells and, if you select one of the cells you should get a small exclamation mark next to the cell with a dropdown which, if you click on, will tell you that it is a number stored as text. In this case, that’s exactly what you want and your VLOOKUP formulae should work, so don’t choose the option to convert Text into a Number. Incidentally, if you look elsewhere in the column you’ll notice that there are some other numbers stored as text in there, so I suspect that someone has hit this problem before.

I have been using VLookup for days now and suddenly I am only returning #NA results, even though my spot checking verifies the values match. My formula is =VLOOKUP(Z34983,'[UPC codes & Price Markup for Day Brite.xlsx]Sheet1′!$A$1:$B$52,2,FALSE). I have numerous rows of data in which I am referencing/matching the UPC number to pullover net price into another spreadsheet (provided by client). It seemed to be working wonderfully up until this afternoon. I have saved, closed excel, restarted the process and still running into the issue. I am “pulling my hair out” frustrated and must get this project done already. Any ideas?

Oh, some things I have already confirmed: my table array did have spaces after the UPC numbers but I knew that and have been getting rid of them all along. I am using the same excel versions as I have been and I can’t think what’s changed. Unless it is Friday, and a major ‘user error’ that I can seem to see. :(

Your formula looks fine so it would seem to be a data issue. If you consolidate a simple example which demonstrates the issue into a single workbook and email to me, I’d be happy to take a look for you.

I have two tables, each on its own tab. The first tab is Prices. The second is March 2013. Prices contains a small 6 row by 2 column table. Abbreviated days of the week are in the left column (B) and the price for each day is in the right column (C). The full table range is B2:C7.

The table on March 2013 is a compilation and calculation table designed to derive the totals per day and month.

The command I have set up is on March 2013 and is intended to retrieve the price for a given day from Prices, and multiply it by the quantity in the C column of the same row on March 2013:
=C7*(VLOOKUP(A7,Prices!$B$2:$C$7,2)).

A7 is the abbreviated day of the week using =WEEKDAY() on the date in column B on March 2013.

The formula returns properly for everything except Fri. Every reference using Fri or Friday turns up #N/A.
I used =VLOOKUP(“Fri”,B2:C7,2) directly in the prices tab to confirm it and used an exact copy of the formula with “Thurs” at the same time. Thurs works but Fri doesn’t.

I’ve changed the left column in the Prices table from General to Text with no apparent change.

I’ve made sure all the text fields are exactly the same, both by testing and re-entering. Yet, for some reason, Excel seems to disagree with Friday and Fri.

Does Microsoft have issues with the best day of the week or something? At first, it had issues returning the correct values for Thurs, Fri, and Sat. That was fixed by putting the rows in alphabetical order. However, Fri still returns #N/A for some reason.

As an extra note, I have discovered that shortening the reference range to B2:C5, so four rows, allows the Fri references to return correctly. As soon as it goes up to five or more rows, Fri goes AWOL.

I am having this problem. the “=” comparison shows “TRUE”, len() shows same, type() also shows same. Still vlookup gives an #n/a. Anything else I can check. Font and formatting is also exactly the same. Anything else I can check?

I assume that Allexpenses is a named range which references your whole table, so the issue is with the MATCH() part of your formula. You want MATCH() to look across the table headers to determine which column position you want to return, so you need to tell it to look across there rather than looking at the whole table.

Suppose your Allexpenses named range is in the range F1:M1000.
Create a new named range called AllexpensesHeaders and give it a reference to the first row of the table. In my example this would be F1:M1.

I have used VLOOKUP for a while so I know what I’m doing, but this case is just odd. On one hand I have a table of divisions giving me rounded numbers and then I have a table of all rounded numbers written by hand. When I do a VLOOKUP I can’t find a handful of this numbers. I copied and pasted the value of one of this numbers and put it side by side with a hand written number. When typing =A2=B2 it comes out true, they are the same type, I check to the 15th decimal and they are the same but the VLOOKUP just can’t find it.

This sounds like a floating-point rounding issue. If it is then I think the easiest way for you to resolve it would be to edit the formulas in your table of divisions to include a rounding to a number of decimal places which is reasonable for your project. You can do this by using the ROUND() worksheet function, eg to round 1/3 to 5 decimal places:

=ROUND(1/3,5)

Once you’ve adjusted your hand written numbers to be the same number of decimal places then your VLOOKUP() formulas should be happy.

Thanks very much for your information. It really helped me out a lot. However, and I probably am stretching things a little here, I have a question after using your info and links. I have a workbook with a lot of info which looks like this:

Imagine that there are several other ‘hero’ names and various stats. In a ‘team’ composition sheet I’ve come a big way with all the help, it now finds the highest WR and KDA per selected hero. A little like this:

The win% and the KDA is found with the following array formula:
=MAX(IF(Totallist!A:A=’Team Composition’!A9;Totallist!E:E)) and
=MAX(IF(Totallist!A:A=’Team Composition’!A9;Totallist!I:I;))
That works splendidly, thanks!

However, finding the corresponding names (wie% and wie KDA) is something which I can’t get working. I can get it working by manually selecting ranges, like this:
=INDEX(Totallist!$F$103:$F$109;MATCH(‘Team Composition’!B9;Totallist!E103:E109;0))
but I would rather have it work the same with a nested IF function and let it only index if the criteria is met. Tried a lot of things, but or too few arguments or a #REF error.

And (I know, I’m really stretching it :-)) would it be possible to do something as follows:
a team consists of 5 players. Each of them plays a certain hero. If i put the heroes in a certain order it should bring up the ‘best scoring’ player for it. However, if on line 1 for hero 1 player ‘Peter’ is found, he should be excluded for hero 2-5. I tried to work with the ‘solver’ to see if it could maximize for example the ‘total’ KDA in a team of 5 players playing 5 heroes, but I failed miserably :-)

Hmm.. one more thing. If in the complete workbook I apply a filter (for example, only show the stats for heroes which a certain player has played at least 10 times) in the aforementioned formula’s it still searches the complete set of data instead of the filtered set. Is that something which can be solved in those formula’s?

Found it!
=INDEX(TotalFilter!F:F;MATCH(‘Team Composition’!B94;(IF(TotalFilter!A:A=’Team Composition’!A94;TotalFilter!E:E;0));0))
Sometimes, it’s just very simple and am I making it difficult for myself.

I have two list of dates and trying to pull certain numbers from an old list. The Vlookup returns a value on the first datea, however, does not work on any of the subsequent dates. I did the len and type errors and both match on the subsequent dates, however, excel still says the dates do not match. So I get the #N/A error instead of the value that I want. Any thoughts on how to check the error or what this could be?

A few things to check:
(1) Did you lock the table_array reference (with $ signs) before you filled it down the column/across the row? If not then the lookup value may not be in the range referenced in the formula.
(2) If the dates in the lookup column are not sorted you need the VLOOKUP() to be an exact match (ie. Range_Lookup needs to be False).
(3) Do any of the dates have times on the end of them? Cell formatting might hide this, but you’ll be able to see the times in the formula bar.

If you’re still having problems then send me a sample workbook (less any sensitive data) and I’ll take a look at it for you.

I was Googling for reasons why my simple VLOOKUP function isn’t working and came across your blog. Although it was informative, none of the reasons you gave in that particular blog helped. I’m getting a #N/A result for some entries and incorrect results for others. I attach the spreadsheet for your reference. Your blog says you work(ed) for a large financial company so the spreadsheet could be easily understood, but what I’m trying to do is show a client the potential results of putting money into a pension. Specifically, at this point I’m stuck at, I want to automate the response Excel gives to how much additional tax relief a client can expect, depending on what rate of income tax they pay.

Hi Tom,

Your VLOOKUP() formula in B17 is this:

=VLOOKUP(B16,J1:K4,2)

You are passing in 3 arguments into VLOOKUP(). The 3 arguments are B16, J1:K4 and 2.VLOOKUP() actually takes 4 arguments and the important thing to know is that VLOOKUP() will do an approximate match if the fourth argument is omitted or if the fourth argument is TRUE or 1.
So these three formulas would all do the same thing:

When you an approximate match with VLOOKUP(), the data in the lookup column (J1:J4) must be sorted. This is covered in my blog post.

In this case, I think you’re using an approximate match by mistake: I reckon you want to do an exact match instead.
When you do an exact match the data in the lookup column doesn’t need to be sorted.
To do an exact match you must pass either FALSE or 0 in as a fourth argument, so either of these formulas should work for you:

I am sooo so frustrated. I feel like this is a very simple formula and it is returning all #N/A. I have done all the trouble shooting mentioned above. Everything matches. My formula is =VLOOKUP(B2,TABLE10,4,FALSE). This particular formula has worked for me in the past and now it works on nothing. I feel like it is my excel software as opposed to the formatting in the spreadsheet.

I am attempting to use the vlookup with this formula =VLOOKUP(B36,A62:B46661,2, FALSE), seems to look right, I have ensured the cell and the table are the same format. I have not included the table header (I get the N/A#) regardless. The table is simple with the column A containing $.01 to $466.00, and Column B having a specific fee ie $1.50 to $5.00. The cell I am asking the vlookup to find contains a formula on a different sheet that is =B34/B22, which is formatted the same as the table. Please advise what is amiss? I’m going out of my bird trying to figure this out.

Your exact match VLOOKUP() formula looks good to me. The number you are looking up is calculated by a division formula, so this looks like a rounding issue – possibly even a floating-point rounding issue which happens because some decimal numbers can’t be fully represented in binary. This problem has bitten a few other people who have posted comments on here.

If that’s the case then the way to fix the problem would be to either change your precision as displayed setting (which I generally would not recommend) or you can apply rounding in your formula:=ROUND(B34/B22,2)

If that still doesn’t work then it’d be worth ensuring that the numbers in the lookup column are rounded to 2 decimal places too and, failing that, email me an example.

Thank you so much.
In my case, I could’t match a number from one workbook no another.
I tried the =XX=XX formula and returned FALSE.
The problem wasn’t my vlookup formula but the numbers in one of my workbooks.
I solved it by copy-pasting all the column numbers over themselves.
The autocorrection asked if the numbers should be copied as numbers or actual text. I went for numbers and voilá! It switched from FALSE to TRUE and of course, my vlookup formula returned an actual value instead of a N/A

From the information on your comment, I’d say the main 2 possible reasons for the error which spring to mind are:
1. When you drag the formula down you haven’t locked the necessary cells so the dragged VLOOKUP() formulas don’t have the correct range references.
2. Time values in MS Excel are floating point numbers, so this could be a floating point rounding issue. Please see previous comments which discuss this.

Good morning,
Thanks for the explicit help. Forgive me if I missed this, but I haven’t seen in the examples above the following error.:

Data Validated List used as the Lookup Value to return data from a Table.
Here’s my formula.
=VLOOKUP($H$6,Table3,2)

The table contains text and numbers, the Data Validated List is the first column (M) of the table.
ex. row/column data:
M16:20 Maximum: Detailed research
N16:20 Review data.
016:20 Narrow to one or 2 segments
P16:20 1-2 weeks

Some of the rows work whereas others do not. In the other vlookups in the doc, it returns the wrong row, such as (original formula: =VLOOKUP($C$3,Table13,2) ) choosing C2 for D2 returns instead: D4….

What I’ve tried in both instances.:
– Eliminating spaces at the end of the sentences
– formatting the cells as text
– INDEX and MATCH (and the more complicated vlookup mentioned in the same response from above)
– True/false proofs
– type checks

An interesting update. When words that start with an M, A, E (and potentially others) are placed in the validated list they return an N/A.
When an S or P word is placed there it returns the correct answers.

The lookup column needs to be sorted ascending because your VLOOKUP formula is doing an approximate match. If you pass False into the 4th parameter to make it an exact lookup then the lookup column won’t need to be sorted.

Hi Colin,
Odd issue here with vlookup. I have a set of recipes all of which reference a data sheet in the same workbook using vlookup. Data sheet is about 90rows of data (about ingredients). In the past I have inserted additional rows in the middle of those 90 rows to add new ingredients with no trouble at all. Today, when I inserted a new ingredient, all my recipes that have a certain code (=text of 00000, which results in BLANK entries after lookup) come up with lookup results N/A. This has not happened before. Any clues?

Please see attached sheet. I have culled all recipes except for one, SM119.

The Ingredients are in the Database tab.

If I add a new row in anywhere after row 4 in Database, the vlookuptable in SM119 from N6 generates NA results if the Code in K is ‘000000’. We have used this without any worry for a long time, adding in new rows, but something seems to have gone wrong now.

Thanks for emailing me your workbook. The VLOOKUP() formula in SM119!N6 is

=VLOOKUP($K6,DATA,4,TRUE)

The TRUE part of the formula tells VLOOKUP() to do an approximate match. When VLOOKUP() does an approximate match, it is essential that the lookup column (the first column in the lookup table which, in this case, is a named range called DATA) is sorted is ascending order. If the lookup column is not sorted ascending then you will get unpredictable results: it might return the correct result, it might return #N/A or it might even return a wrong value! In this case I think your safest option is to change your formulae so that they tell VLOOKUP() to do an exact match. To do this, just change the TRUE to FALSE, like this:

Colin – I didn’t leave a comment in your post about VLookup returning #N/A, but I saw that someone commented on it today and you had replied to email them the workbook. So I’m doing the same :)

Your article worked great, but for some reason it’s not working on every line. This is a spreadsheet for fantasy basketball, where I figure out the best team using projected points. The name columns on each tab are a little crazy, because I had to merge them on Sheet 1, then do the Proper() of the column D and put it in Column E. I also did the same on Sheet 2 so they would match exactly.

It comes back with the value for most of the players, but not all of them. The example that I have been trying to figure this out with is cell E25, Blake Griffin Lac. On sheet 2 he is there in cell D24. They come back as FALSE when doing =E25=D24, but all the other tests you suggest come back as they are the same.

I’m hoping you can help me out with this, as I’ve just been entering them manually for quite some time and it’s a real pain in the butt! Thanks in advance for taking a look.

If you look in sheet1, you’ll see that Blake Griffin is in cell E24. Your formula references Sheet1!E25:F274, so it can’t find him (E24 is not in the lookup range).

This has come about because you’ve put the following formula in cell H2 and then filled it down the column without locking the E2:F251 reference.

=VLOOKUP(E2,Sheet1!E2:F251,2)

What you need to do is change the formula in cell H2 to the below to lock the row references:

=VLOOKUP(E2,Sheet1!E$2:F$251,2)

and then fill it down the column. That fixes the first problem.

The second problem is that your formula is doing an approximate match. This is fine in the sense that the data in Sheet1 is sorted in ascending order, but the problem is that Sheet1 is “missing” some data. I’ll explain what I mean by “missing” in a minute. The result of this is that your approximate match formula is returning some incorrect results (ie, projected points for some players are wrong). To demonstrate this, make the correction to the formula I suggested above and fill it down column H. If you look at Alex Burkes, you’ll see that his projected points returned by the VLOOKUP() formula are 3.1. But, if you look at Sheet1 you’ll see his projected points should be 17.925. The reason for the mismatch is on your NBA sheet, Alec Burks’ team is Uta but on Sheet1 it is Utah (with an “h”).

Because you have inconsistencies in your data which are being masked by the approximate match, I think you should use an exact match instead. This will highlight the problems so you can fix them. Change the formula in H2 to this:

=VLOOKUP(E2,Sheet1!E$2:F$251,2,FALSE)

and then fill it down the column. The FALSE at the end of the formula tells VLOOKUP() to only return projected points when it finds an exact match for the player. Formulas that show #N/A then need to be investigated. For example, Alex Burks and Andris Biedrins both have a team mismatch. Austin Daye and Chris Wilcox are entirely missing from Sheet1, etc.

Colin,
I tried using your tips and applying it to my take home test for my class, where we have to find the Standard Bonus and Performance Bonus using the VLookup formula, but I am so confused. I really don’t know what to refer to since in the first table, the first column contains the agent, then the annual commission, then the year with the company. Then theres another table which is the bonus schedule that contains the years of service, performance award, and standard bonus. The Standard Bonus and Performance Bonus are both going into the first table and in the formula, the table_array portion of it has to be an absolute value because I have to autofill it down the column. I just don’t know what to refer to. If you could help out, that’d be awesome!
Thanks!
-Kaitlyn

The first table contains the years of service, so it sounds like you need to add a new column of VLOOKUP formulas to the first table which will look up the performance award and standard bonus by using the years of service for each agent.

I currently am working on a spreadsheet that compiles a number of different reports into one larger aggregate report. Put another way, I have 8 buckets , all containing the same 3 columns of data, keyed to a date, filling one big bucket (the overall/total report). Hope this makes sense.

Currently I have it setup so that each smaller bucket is assigned to its own worksheet, and the total value report in its own sheet gets its summarized aggregate totals by dragged down a column that uses chained vlookups referencing the dates in the table. So the total worksheet pulls total sales from Nov1 in sheet 1, Nov1 in sheet 2, Nov1 in sheet 3 etc using vlookup against the date…and this is dragged down to include all the dates in the tables.

Problem is, some of the campaigns in the smaller buckets ended earlier or later than others, so it causes the result to reflect as a #N/A because it can’t find the values as the date doesn’t exist.

Aside from entering all 0’s and dummy rows to keep the dates the same among the tables, is there any way to just have it ignore missing values or stop once it reaches the end/last date of the table without actually taking the particular vlookup function that is hitting the missing date and causing the error out of chain?

There are always multiple ways one can skin a cat, so it’s hard for me to suggest the best way for you to achieve your goal (formulas, VBA, pivot tables etc) without spending some time looking at your workbook. You mention the word ‘aggregate’ in your question which makes me wonder whether or not you should be using SUMIF() formulas instead of VLOOKUP() formulas.

I think the best you could do with VLOOKUP()s would be to wrap them with an error handling function such as:

IFNA() (only available in Excel 2013 or later)

IFERROR() (only available in Excel 2007 or later)

IF(ISNA()) (pre 2007)

That way you can have the formula return 0 instead of those unsightly #N/As.

Please help
I’m trying to do the vlookup from Excel A AND B and my purpose is to dig out How many invoices are still outstanding. The excel A is mess up data and B is all completed and paid invoices. So The lookup value is the invoice doc. no. Say in coulmn H from Excel A. Also, the coulm H is being sorted by invoice no. Originally, it is mess up, but in order to delete the duplicate invoices, I sorted it by invoice number.
The table array is in excel B( this excel shows all the completed invoices), from column A to I, column, colum A being the system generated invoice no. and column I being the invoice doc. no

Hi Colin,
I have two columns. I would like to compare two columns in excel and lookup value matches with the array should display “match” else “do not match”. How do you think this can be accomplished.

Below is the data set:
one seven #N/A
two eight #N/A
three one one
four four four
five #N/A
six #N/A

formula used: =VLOOKUP(B2,A$2:A$7,1,FALSE)

With thousands of records, just wanted to verify, which lookup value do not exists in array of values. It seems below formula seems to be not working. Please suggest right approach with vlookup formulae.
=IF(VLOOKUP(B4,A$2:A$7,1,FALSE)=”#N/A”,”Do Not Match”,”MATCH”)

Hi Colin,
My function uses the ‘TRUE’ range look up value, but when the Lookup value is exactly the same as a value, it still returns the next smallest value rather than the same value.

I tried changing the range lookup of the same equation to FALSE but this returned a #N/A error. I have tried using the suggested methods to troubleshoot my VLookup function but they unfortunately haven’t solved my problem. I now know the problem has something to do with how the lookup value is being calculated as if I put the number directly into the vlookup it returns the correct answer.

The lookup value:
=E20-(2*E13) where each cell represents a diameter value.

I also tried using the Match function but this resulted in the same issue, both when looking for an approximate or exact value.

I have tried for a while to solve this but have been unsuccessful, so any help would be much appreciated.

It sounds like a rounding or floating point issue. Both of these have been discussed in previous comments. As a test, try rounding your source data and lookup value to 2 decimal places (actually 2 decimal places and not formatting to only display 2 decimal places) and then try doing an exact match VLOOKUP.

If you’re still having problems then email me a simple example workbook and I’ll take a look for you.

I realise post was written years ago but I just wanted to say thank you, thank you, thank you! Just wasted 2 hours of my life with the #N/A only to find some of my numbers were saved as text despite the fact I thought I had already converted them. You have saved me many more hours of frustration.

Hi, what u posted is a very precise info on what to do about the error.. However I am still having problem with it. comparing 2 cells (eg. =a20=a45) gives out true value. but still i get N/a error. I am my file to ur email id, if it is possible for you to look at it and find a solution. Thanx a lot.

Basically, I am looking through the range $F$3:$G$23 to find the numeric value 750. When this value is found, the formula should return the value in column G, which happens to be a date.

The range $F$3:$G$23 includes twenty different numbers in column F (sorted in ascending order) and twenty different dates in column G also ascending). Again, no dupes.

The number “750” is not an exact match for any of the values in column F. However, there are values for 725 (this is row 10 with a corresponding date value of Jan 1, 2014) and 775 (this is a in row 11 with a corresponding date value of Feb 1, 2014).

The last value in the range is 1500 with a corresponding date value of Dec 1, 2014.

I would expect the VLOOKUP(750,$F$3:$G$23,2,TRUE) to return the date from the 10th or 11th row. However, it is returning the date from the 20th (last) row, as though the value were not found.

One follow-up. In my data range ($F$3:$G$23), not every row has a value in the first column. The final five rows do not have numbers in column F. They do, however, have dates in column G. Interestingly, the problem I identified goes away if I put values in the last five rows of column F. However, it seems to me to be irrelevant whether I do this, since the value 750 falls between two rows which do already have values.

It sounds like you’ve found the problem there. Those final five rows which are empty will cause the behaviour you describe. It’s important to understand that, when you’re doing an approximate match, VLOOKUP searches the column by jumping down and up it rather than just starting at the top and working down. I wrote about this in more detail here so have a read through and hopefully things will make more sense.

VLOOKUP on text is not working for me. I have several sheets (sheet1, sheet2, sheet3) with data by countries and one sheet (sheet4) with the three-letter country codes defined in ISO 3166-1. I am trying to VLOOKUP the three-letter country code in sheet1 and only get #N/A errors.
I have checked for trailing spaces and such things, data type… – checking with a formula returns TRUE, confirming that two values *are* the same. VLOOKUP however will return an #N/A error.

When you do a VLOOKUP(), the first column in the table must contain the value you’re trying to find. Your table columns are:ISO | COUNTRY

but you are trying to look up a country and return an ISO code – a ‘lookup to the left’. This means that your current table structure will not work for a straightforward VLOOKUP() formula. Here are 3 ways you can work around this limitation:

Option 1 – Change your table structure
If you swap around the ISO and Country columns then your VLOOKUP() formula should work (change the column index from 1 to 2).=VLOOKUP(B2,sheet4!$A$2:$B$250,2,0)

Hej Colin,
I was not actively aware of the restriction that lookups could only be done “to the right”. Swapping ISO and Country columns was the easiest solution for me, but I will keep option 2 and 3 in mind in case I am stuck with the order of columns for any reason. Thank you very much for your help!
PS: You don’t happen to know your way around data labels in a bubble chart as well? :-)

VERY helpful article, thank you! Particularly the systematic diagnosis ideas. The problem I resolved with some thought provocation from your post was I was getting a ‘False’ return to my vlookup formula. Turns out I had the formula correct but there were HIDDEN COLUMNS in my lookup range, one of which very coincidentally contained the actual text value FALSE. And of course that column was hidden and happened to be the column I THOUGHT I was referencing by counting the columns left to right in the data set. So, I was mighty confused to be getting a ‘False’ in the cell of the formula until I realized that ‘duh, hidden columns’ mean I’m getting a match but the return value isn’t in the column # I thought it was.’ Bottom line is your post helped to eliminate some possibilities and consider others, so again, thank you!

I’ve checked for type mismatch, and I’ve checked for the stealthy missing spaces, and I still get an #N/A. I’m running Excel on a Mac and I’ve tried running it in Excel in Windows on a VM, and it turns up the same error. Any suggestions? Please tweet to me at @kbierce if/when you post a comment. Thanks!

dude for some odd reason my VLOOKUP function only works when I place the accounting or dollar sing in front of the numbers in the column , if I try to create a vlookup function without the dollar sing it will give me N/A error message strange huh ?

I have a pivot table that I am using to pull information from. This information changes weekly. The table has two columns. I am trying to use a VLOOKUP function to accomplish this. I am trying to lookup a specific text from column A and pull the corresponding number from column B. I have managed to get this accomplished and it will update itself if this corresponding number increases. Where my problem comes in is if the specific text from column A is removed, it will now read the information that has taken its place. For example…
A B C D E F G H
1 Row Labels Count of TSPM ABQ ALB ATL1 ATL2 AUS
2 ABQ/lcano 3 3 2 14 16 26
3 ALB/lhayes 2
4 ATL1/cmshelby 14
5 ATL2/cmshelby 16
6 AUS/lhernan 26

I need cell E2 to match the info the table to the left. In other words, cell E2 must equal the info that corresponds to cell A3 which is “2”. I got this to work as long as the text in column A stayes within my table. Now here’s the problem… If for some reason the text “ALB/lhayes” in column A were to be removed, obviously the information from row 4 will not become row 3. However, now my formula that I have in cell E2 is now going to pick up the information from the row below which has moved up into the 3 row position. I would like to have happen is, if my specific text is remove, i would like for the results to be “0”. This is my current formula i am using…
=VLOOKUP(“ALB/lhayes”,A:B,2,B:B). If there is another function that i should use that would work better, please let me know.

Hello,
I have my VLOOKUP working to associate an account number with an account name. Per our accounting system, we sometimes need to use the ~ symbol which means to exclude that account(s). For example: ‘6900-6990,~6930-6940 is Benefits & Payroll Taxes (excl. Bonus). The VLOOKUP comes up as #N/A when that symbol is being used, but still pulls accurately for everything else. Any ideas on how to fix this issue? Thanks!

Thanks for the helpful article but I am still having the issues getting the right information out of “Vlookup” On one sheet the vlookup is working fine and giving the desired value but on the other sheets it is giving the error. I have checked the two values and givning “TRUE” result.

Great info, thank you for sharing. :)
However, I’m a n00b at excel, and I can’t seem to get my head around how to solve my problem with your examples. This is my scenario:
I have a list of names sorted alphabetically in a column. Each name has 12 cells next to it (one for each round of a tournament) which will be filled with a numerical value from 0 to 50. The total for each name is in a further column. I then use RANK to determine the order, and VLOOKUP to produce a list with the names sorted in descending order according to their score.
When two names receive the same rank (they have equal scores), it results in the #N/A error. I also want to use the same method to show all the scores in the sorted list, but they also result in the #N/A error.

Now I am having an issue. none of the troubleshoots worked here for me. Format, length, type etc are all the same for the two columns. I have datetime for two different datasets. It worked for another dataset exact same… just a different year of data and worked fine. Now I try and do the vlookup for another year of data and N/A is received back for everything and i cannot seem to find an issue. would there be any other possible issues?

Times in Excel are represented by floating point numbers, so you could be experiencing issues with binary rounding. Workarounds are discussed in previous comments on here, or you are welcome to email me a simple example and I will then confirm what’s happening.

Hi, my problem is this: I have a column of names and for some reason some of them are impossible for excel to find with vlookup,lookup,index etc. The only thing that works is match but only if i use the match type=0. The thing is that l need to use lookup in a more comlexed ecuation and this is causing me trouble. I have no idea why this is happening. All test come back as true, so the text is ok. I have 20 names now and 6 of them are causing me trouble.

Hi, if MATCH() works with Match type 0 (an exact match) then VLOOKUP() should also work the same if you use a range lookup value of 0 (that’s the 4th parameter). If it doesn’t then e-mail me an example and I’ll take a look for you.