VLOOKUP week enters its second day, meaning that it’s time to have a look at how to problemshoot #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 hardtospot 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!
Pingback: Using WorksheetFunction.VLookUp To Look Up A Date In VBA  RAD Excel
Pingback: Colin Legg looks at “Why Does VLOOKUP Return An #N/A Error?” « VLOOKUP WEEK
Thanks for the post Colin.
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?
Your help would be much appreciated!
LikeLike this
Hi Sid,
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.
LikeLike this
Nicely done. Very clear, very succint.
LikeLike this
Hi guys. I have a table in Excel what I can’t get a proper reference to. Maybe you can help me.
A simplified version of the table (Array1) is:
(Blank) – MAD – MAPE – MSE
ES – 13 – 9 – 18
MA – 11 – 7 – 16
MWA – 12 – 8 – 17
NA – 10 – 6 – 15
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:
=VLOOKUP(B2,Array1,1,FALSE)
What am I doing wrong and are you able to return the name of the row?
LikeLike this
Hi Rune,
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))
As part of VLOOKUP week, Mike Girvin did a video explaining how to use INDEX() and MATCH(). You can find it on the link below and I’m sure you’ll find it to be an hour of your time well spent.
http://vlookupweek.wordpress.com/2012/03/28/mikeexcelisfungirvinindexandmatchhowto/
If you have any further questions or want me to explain the formula then please let me know.
LikeLike this
Thank you for the fast reply, it was very helpful even though i found my own workaround. I will try out the INDEX and MATCH functions, and definitely watch the video once I got an hour free.
Thanks again.
LikeLike this
Very useful to me as I often get this error. Also, one item not listed that I use is the “clean” function for the table used in the formula
LikeLike this
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?
LikeLike this
Hi Venkat,
Specifically which cell in Sheet2!A:B should match A2?
LikeLike this
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
LikeLike this
Hi Arpit,
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 leftmost 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.
Let us know how you get on.
LikeLike this
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.
Any ideas what’s wrong?
LikeLike this
Hi Andrew,
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().
=VLOOKUP(B2,CHOOSE({1,2},E2:E6,D2:D6),2,0)
Hope that helps,
Colin
LikeLike this
Hi Colin
That’s great info and thanks for sharing. I get kind of frustrated with excel functions a lot and end up just using VBA!
Andrew
LikeLike this
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?
R//Lance
LikeLike this
Hi Lance,
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.
LikeLike this
Hi Lance,
Thanks for sending me your workbook. Your VLOOKUP() formula (simplified from a larger formula) looks something like this:
=VLOOKUP(Main!B2,'Symbol List'!A1:C30,1)
I’ve simplified it both to make problemshooting 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)
LikeLike this
Thanks so much!! This was VERY helpful!
LikeLike this
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.
Best regards,
Gus
LikeLike this
Hi Gus,
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 email 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.
LikeLike this
Thank you Colin, but I found a solution for it finally and would love to share it with you guys.
LikeLike this
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 all.
LikeLike this
Hi Gus,
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!
LikeLike this
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…
LikeLike this
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.
LikeLike this
Hi Deb,
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.
LikeLike this
Hi Debra,
Thanks for sending your worksheet: I see the problem now. Believe it or not, this is a data type issue.
Let’s take the formula in row 36 as an example:
=IFERROR(IF(LEN($C36)<5,VLOOKUP("0"&LEFT($C36,5),'4. Static Data'!$A$2:$F$99,MATCH('1. four_day_outlook'!H$2,'4. Static Data'!$A$2:$F$2,0),FALSE),VLOOKUP(LEFT('1. four_day_outlook'!$C36,5),'4. Static Data'!$A$2:$F$99,MATCH('1. four_day_outlook'!H$2,'4. Static Data'!$A$2:$F$2,0),FALSE)),"")
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.
LikeLike this
Thank you so much, it now works perfectly, you are a life saver
LikeLike this
Hi Colin,
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. :(
Thanks in advance for the help/advice!
LikeLike this
Hi Andrea,
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.
LikeLike this
Hey Colin,
I’m having a VLookup issue in Excel 2007.
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 reentering. 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.
Any thoughts?
LikeLike this
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.
LikeLike this
Wow. I’m such a dunce. I use =TEXT(CellReference,”ddd”) to return the day of the week from the date on March 2013 so it returns in the text format instead of a number code.
LikeLike this
Aaand to continue the Dunceage:
I didn’t have my Prices table alphabetized quite right. I had Monday first, probably because it’s first in the week. As soon as I corrected that, my error cleared up.
I have got to be one of the dumbest smart people I know.
LikeLike this
Thank you very very much. Very seldom you come across such a succint yet concise explanation.
MS should hire you to do their F1s.
LikeLike this
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?
LikeLike this
Hi Faisal, please would you post your VLOOKUP formula, the “=” comparison check formula which you used, and tell me the exact value you’re looking up?
LikeLike this
Hi,
Can anyone assist with this:
=VLOOKUP(B5,Allexpenses,MATCH($P$4,Allexpenses,0),FALSE)
All the data is #N/A
LikeLike this
Hi Niki,
Please would you tell me what range the named range Allexpenses refers to?
LikeLike this
Hi Colin,
The data is like this:
Employee name Recategorisaion code E M N
Mocha Vet bills 1,000.00
Fifi Hay 100.00
Mamba Bunny meals 300
Milli Vet bills 998
Patch Grass 100
Niki
LikeLike this
sorry those numbers go under the letters
LikeLike this
Hi Niki,
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.
Then amend your formula to:
=VLOOKUP(B5,Allexpenses,MATCH($P$4,AllexpensesHeaders,0),FALSE)
LikeLike this
Hi Colin,
Sorry for taking a while to get back to you. I had some issues with the formula.
I made your recommended adjustments and noticed two issues:
1) Some employee names are not pulling through even though they are listed on Allexpenses.
2) On the lookup sheet some names appear like Pumpkin that is not on the Allexpenses range N/A appears – do you know how to avoid this.
Hope you can help.
Thank you in advance
Nik
LikeLike this
Hi Nik,
Please email me a sample workbook (without sensitive data) which demonstrates the issue and I will take a look at it for you. My email address is colinleggblog at gmail dot com.
LikeLike this
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.
Please help!
LikeLike this
Hi Ivan,
This sounds like a floatingpoint 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.For more information on floatingpoint rounding have a look through these articles:
Floatingpoint arithmetic may give inaccurate results in Excel
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”
LikeLike this
Hi Colin,
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:
Hero Games Wins Losses WR Player KDA
Alchemist 1 1 0 100,00% Huite 1,29
Alchemist 4 1 3 25,00% Jurjen 2,18
Alchemist 5 4 1 80,00% Marc 2,4
Alchemist 2 1 1 50,00% Peter 2,75
Alchemist 3 1 2 33,33% Roland 2,9
Alchemist 3 0 3 0,00% Ermo 3,33
Ancient Apparition 1 0 1 0,00% Peter 0,78
Ancient Apparition 2 0 2 0,00% Jurjen 0,95
Ancient Apparition 4 1 3 25,00% Huite 1,77
Ancient Apparition 3 2 1 66,67% Roland 2
Ancient Apparition 6 3 3 50,00% Marc 2,2
Ancient Apparition 12 5 7 41,67% Ermo 3,47
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:
Teamfight win% KDA wie % wie KDA
Dark Seer 100,00% 17,00 Peter Peter
Warlock 81,82% 3,96 Ermo Ermo
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 25. 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 :)
Thanks in advance,
Jurjen
LikeLike this
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?
LikeLike this
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.
LikeLike this
Hi,
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?
LikeLike this
Hi Brian,
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 beFalse
).(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.
LikeLike this
I did not lock in the array. It works now. Thank you for the help!
LikeLike this
Tom asks:
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 areB16
,J1:K4
and2
.VLOOKUP()
actually takes 4 arguments and the important thing to know is thatVLOOKUP()
will do an approximate match if the fourth argument is omitted or if the fourth argument isTRUE
or1
.So these three formulas would all do the same thing:
=VLOOKUP(B16,J1:K4,2)
=VLOOKUP(B16,J1:K4,2, TRUE)
=VLOOKUP(B16,J1:K4,2, 1)
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
or0
in as a fourth argument, so either of these formulas should work for you:=VLOOKUP(B16,J1:K4,2, FALSE)
=VLOOKUP(B16,J1:K4,2, 0)
Hope that helps,
Colin
LikeLike this
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.
LikeLike this
Hi Sonya,
I can’t see anything wrong with your formula, so it looks like there must be an underlying issue with the data. If you email me a small example file I’ll take a look for you.
LikeLike this
Hi Colin,
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.
LikeLike this
Hi Pat,
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 floatingpoint 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.
Here are a couple of links I posted on another comment:
Floatingpoint arithmetic may give inaccurate results in Excel
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”
LikeLike this
Hi Colin!
Worked beautifully with the rounding formula. Thanks so much!
LikeLike this
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 copypasting 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
LikeLike this
Hello,
I am facing a problem when calling for a value corresponding to TIME. The first cell which i call gives me a correct value but when i drag it, the output received is #N/A. Please help
LikeLike this
Hello Anoop,
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.
Hope that helps,
Colin
LikeLike this
Awesome…your advice regarding LEN and TYPE helped me so much with issue I was experiencing!
LikeLike this
Pingback: Combine Data in two Sheets Using VLookup "#N/A" Error
Thanks so much! I was totally stuck with my vlookup and your article had the answer (sort order was my issue). ~Avni
LikeLike 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 12 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
All of the text was typed into the worksheet.
Thank you in advance.
Best,
Kris
LikeLike this
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.
LikeLike this
Organizing the other vlookup’s validated list alphabetically solved the problem of mismatched row returns.
LikeLike this
Hi Kris,
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.
LikeLike this
Thanks, I assume that’s why the other bad returns came back as well.
LikeLike this
Hi Kris,
Yes, that sounds right. If you have a look towards the end of the blog post there’s a short section on approximate lookups which describes the behaviour you’re seeing.
LikeLike this
Hi Colin,
Thanks so much. It solved my problems.
Tony Yap
LikeLike this
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?
LikeLike this
Hi Gavin,
I’m not sure why your formula is returning #N/A. Please would you email me an example workbook and I’ll take a look at it for you?
Regards,
Colin
LikeLike this
Hi Colin,
Thanks for the offer of help.
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.
Any help gratefully received.
Best Wishes,
Gavin Heys
LikeLike this
Hi Gavin,
You can’t attach workbooks on here. Please would you email it (email address at the end) to me?
LikeLike this
Hi Gavin,
Thanks for emailing me your workbook. The
VLOOKUP()
formula in SM119!N6 is=VLOOKUP($K6,DATA,4,TRUE)
The
TRUE
part of the formula tellsVLOOKUP()
to do an approximate match. WhenVLOOKUP()
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 calledDATA
) 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 tellVLOOKUP()
to do an exact match. To do this, just change theTRUE
toFALSE
, like this:=VLOOKUP($K6,DATA,4,FALSE)
Hope that helps,
Colin
LikeLike this
Jason asks:
LikeLike this
Hi Jason,
Your formula for Blake Griffin is
=VLOOKUP(E25,Sheet1!E25:F274,2)
If you look in sheet1, you’ll see that Blake Griffin is in cell
E24
. Your formula referencesSheet1!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 theE2: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 thatSheet1
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 columnH
. If you look at Alex Burkes, you’ll see that his projected points returned by theVLOOKUP()
formula are3.1
. But, if you look atSheet1
you’ll see his projected points should be17.925
. The reason for the mismatch is on yourNBA
sheet, Alec Burks’ team is Uta but onSheet1
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 fromSheet1
, etc.If you’re still not sure about what approximate are in
VLOOKUP()
formulas, you might want to have a read of my blog post on them:http://colinlegg.wordpress.com/2012/03/25/binarysearcheswithvlookup/
Hope that helps,
Colin
LikeLike this
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
LikeLike this
Hi 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.
LikeLike this
Hi:
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?
Thanks for the help!
LikeLike this
Hi Larry,
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 ofVLOOKUP()
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/A
s.LikeLike this
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
Myvlookup formula is below;
=VLOOKUP(H2,’[Completed PR Raw data 2013.XLS]Sheet1′!$A:$I,1,0)
It’s all return with #N/A, but I saw there are matched invoice doc. No. from EXCEL A and B
can you help me ?
thanks heaps!!
LikeLike this
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”)
Regards,
Shaik
LikeLike this
I could able to solve the issue by using: IF(ISNA(VLOOKUP(
LikeLike this
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.
Regards,
Elliot
LikeLike this
Hi Elliot,
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.
LikeLike this
Thanks for the reply. Managed to fix it by using =ROUND on the lookup value.
Cheers
Elliot
LikeLike this
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.
LikeLike this
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.
LikeLike this
found out i had problem in another entry which somehow caused error in other data.. sorted it out and now all is ok.. thanx a lot for dis awesom post..
LikeLike this
I have the following formula: VLOOKUP(750,$F$3:$G$23,2,TRUE).
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.
Why?
Thanks.
LikeLike this
One followup. 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.
LikeLike this
Hi Shawn,
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.
Regards,
Colin
LikeLike this
I have the following data on one worksheet:
TABLE #1
START DATE  END DATE  RELEASE NAME
01/01  01/07  missing formula here
01/08  01/14  missing formula here
01/15  01/21  missing formula here
01/22  01/29  missing formula here
I have the following data on another worksheet:
TABLE #2
LAUNCH DATE  RELEASE NAME
01/04  Apple
01/11  Banana
01/17  Orange
01/25  Peach
I want to search column A in TABLE #2 (Launch Date) and find our where within TABLE #1 the date appears. Then I want to return the “Release Name” as the formula result in TABLE #1.
For example: The date 01/11 in TABLE #2 falls between 01/08 and 01/14 in TABLE #1. So, I would like the formula in column C row 2 in TABLE #1 to result in the value “Banana.”
Thanks,
Shawn
LikeLike this
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 threeletter country codes defined in ISO 31661. I am trying to VLOOKUP the threeletter 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.
See https://www.dropbox.com/s/o8fg387jf8a3wp8/sample.ods for a sample.
LikeLike this
Hi Mariano,
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)
Option 2 – Use An Index / Match Formula
Instead of using:
=VLOOKUP(B2,sheet4!$A$2:$B$250,1,0)
try this:
=INDEX(sheet4!$A$2:$A$250,MATCH(B2,sheet4!$B$2:$B$250,0))
Option 3 – Use
CHOOSE()
embedded in theVLOOKUP()
Like so:
=VLOOKUP(B2,CHOOSE({1,2},sheet4!$B$2:$B$250,sheet4!$A$2:$A$250),2,0)
Personally, in this case, I think the best one for you is Option 2.
Regards,
Colin
LikeLike this
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? :)
LikeLike this
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!
LikeLike this