VLokup Not Recognizing Data

Hello everyone,

I am having trouble with the vlookup command. The problem is, the value the function is trying to lookup cannot be found in the data set I give it to look in (so excel says). I have it setup to find X in 2 columns on another worksheet where the first column contains X and the function is setup to return the 2n'd column value, say Y. Well, it is telling me X isn't there, but it is...

I did some digging by using the find function and found that the only way "find" will work is if I tell it to "look in" the "values", not the defualt "formulas". I know the data I am searching for does exists because I can copy the data from one sheet and "find" it in the other by telling it to look for a value instead of forumula. How can I tell the vlookup function to look in the "values" and not the "function"?

In addition, there are no functions in the data set I am searching through, it is imported data from a delimited text file.

How can I make this work?

Thanks!

Originally Posted by blakrapter

Hello everyone,

I am having trouble with the vlookup command. The problem is, the value the function is trying to lookup cannot be found in the data set I give it to look in (so excel says). I have it setup to find X in 2 columns on another worksheet where the first column contains X and the function is setup to return the 2n'd column value, say Y. Well, it is telling me X isn't there, but it is...

I did some digging by using the find function and found that the only way "find" will work is if I tell it to "look in" the "values", not the defualt "formulas". I know the data I am searching for does exists because I can copy the data from one sheet and "find" it in the other by telling it to look for a value instead of forumula. How can I tell the vlookup function to look in the "values" and not the "function"?

In addition, there are no functions in the data set I am searching through, it is imported data from a delimited text file.

How can I make this work?

Thanks!

Whether the value exists as a straightforward value or as the result of a formula is irrelevant. I suspect the problem you have is that what you have in the first column of the lookup table is a 'number' formatted as text, and the value you're looking up is a number. Or vice versa. To check this, with each of the two 'numbers' you're dealing with, and which you think are the same, apply the formula =ISTEXT(your 'number'). I guess you'll find one returns TRUE, and the other False.

In your VLOOKUP try

Please Login or Register  to view this content.

HTH

Mr. Buttrey,

Your a genius. THANKS!

Originally Posted by blakrapter

Mr. Buttrey,

Your a genius. THANKS!

Many thanks for that.

I should have added last time that since this is an imported csv file, in the import wizard, if you select the 'number' column and set it as a number, rather than accepting the default 'General' or perhaps 'Text' option, then you'll avoid having to have an extra VALUE() or TEXT() function at the beginning of your VLOOKUP().

Rgds