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

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

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

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

Nicely done. Very clear, very succint.

Like

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

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

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

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

• Colin Legg says:

Hi Venkat,

Specifically which cell in Sheet2!A:B should match A2?

Like

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

• Nuala McCann says:

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.

Like

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

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

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

11. Mary Fetsch says:

Thanks so much!! This was VERY helpful!

Like

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

• Gus says:

Thank you Colin, but I found a solution for it finally and would love to share it with you guys.

Like

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

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

15. 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. :(

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

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

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

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

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

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

21. Niki says:

Hi,

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

All the data is #N/A

Like

22. Colin Legg says:

Hi Niki,

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

Like

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

24. Niki says:

sorry those numbers go under the letters

Like

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

Like

• Niki says:

Hi Colin,

Sorry for taking a while to get back to you. I had some issues with the formula.

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.

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

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

Like

27. 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 :-)

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

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

• Brian says:

I did not lock in the array. It works now. Thank you for the help!

Like

29. Colin Legg says:

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

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

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

• Colin Legg says:

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?”

Like

• Pat says:

Hi Colin!
Worked beautifully with the rounding formula. Thanks so much!

Like

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

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

34. CiCi Smith says:

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

Like

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

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

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

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

38. Tony says:

Hi Colin,

Thanks so much. It solved my problems.

Tony Yap

Like

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

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

40. Colin Legg says:

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:
https://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/

Hope that helps,

Colin

Like

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

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

43. 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/A`s.

Like

44. jamie zhao says:

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

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

46. Shaik says:

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

Like

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

• Elliot Gold says:

Thanks for the reply. Managed to fix it by using =ROUND on the lookup value.

Cheers
Elliot

Like

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

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

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

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

52. 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
`=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

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

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

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

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

• Colin Legg says:

Hi, it’s hard to say. Perhaps you could email me an example workbook?

Like

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

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

-Deanna

Like

59. Will Chard says:

Awesome blog! Thanks, I was looking to return data to the left which was my problem.

Like

60. Em says:

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!

Like

• Colin Legg says:

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 literal

So, 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 because `SUBSTITUTE()` returns a text type.

Like

• Em says:

Thank you so much! Your suggested change worked perfectly.

Like

61. Faisal says:

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

Like

• Colin Legg says:

Hi Faisal, my email address is colinleggblog at gmail dot com

Like

62. Bob says:

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)

Like

63. Eddie says:

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!

Like

• Colin Legg says:

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.

Like

• Eddie says:

Colin I will send you the example to your email.

Like

64. Eddie says:

Colin, never mind I figure out the issue. Thanks !

Like

65. hildah says:

What if =A24=H54 is true but still showing the #NA error?

Like

• Colin Legg says:

Email me your sheet and I’ll take a look for you?

Like

66. Ashley says:

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

Like

• Colin Legg says:

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.

Like

• Ashley says:

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!

Like

67. Stelios says:

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.

Like

• Colin Legg says:

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.

Like

• Stelios says:

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.

Like