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

 

[Aladin Akyurek]

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

A faster alternative...

=IF(LOOKUP(A2,WS2!B:B)=A2,LOOKUP(A2,WS!B:B,WS!A:A),"NA")

 

[Greg Truby]

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

@ VoG - Phhhht!
@ Lenze - I guess he didn't like your 2^nd suggestion.
@ Aladin - you never cease to amaze me.

 

[kholmes]

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

I found this article too but I was confused on how to use it. I found your post online trying to understand it. If you understand this article, I was wondering if you could help me.

There is a master excel file on my company's server and I use VLOOKUP to automatically update my personal workbook using that information. The problem is the cells with the part numbers on the master workbook are formatted as text, number, or general (even though they are all numbers). Because of that, my VLOOKUP doesn't always work.

I'm pretty sure this "Alternative Solution" is just what I need, but I can't get it right. I don't see where they put the first argument that VLOOKUP uses to base its search. I'm probably missing other things too, but I don't know what. Help?

Thanks,
KHolmes

 

[Aladin Akyurek]

I found this article too but I was confused on how to use it. I found your post online trying to understand it. If you understand this article, I was wondering if you could help me.

There is a master excel file on my company's server and I use VLOOKUP to automatically update my personal workbook using that information. The problem is the cells with the part numbers on the master workbook are formatted as text, number, or general (even though they are all numbers). Because of that, my VLOOKUP doesn't always work.

I'm pretty sure this "Alternative Solution" is just what I need, but I can't get it right. I don't see where they put the first argument that VLOOKUP uses to base its search. I'm probably missing other things too, but I don't know what. Help?

Thanks,
KHolmes

Care to post the VLOOKUP formula that you have?