Posted by Judy P. on July 23, 2000 11:03 AM

Please I need help with the following problem. In column A:A I have accounts (# and name) and in column

B I have only account numbers (last 4 digits). In column C I need to extract the whole account number from column A based on the last 4 digits from column B.

A1=828810 dfoiasdof

A2=137322 sdafodsf

A3=311410 dslif

A4=730999 asdfdsadf 0999

A5=931999 dsfdsf

B1=7322

B2=0999

B3=1999

The result should be:

C1=137322

C2=730999

C3=931999

Thank you so much for your help.

Posted by Judy P. on July 24, 0100 6:22 AM

Please please please need help with this formula

Posted by Tim Francis-Wright on July 24, 0100 8:31 AM

Re: Please please please need help with this formula

I'll assume that all of the account numbers are exactly 6 digits long, and always at the far left

of each cell in column A.

Make sure that you have 2 free columns, say F and

G. G1 = LEFT(A1,6), copied down to the end of

the data. F1 = VALUE(RIGHT(G1,4)), copied down

to the end of the data.

Now, each of C1 to C3 will be like the following:

C1 = VLOOKUP(B1,$F$1:$G$5,2,FALSE)

You'll need to modify the $5 to be the number of

rows in Column A.

HTH

--tim f-w

Posted by Judy P. on July 24, 0100 9:48 AM

Thanks Tim, but there is no way to build just one formula without inserting (using) more columns?

Posted by Tim Francis-Wright on July 24, 0100 12:05 PM

Re: Thanks Tim, but there is no way to build just one formula without inserting (using) more columns?

I couldn't come up with a way w/o using VBA,

but here is a fairly simple VBA function that

will do the job. Not elegant, just simple.

Function Mylookup(Key As Object, InfoRange As Object)

Dim cel As Object

Dim TempS As String

Application.Volatile

For Each cel In InfoRange

TempS = cel.Value

If Key.Value = Val(Right(Left(TempS,6),4)) Then

Mylookup = TempS

Exit Function

End If

Next

Mylookup = "N/A"

End Function

Call this,for example, as

C1: = Mylookup(B1,$a$1:$a$5)

Posted by Ada on July 25, 0100 5:29 PM

Judy

=IF(ISNA(VLOOKUP("??"&B1&"*",$A$1:$A$10000,1,FALSE)),"",IF(B1="","",LEFT(VLOOKUP("??"&B1&"*",$A$1:$A$10000,1,FALSE),6)))

Ada

Posted by Ada on July 25, 0100 5:52 PM

Simplified version (without error handlers) :-

=LEFT(VLOOKUP("??"&B1&"*",$A$1:$A$10000,1,FALSE),6)

Ada