I'm having a problem with the vlookup() (and/or match()) function that
I run into every so often but have never found a solution for.  I will
have to do my best to simply describe it because the data I'm dealing
with is very confidential.

I have a list that cross references employee ID numbers with the
country in which they are employed.  I need to use this list to insert
the country of employement into another spreadsheet which contains the
employee ID number and a whole plethora of other data.  This should be
very simple to do with a vlookup and I've used it successfully in many
other files with similar data without any issue whatsoever - no
special steps, no effort to reformat nothing.

The employee ID's are mixed data - some have an alpha character and
then some numbers i.e. A384827, some have just numbers i.e. 34832838
and some have dashes in the data such as 000-00-0000.

Now there is something wrong with the formatting in column for the
Employee ID that I can't seem to change.  Everywehre else I have this
data I have the little green triangle on all the fields that have just
numbers with no other characters warning me that I have a number
stored as text.  In the sheet that I'm trying to inser the data into
the Employee ID column does not have those triangles on the numbers so
I can tell that the formating must be somehow different.  Originally
one column of numbers had the leading ' and one did not.  I got rid of
that by copying and pasting values only to a new spreadsheet but still
I have the green triangles on one set of data and not on the other.  I
tried converting the employee ID data on my master spread sheet to a
"number stored as text" like it is on my cross reference list but it
doens't work.  Data in the format of 000-00-0000 shows up identically
but any cell where I'm trying to convert something that just looks
like a number - i.e. 34828348 comes up blank after being passed
through the =t() function.  I don't understand it all.  I don't
understand why this crazy formating problem stays with the data evne
when I copy and paste the data into a new spreadsheet using the paste
special value function of excel.

I know that this is possible to do without problems because I have had
many other spreadsheets where the data (although mixed as described
above) somehow had compatable formating because the vlookup function
worked without issue.

Can anyone tell me how to sort this out for this specific situation
and also what the solution is when I encounter this problem in
general.  In the past I always just gave after many frustrating
attempts but this is too important.  If worst comes to worst I will
have to manaully retype the data but htere are hundreds of lines and
this would take hours.

Thanks!

Hello Brh986,

From your description, the "Employee ID" column one of your worksheets
is likely formatted as "General" and not as "Text". In this case, you
may have mixed data with "numbers" and "text" in the same Employee ID
column. Excel treats numbers far different than text (as you've
discovered) and a comparison of a "text" number and a real number will
fail (unless you do special handling).

It is likely in the worksheets where everything is OK, the cells are
formatted as text OR the values ARE text - entered with a leading
single quote (') and no trailing quote. The leading quote trick is a
way to force Excel to accept a number as text.
[from your question - it appears some of your data was entered in that form]

Starting with an empty worksheet, I created a simple example that
illustrates the issues.

Column A - titled "Employee ID" with cell format general (the
default). Enter the values you mentioned in your question (A384827,
34832838, and 000-00-0000) and you should notice that the second value
is right aligned and the other two are left aligned. This is a visual
hint that the second value was converted to a number and the other two
are text.

Column B - titled "Ditto" with cell format Text (menu Format -> Cells
-> select Text). Enter the values you mentioned (A384827, 34832838,
and 000-00-0000) and you should notice that all three values are text.
[don't copy the values from the first column - enter them again]

Column C - titled "T Value". Enter the formula =T(A2) in C2 and fill
down to C4. Only two of the values are shown as text, the second one
is blank (same symptom as you've reported).

In Columns D and E, I also added a VLOOKUP function reading
  =VLOOKUP(E2,A2:B4,2,FALSE)
in D2 and entered the value I was trying to lookup in E2. If you leave
the format of E2 as General, it can lookup all three values (first and
third as text, second as a number). If you format E2 as Text, it can
only lookup the first and third value, the second results in #N/A as
the result.

Since you don't describe the precise format of your worksheets, I
cannot provide a direct solution, but there is more than one way that
should be able to "fix" your data.

[1] Using the sample worksheet layout I described above, insert
  =IF(T(A3)="",TEXT(A3,"#"),A3)
into cell F2 and fill down. The values displayed will all be in "text"
even if the original value is a number. You can then copy / paste
these "text only" values over the original data to convert all the
values to be text. This works if the Employee ID fields are formatted
as text OR as General. I assume you have a blank column you can use in
your worksheet to do a similar IF statement.

[2] Insert a column next to "Employee ID" and format as Text. Copy the
employee ID values using
  Copy
  Paste Special -> Values
and the resulting values in the new column should be text. You can now
copy these values (I suggest Copy & then Paste, to preserve the Text
formatting). You can then remove the extra column to return the
worksheet to the original layout.

[3] A "harder" method is to format the "Employee ID" column as text
and then fix the erroneous values by hand. Changing the format, the
cells "look" all right, but the values entered as numbers are still
numbers. Selecting the region and using copy & paste (or paste special
-> value) is not good enough either. But you could simply select the
"wrong" values, one at a time, and add a space / remove it in the
formula bar to convert the value from a number to text.
[don't ask me why Excel does this - I don't know]

If you have any difficulty understanding this answer or have further
problems getting your data "clean" so the vlookup function always
works - please make a clarification request. I would be glad to help
you further on this problem.

Good luck with your work.

  --Maniac

You issue here is one list has values both a numbers and the text the
other list has the same but not necessarily for the same record.

I have been able to get around this by imbedding my vlookup statement
in an IF statement.

In sheet1, Assume Column A has your Employee ID Column B has the
country where they are employed.

In sheet2 you have the employee ID in column A, with all the other
information but not the country of employement.

Try using the following formula within sheet2

=IF(ISNA(VLOOKUP(A1,'Sheet1'!$A$1:$B$4,2,FALSE))=TRUE,VLOOKUP(VALUE(A1),'sheet1'!$A$1:$B$4,2,FALSE),VLOOKUP(A1,'sheet1'!$A$1:$B$4,2,FALSE))

With this formula the vlookup is attempted based on a text value, and
then if it fails, it is attempted based on a numeric value.