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!
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
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
Nicely done. Very clear, very succint.
LikeLike
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
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.
If you have any further questions or want me to explain the formula then please let me know.
LikeLike
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
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
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
Hi Venkat,
Specifically which cell in Sheet2!A:B should match A2?
LikeLike
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
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 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.
Let us know how you get on.
LikeLike
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.
LikeLike
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
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
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
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
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
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 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)
LikeLike
Thanks so much!! This was VERY helpful!
LikeLike
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
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 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.
LikeLike
Thank you Colin, but I found a solution for it finally and would love to share it with you guys.
LikeLike
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
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
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
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
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
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
Thank you so much, it now works perfectly, you are a life saver
LikeLike
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
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
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 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.
Any thoughts?
LikeLike
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
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
Aaand to continue the Dunce-age:
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
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
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
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
Hi,
Can anyone assist with this:
=VLOOKUP(B5,Allexpenses,MATCH($P$4,Allexpenses,0),FALSE)
All the data is #N/A
LikeLike
Hi Niki,
Please would you tell me what range the named range Allexpenses refers to?
LikeLike
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
sorry those numbers go under the letters
LikeLike
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
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
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
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
Hi Ivan,
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.For more information on floating-point rounding have a look through these articles:
Floating-point arithmetic may give inaccurate results in Excel
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”
LikeLike
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 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 :-)
Thanks in advance,
Jurjen
LikeLike
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
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
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
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
I did not lock in the array. It works now. Thank you for the help!
LikeLike
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
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
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
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
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 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.
Here are a couple of links I posted on another comment:
Floating-point arithmetic may give inaccurate results in Excel
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”
LikeLike
Hi Colin!
Worked beautifully with the rounding formula. Thanks so much!
LikeLike
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
LikeLike
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
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
Awesome…your advice regarding LEN and TYPE helped me so much with issue I was experiencing!
LikeLike
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
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
All of the text was typed into the worksheet.
Thank you in advance.
Best,
Kris
LikeLike
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
Organizing the other vlookup’s validated list alphabetically solved the problem of mismatched row returns.
LikeLike
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
Thanks, I assume that’s why the other bad returns came back as well.
LikeLike
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
Hi Colin,
Thanks so much. It solved my problems.
Tony Yap
LikeLike
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
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
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
Hi Gavin,
You can’t attach workbooks on here. Please would you email it (email address at the end) to me?
LikeLike
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
Jason asks:
LikeLike
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:Hope that helps,
Colin
LikeLike
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
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
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
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
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
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
I could able to solve the issue by using: IF(ISNA(VLOOKUP(
LikeLike
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
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
Thanks for the reply. Managed to fix it by using =ROUND on the lookup value.
Cheers
Elliot
LikeLike
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
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
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
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
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.
LikeLike
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
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
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.
See https://www.dropbox.com/s/o8fg387jf8a3wp8/sample.ods for a sample.
LikeLike
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
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
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
A golden rule with VLOOKUP() is that the lookup column must be the first column in table_array . Thank you Collin for this post .. This was the reason i was getting # NA error
LikeLike
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!
LikeLike
Hi, it’s hard to say. Perhaps you could email me an example workbook?
LikeLike
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 ?
LikeLike
Hi John,
If you select a cell in the lookup column and check the formula bar, does it have a dollar sign at the front? If yes then your lookup value will also need a dollar sign at the front.
If you think something more sinister is going on then you’re welcome to email me an example?
Colin
LikeLike
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.
Thank you for any help you can give me.
-Deanna
LikeLike
Pingback: VLOOKUP works in some cells but not others. | Chris Memo
Awesome blog! Thanks, I was looking to return data to the left which was my problem.
LikeLike
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!
LikeLike
Great question – this scenario isn’t covered in my blog post.
A little known fact is that
VLOOKUP()
can use three wildcards in the lookup value:* ? ~
*
represents any number of characters?
represents a single character~
is used to escape a wildcard and treat it as a literalSo, in this case, you need to escape the
~
in your lookup value (but not in the lookup table). To do this you need to double them up. For example:~6930-6940
would become
~~6930-6940
I’m sure you want a generic formula which will work whether the account has a tilde or not. You can achieve this quite nicely by using the
SUBSITITUTE()
function.If your current
VLOOKUP()
formula is like this:=VLOOKUP(A1,$C$1:$D$2,2,0)
change it to:
=VLOOKUP(SUBSTITUTE( A1 ,"~","~~") ,$C$1:$D$2,2,0)
One caveat:
VLOOKUP()
is data type sensitive (see the blog post above) so this formula assumes all the lookup values are text types becauseSUBSTITUTE()
returns a text type.LikeLike
Thank you so much! Your suggested change worked perfectly.
LikeLike
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.
I can email you the workbook if i can get hold of your email ID
LikeLike
Hi Faisal, my email address is colinleggblog at gmail dot com
LikeLike
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.
example of my RANK formula:
=RANK(P1;P1:P25;0)
example of my VLOOKUP formula:
=VLOOKUP($R1;$A$1:$P$25;2;0)
Regards,
Rob 8)
LikeLike
Great Blog,
My Question is the following:
My vlookup is returning a #N/a, i try the true and false trouble shooting an it return false , but when I check my len formula they match the same quantity of characters.
My vlookup formula is =VLOOKUP(AC106,$AL$87:$AN$145,3,FALSE)
In column AC106 I have the following formula enter: =CONCATENATE(RIGHT(AC105,3)).
I want my vlookup formula to look for the value that appears in cel AC106 from my array “AL87:AN87”.
Thanks!
LikeLike
Perhaps they’re different data types. Your formula in AC106 is returning a text type (a string). If the lookup column contains number types then VLOOKUP with return #N/A.
If you send me an example workbook I would be happy to check it for you.
LikeLike
Colin I will send you the example to your email.
LikeLike
Colin, never mind I figure out the issue. Thanks !
LikeLike
What if =A24=H54 is true but still showing the #NA error?
LikeLike
Email me your sheet and I’ll take a look for you?
LikeLike
Thank you for the clear and concise explanation.
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?
Thank you, Ashley
LikeLike
Hi Ashley,
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.
LikeLike
youll have to excuse me, i cant find your email on here to email you the data. i appreciate your help a lot because this has been bugging me for ages now!
LikeLike
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.
LikeLike
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.
LikeLike
You are right, it does work. But VLOOKUP() doesn’t really help me. You see what l mostly need from LOOKUP() is the “lookup_ventor”. So l guess l have to find another way. Thank you anyway.
LikeLike