VLOOKUP PROBLEM
Posted by KELLY JACKSON on February 13, 2002 5:04 AM
I NEED TO KNOW HOW TO CHANGE THE FORMAT ON A COLUMN OF NUMBERS FROM SPECIAL TO SOMETHING THAT A VLOOKUP WILL READ WITHOUT HAVING TO RETYPE ALL OF THE NUMBERS IN THE COLUMN. (IF I RETYPE ALL THE NUMBERS AFTER I HAVE CHANGED THE COLUMN TO TEXT THEN THE VLOOKUP WILL RECOGNIZE THE NUMBERS) CAN IT BE DONE? IF SO, HOW? THANK YOU FOR YOUR HELP IN ADVANCE!
Posted by Don C on February 13, 2002 5:11 AM
Not sure just what you want here:
If you have digits that Excel "sees" as text, you can multiply each cell in the column by one (1) and they will change to numerals.
If you have numerals and want them to become text, then the TEXT function will work.
Do you need help doing either of these?
BTW - bulletin board curtesy asks you to release the caps lock when you type. All caps is hard to read.
Posted by Will R on February 13, 2002 5:33 AM
I agree...Insert a column next to your text numbers and multiply the text numbers by one
to get a result that Vlookup will recognise.
Posted by Sam S on February 13, 2002 1:35 PM
try this - =vlookup(A1+0,table,column,false).
Adding the "+0" converts any numbers that have been created as text back to numbers but should have no effect no the text.
Posted by KELLY J on February 14, 2002 7:02 AM
In this case, the format for the column of numbers comes in as "special format" the vlookup will not recognize this column until i change the entire column to "text" then i have to retype the number in, then it will recognize it. why?
Posted by Don C on February 14, 2002 2:07 PM
I am not sure what the "special format" is that you are getting, but you should not have to retype anything. There are a number of ways to convert the column to "real" numbers, several of which are listed in this thread.
Another way is to use the Value function ...
=Value(a2)
or embed that in the formula
=VLOOKUP(Value(a2),...
Have you tried any of these suggestions?
If for some reason, the vlookup only works on text (can't imagine why, though) you can force the column to be text:
=Text(A2,"000") where you enter as many zeros as the number of digits you need (if the largest number is 999, use three zeros).
In this case, the format for the column of numbers comes in as "special format" the vlookup will not recognize this column until i change the entire column to "text" then i have to retype the number in, then it will recognize it. why?
