Need help with a VLOOKUP formula & two similar, yet non-identical columns of data.

[mikey386]

I am working with two separate worksheets.




In the 1st worksheet I need a VLOOKUP formula that checks A2 & looks for the same ID# in the 2nd sheet (also in column A2) - then it must return the data in the column to the right.


The problem is that the ID# numbers in each column of the worksheets are similar, yet different.


Here is an example of how the ID numbers appear in the first worksheet:


Column A
907677/001/1 *
907694/001/2
907694/001/3
907695/001/1


"/1" references the number of times the ID# appears in the table


- and the 2nd worksheet (which I need to be able to pull data from)


Column A
907677/001
907694/001
907694/001
907695/001


As you can see - my problem is that in the 2nd worksheet the last part of of the string "/#" is missing - so there is no unique identifier to use to differentiate between the first appearance of an ID# and the second.

Is there anyway to add this ability to column A of the 2nd worksheet so that I can have my precious VLOOKUP and get on with the rest of my life?


Any & all help is GREATLY appreciated.


Pardon any confusion or wordiness.

[Pecoflyer]

Wild guess perhaps something like =vlookup(left(a1,10),sheet2!$a$1:$b$100,2).

Post an example sheet showing BEFORE and AFTER) if this is not what you need