Problem with Excel, the VLOOKUP() function and data formatting....
Category: Computers > Software
Asked by: brh986-ga
List Price: $4.50
14 Jul 2006 09:00 PDT
Expires: 13 Aug 2006 09:00 PDT
Question ID: 746309
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!
Re: Problem with Excel, the VLOOKUP() function and data formatting....
Answered By: maniac-ga on 18 Jul 2006 18:56 PDT
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.  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.  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.  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
Re: Problem with Excel, the VLOOKUP() function and data formatting....
From: djweiss-ga on 18 Jul 2006 16:51 PDT
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.
If you feel that you have found inappropriate content, please let us know by emailing us at firstname.lastname@example.org with the question ID listed above. Thank you.
|Search Google Answers for|