Lookup function

K

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.
 
S

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top