I have two excel spreadsheet that I need to compare using vlookup, using case number to do so. The two sheets have case number stored differently, for example, one has the, displayed as 2012DT00001 or 2001CM00012 and the other sheet displays them as 2012DT1 or 2001CM12. Obviously, they are the same record but when using vlookup to compare the record and bring over information that is on one and not the other it does not work well. I have tried using vlookup to compare and look for approximate matches (TRUE). However, when I look at a sampling of the information it misses 80% of the records. At this point, I am thinking that the vlookup function is missing those records as a result of the two case numbers being different as a result of the unneeded zeros in the one. Any help with this would be greatly appreciated.

Here are two ways to do it: With your data in Column A:

A B 1) 2012DT00001 2012DT1 2) 2001CM00012 2001CM12In cell B1 enter the formula: =LEFT(A1,6)&VALUE(RIGHT(A1,5))

Drag down as needed.You can now use Column B in your =VLOOKUP()

something along the lines of:=VLOOKUP(B1,Sheet2!A1:B2,2,1)

The second way is to use the formula directly in your =VLOOKUP()

which eliminates the need for column BSomething like:

=VLOOKUP(LEFT(A1,6)&VALUE(RIGHT(A1,5)),Sheet2!A1:B2,2,1)

The trick is in the: VALUE(RIGHT(A1,5)) section,

it takes the last five characters of your text string,

IE 00001 or 00012

and converts them from TEXT to a NUMBER,

and since Excel strips out any leading zeros in a number

you end up with your required digit.See how that works for you.

MIKE

This worked great! I used the =LEFT(A1,6)&VALUE(RIGHT(A1,5)) as you suggested from there use the VLOOKUP as previously described. I was able to connect and add information to thousands of records that was previously missing. Thank you!

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History