Vlookup Troubleshooting...

[reducedjuice]

Hi All,

I'm using a vlookup function in Excel 07 as follows:

WS1 Columns:
A: customer code X (text values)
B: my vlookup formula

WS2 Columns:
A: customer code Y (number value)
B: customer code X (text value)

I am using the following VLOOKUP formula in WS1 cell B2:

=VLOOKUP(A2,WS2!A:B,1,0)


I'm having all 170,000 rows returning values of #N/A even though there are plenty that should not be #N/A.

Do the text/number values have some bearing on this perhaps?

cheers,
-reducedjuice

 

[Greg Truby]

This doesn't paste 100%, but close. It's from an article I wrote recently on common VLookup problems.

* * *

Problem #2: You know your data matches, but VLookup() has gone stupid on you and is returning #N/A. This happens a lot when looking up Sony part numbers.
Solution #2: The problem is almost always that the keys are a mix of numbers values and text values within the cells and one of the key columns is formatted as GENERAL and the other is formatted as TEXT. Here the left table’s key is formatted as General and the right’s key is formatted as Text.

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.8pt; WIDTH: 271pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=361 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c2d69a 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #9bbb59; PADDING-BOTTOM: 0in; BORDER-LEFT: #c2d69a 1pt solid; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-top-alt: solid #C2D69A .5pt; mso-border-left-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=64>

Key<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

​

</TD><TD style="BORDER-RIGHT: #c2d69a 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #c2d69a 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #9bbb59; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-top-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt; mso-border-right-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=71>Flowers<o></o>

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: white 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #9bbb59; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: white 1.5pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid white 1.5pt; mso-border-right-alt: solid white .5pt" vAlign=bottom noWrap width=64>

Key 2<o></o>​

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #9bbb59; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 74pt; PADDING-TOP: 0in; BORDER-BOTTOM: white 1.5pt solid; HEIGHT: 15pt" vAlign=bottom noWrap width=99>Vlookup()<o></o>

​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #c2d69a 1pt solid; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-left-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=64>

R<o></o>​

​

</TD><TD style="BORDER-RIGHT: #c2d69a 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid #C2D69A .5pt; mso-border-right-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=71>Roses<o></o>

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: white 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #d7e4bc; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: white 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid white .5pt; mso-border-right-alt: solid white .5pt" vAlign=bottom noWrap width=64>

9<o></o>​

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #d7e4bc; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 74pt; PADDING-TOP: 0in; BORDER-BOTTOM: white 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid white .5pt" vAlign=bottom noWrap width=99>#N/A<o></o>

​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c2d69a 1pt solid; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=64>

4<o></o>​

​

</TD><TD style="BORDER-RIGHT: #c2d69a 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid #C2D69A .5pt; mso-border-right-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=71>Carnations<o></o>

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: white 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: white 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid white .5pt; mso-border-right-alt: solid white .5pt" vAlign=bottom noWrap width=64>

4<o></o>​

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 74pt; PADDING-TOP: 0in; BORDER-BOTTOM: white 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid white .5pt" vAlign=bottom noWrap width=99>#N/A<o></o>

​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #c2d69a 1pt solid; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-left-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=64>

L<o></o>​

​

</TD><TD style="BORDER-RIGHT: #c2d69a 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid #C2D69A .5pt; mso-border-right-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=71>Lillies<o></o>

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: white 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #d7e4bc; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; mso-border-right-alt: solid white .5pt" vAlign=bottom noWrap width=64>

R<o></o>​

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #d7e4bc; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 74pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt" vAlign=bottom noWrap width=99>Roses<o></o>

​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #c2d69a 1pt solid; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=64>

9<o></o>​

​

</TD><TD style="BORDER-RIGHT: #c2d69a 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid #C2D69A .5pt; mso-border-right-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=71>Zinnias<o></o>

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 74pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=99></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #c2d69a 1pt solid; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-left-alt: solid #C2D69A .5pt; mso-border-bottom-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=64>

T<o></o>​

​

</TD><TD style="BORDER-RIGHT: #c2d69a 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 53pt; PADDING-TOP: 0in; BORDER-BOTTOM: #c2d69a 1pt solid; HEIGHT: 15pt; mso-border-bottom-alt: solid #C2D69A .5pt; mso-border-right-alt: solid #C2D69A .5pt" vAlign=bottom noWrap width=71>Tulips<o></o>

​

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64></TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 74pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=99></TD></TR></TBODY></TABLE>​

<o></o>
Ah, so you think, “I’ll just format the General column as Text” (or vice-versa). So you highlight one of the columns and hit Ctrl+F1 (or Format | Cells… from the menu) and change the format and …. What!? It doesn’t fix the problem. For whatever reason, changing a cell’s format doesn’t “take” until you edit the cell. You can plink back and forth hitting F2 then ENTER a couple hundred times or you can use Excel’s Text-to-Columns tool.
Highlight the column whose format you wish to change and then from the menu (using Excel 2003 here) select Data | Text to Columns…

You can just leave the DELIMITED option in place and click Next > and then make sure that whatever delimiter you have checked does not actually occur in your column. I normally just stick with TAB which very rarely occurs in a cell. Then click Next > again and on Step 3 you pick the format you need General or Text and click Finish and, if you correctly remembered what format you needed you should be rewarded with the VLookup() functioning properly.
Alternate Solution – if you’re feeling adventurous, you can do the type conversion in-formula by coercing the data types. If the cells that go into the first VLookup argument are the ones that are text and the keys that are in the range that are in the second argument are General then something like:
· =VLOOKUP(IF(ISERR(Key2+0),Key2,Key2+0),Sheet1!Data,2,FALSE)
will coerce the first arg to general whenever the data is numeric in nature. If it’s the data that’s in the key field of the 2<SUP>nd</SUP> argument’s range that is Text format and your first argument’s cell is General format then concatenating an empty string:
· =VLOOKUP(Key2&””,Sheet1!Data,2,FALSE)
will coerce the numbers in the first arg to all text.

 

[gecs]

Isn't the code X you're trying to match with the VLOOKUP and return code y? If this is the case in WS2 switch the column A with column B and change the formula to =VLOOKUP(A2,WS2!A:B,2,0)

 

[lenze]

Shouldn't your columns on WS2 be reversed and your formula?

=VLOOKUP(A2,WS2!A:B,2,0)

If thats not possible, then use INDEX(MATCH)

=INDEX(WS2!A:A,MATCH(A2,WS2!B:B,0),1)

lenze

 

[VoG]

ADVERTISEMENT

I may have misread this, if so I apologise. Are you trying to lookup to the left? If so it can't be done with VLOOKUP. Instead try

=INDEX('WS2'!A:A,MATCH(A1,'WS2'!B:B,0))

 

[Greg Truby]

Isn't the code X you're trying to match with the VLOOKUP and return code y? If this is the case in WS2 switch the column A with column B and change the formula to =VLOOKUP(A2,WS2!A:B,2,0)

Shouldn't your columns on WS2 be reversed and your formula?
lenze

I may have misread this, if so I apologise. Are you trying to lookup to the left? If so it can't be done with VLOOKUP.

Will be interesting to see... because I saw that too, but I interpreted the fact that he's fetching the first column (i.e. the 3^rd arg in VLookup = 1) to mean that he's simply trying to match and make sure the item appears on the second list.

 

[Aladin Akyurek]

ADVERTISEMENT

Just curious: Is WS2!A:B sorted on column B in ascending order?

 

[gecs]

Will be interesting to see... because I saw that too, but I interpreted the fact that he's fetching the first column (i.e. the 3^rd arg in VLookup = 1) to mean that he's simply trying to match and make sure the item appears on the second list.

You might be right, but isn't the use of VLOOKUP a mistake in this case? I mean, if he's not sure VLOOKUP is the solution why isn't he asking for another approach? On the other hand I don't think he's trying to match strings with numbers using VLOOKUP because, as you have mentioned, he's trying to retrieve matches on column 1 (numbers)...

 

[Greg Truby]

...but isn't the use of VLOOKUP a mistake in this case? I mean, if he's not sure VLOOKUP is the solution why isn't he asking for another approach?...

Oh, I reckon there probably are better ways of skinnin' the cat if that's what he's doing. But at this point, I'm thinkin' we're stuck coolin' our keyboards until Mr. reducedjuice rejoins our little thread here and shares a little more knowledge.

 

[reducedjuice]

Hi All,

Thanks so much for all yoru responses and suggestions (and sorry for temporarily disappearing after my post). Looks like there's a few interested parties to see what works. Here are a few comments based on the responses in the string:

@gecs and @lenze: I was trying to match customer code X and return the corresponding value for customer code Y. Therefore, I do not believe it was a matter of me switching those values around in the formula as you had suggested.

@Aladin Akyurek: yes, WS2!A:B is sorted in column B in ascending order

@VoG: your formula has worked and provided me with the solution I was seeking.

I thank you all for taking the time and for your collaborative approach.

-reducedjuice