Vlookup problem

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

Here's a single formula

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

Simplified version (without error handlers) :-

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

Ada