Why Does VLOOKUP Return An #N/A Error?

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!

About these ads

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , . Bookmark the permalink.

107 Responses to Why Does VLOOKUP Return An #N/A Error?

  1. Pingback: Using WorksheetFunction.VLookUp To Look Up A Date In VBA | RAD Excel

  2. Pingback: Colin Legg looks at “Why Does VLOOKUP Return An #N/A Error?” « VLOOKUP WEEK

  3. Sid says:

    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!

    Like

    • Colin Legg says:

      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.

      Like

  4. John Madden says:

    Nicely done. Very clear, very succint.

    Like

  5. Rune Antonsen says:

    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?

    Like

  6. Colin Legg says:

    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/mike-excelisfun-girvin-index-and-match-how-to/

    If you have any further questions or want me to explain the formula then please let me know.

    Like

    • Rune Antonsen says:

      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.

      Like

  7. Shah says:

    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

    Like

  8. Venkat Jayaraman says:

    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?

    Like

  9. Arpit says:

    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

    Like

    • Colin Legg says:

      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.

      Like

  10. Andrew says:

    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?

    Like

  11. Colin Legg says:

    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

    Like

    • Andrew says:

      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

      Like

  12. Lance says:

    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

    Like

    • Colin Legg says:

      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.

      Like

      • Colin Legg says:

        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)

        Like

  13. Mary Fetsch says:

    Thanks so much!! This was VERY helpful!

    Like

  14. Gus says:

    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

    Like

    • Colin Legg says:

      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.

      Like

  15. Gus says:

    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.

    Like

    • Colin Legg says:

      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!

      Like

      • ebowman says:

        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…

        Like

  16. Deb Hawkes says:

    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.

    Like

    • Colin Legg says:

      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.

      Like

      • Colin Legg says:

        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.

        Like

      • Deb Hawkes says:

        Thank you so much, it now works perfectly, you are a life saver

        Like

  17. Andrea says:

    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!

    Like

    • Colin Legg says:

      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.

      Like

  18. Jay says:

    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?

    Like

    • Jay says:

      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.

      Like

  19. Jay says:

    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.

    Like

    • Jay says:

      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.

      Like

  20. Adnan Qiyas says:

    Thank you very very much. Very seldom you come across such a succint yet concise explanation.

    MS should hire you to do their F1s.

    Like

  21. Faisal says:

    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?

    Like

  22. Colin Legg says:

    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?

    Like

  23. Niki says:

    Hi,

    Can anyone assist with this:
    =VLOOKUP(B5,Allexpenses,MATCH($P$4,Allexpenses,0),FALSE)

    All the data is #N/A

    Like

  24. Colin Legg says:

    Hi Niki,

    Please would you tell me what range the named range Allexpenses refers to?

    Like

  25. Niki says:

    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

    Like

  26. Niki says:

    sorry those numbers go under the letters

    Like

  27. Colin Legg says:

    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)

    Like

    • Niki says:

      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

      Like

      • Colin Legg says:

        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.

        Like

  28. Ivan says:

    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!

    Like

  29. Jurjen says:

    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

    Like

    • Jurjen says:

      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?

      Like

      • Jurjen says:

        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.

        Like

  30. Brian says:

    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?

    Like

    • Colin Legg says:

      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 be False).
      (3) Do any of the dates have times on the end of them? Cell formatting might hide this, but you’ll be able to see the times in the formula bar.

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

      Like

  31. Colin Legg says:

    Tom asks:

    Colin,

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

    Hi Tom,

    Your VLOOKUP() formula in B17 is this:

    =VLOOKUP(B16,J1:K4,2)

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

    =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 or 0 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

    Like

  32. Sonya Parks says:

    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.

    Like

    • Colin Legg says:

      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.

      Like

  33. Pat says:

    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.

    Like

  34. Raúl says:

    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

    Like

  35. Anoop says:

    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

    Like

    • Colin Legg says:

      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

      Like

  36. CiCi Smith says:

    Awesome…your advice regarding LEN and TYPE helped me so much with issue I was experiencing!

    Like

  37. Pingback: Combine Data in two Sheets Using VLookup "#N/A" Error

  38. Avni Pandya says:

    Thanks so much! I was totally stuck with my vlookup and your article had the answer (sort order was my issue). ~Avni

    Like

  39. Kris says:

    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

    Like

    • Kris says:

      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.

      Like

    • Kris says:

      Organizing the other vlookup’s validated list alphabetically solved the problem of mismatched row returns.

      Like

      • Colin Legg says:

        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.

        Like

  40. Kris says:

    Thanks, I assume that’s why the other bad returns came back as well.

    Like

    • Colin Legg says:

      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.

      Like

  41. Tony says:

    Hi Colin,

    Thanks so much. It solved my problems.

    Tony Yap

    Like

  42. Gavin says:

    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?

    Like

    • Colin Legg says:

      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

      Like

      • Gavin Heys says:

        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

        Like

      • Colin Legg says:

        Hi Gavin,

        You can’t attach workbooks on here. Please would you email it (email address at the end) to me?

        Like

      • Colin Legg says:

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

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

        Hope that helps,

        Colin

        Like

  43. Colin Legg says:

    Jason asks:

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

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

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

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

    Respectfully yours,

    Jason

    Like

    • Colin Legg says:

      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 references Sheet1!E25:F274, so it can’t find him (E24 is not in the lookup range).

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

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

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

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

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

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

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

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

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

      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/binary-searches-with-vlookup/

      Hope that helps,

      Colin

      Like

  44. Kaitlyn says:

    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

    Like

  45. Colin Legg says:

    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.

    Like

  46. Larry says:

    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!

    Like

    • Colin Legg says:

      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 of VLOOKUP() formulas.

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

      • IFNA() (only available in Excel 2013 or later)
      • IFERROR() (only available in Excel 2007 or later)
      • IF(ISNA()) (pre 2007)

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

      Like

  47. jamie zhao says:

    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!!

    Like

  48. Shaik says:

    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

    Like

  49. Shaik says:

    I could able to solve the issue by using: IF(ISNA(VLOOKUP(

    Like

  50. Elliot Gold says:

    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

    Like

    • Colin Legg says:

      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.

      Like

  51. Karen says:

    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.

    Like

  52. taral says:

    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.

    Like

    • taral says:

      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..

      Like

  53. Shawn says:

    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.

    Like

    • Shawn says:

      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.

      Like

      • Colin Legg says:

        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

        Like

  54. Shawn says:

    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

    Like

  55. Mariano says:

    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.

    Like

    • Colin Legg says:

      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 the VLOOKUP()
      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

      Like

  56. Mariano says:

    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? :-)

    Like

  57. Scott R says:

    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!

    Like

  58. chacko says:

    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

    Like

  59. 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!

    Like

  60. john patros says:

    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 ?

    Like

    • Colin Legg says:

      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

      Like

  61. Deanna Foster says:

    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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s