Lookup function

[ksgoodwin]

I am trying to add an employee value, something like "6TU", to a blank
column on a spreadsheet (sheet 1). The employee value is on a
seperate sheet (sheet 2) with an associated employee number in the
same row. e.g. Col A has employee number "12345"; Col B has the
employee value "6TU". I want to copy the employee values to Sheet 1
in association with the employee numbers. The problem: The employee
numbers on sheet 1 are imbeded in the name of the employee in Col A.
Something like "Jim Smith (12345)". What formula to use to extract
the number from Col A sheet 1, associate it with the right number on
sheet 2 Col A, to get the value on sheet 2 Col B, and put it in the
blank Col B on sheet 1.

 

[Sheeloo]

If all employee numbers are 5 digit then use this in
B1 of sheet1 and copy down
=MID(A1,FIND("(",A1)+1,5)

if not then use this to extract employee numbers
=MID(A2,FIND("(",A2)+1,LEN(A2)-FIND("(",A2)-1)

then in C1 enter this
=VLOOKUP(B1,Sheet2!A:B,2,False)
to pick the corresponding values from Col B of Sheet1

 

[Ashish Mathur]

Hi,

You can use this to extract the numeric portion from within the brackets

=MID(D29,SEARCH("(",D29,1)+1,SEARCH(")",D29,1)-SEARCH("(",D29,1)-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com