Hi,
Not certain that I fully understand your question, but....
In C1 of Sheet3 enter: =MID(Sheet1!A2,6,6)
In C2 of Sheet3 enter: =VLOOKUP(C1, Sheet2!$A$2:$C$3, 2, False)
In C3 of Sheet3 enter: =Sheet1!A2
In C4 of Sheet3 enter: =Sheet1!B2
In C5 of Sheet3 enter: =Sheet1!C2
Copy the formulas in Sheet3 down as many rows as are in Sheet1.
Is this what you wanted?
--
Ken Hudson
"via135" wrote:
hi!
in sheet1 i'm having the following data thru A1:C6
old_ac--------------new_ac----------name
08765001111------10455------------ AAA
07654002222------10613------------ BBB
0356700111100----10489------------AAA
0258900111101----10356------------AAA
0987500222200----10856------------BBB
in sheet2 thru A1:C3
old_cus----------- -new_cus--------------name
001111------------82055614-------------AAA
002222------------82056763-------------BBB
the point is the *six digits* starting from 6th digit in the "old_ac"
always represents the "old_cus",
and the "old_ac" can't have more than 13 digits.
what i want is to extract the five fields "old_cus", "new_cus",
"old_ac", "new_ac" & "name"
in sheet3 for all the records in sheet1.
the columns "old_ac" & "old_cus" has been formatted as text..!
any hlp pl..??
-via135
--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=546847