Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function
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 " wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function
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 wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
V Lookup & IF Function | Excel Discussion (Misc queries) | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
Lookup Function | Excel Discussion (Misc queries) | |||
Need help on LOOKUP function..please | New Users to Excel | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |