First, Your data doesn't have to be text. It's just that often your search
argument is sometimes text and sometimes numbers, that make the results
looking inconsistent.
So the easiest thing is to make sure your search argument is a number (if
you're sure your table is all numbers). You can use the VALUE() function for
that.
If for some reason you insist on making your table text, introduce a
(temporary) extra column, use the TEXT() function to convert to text, copy
and replace the original with the results using Paste SpecialValues

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"JoFlo " wrote in message
...
I have a VERY large table that I want to use in a VLOOKUP function. The
first column of the table, if I understand correctly, needs to be in
text format. It is currently all in number format, causing my VLOOKUP
function to not work properly. If I right click on the entire column,
and format it into TEXT format, the numbers all align over to the left,
appearing to have been converted to text. However, the VLOOKUP
function still doesn't work. BUT....if I find the value in the table
manually, F2 it, and then hit ENTER, my VLOOKUP function then DOES
work.

It almost seems to be that it doesn't actually convert to text unless I
do this step (F2, enter). The problem is, there's over 40000 rows in
this table, so I can't possibly do this to every cell in that first
column. Is there a macro (I'm macro-challenged) or some other way that
I can do this quickly and easily?


---
Message posted from http://www.ExcelForum.com/