Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find text from 1 col in 2nd col & return value from 3rd col
Hello,
I have: Sheet1 with empoyee numbers on A:A and last names on C:C Sheet2 with concantinated names (lastname,firstname) on B:B (always with a comma between if that helps). I need some kind of lookup/search formula on sheet2 A:A to extract the last name from sheet2 B:B, then take this extracted last name and match it up on sheet1 C:C , then return the emplyee # of sheet1 AA from the same row. Thanks in advance for any help, Robert |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find text from 1 col in 2nd col & return value from 3rd col
In Sheet2,
Place this in A2: =INDEX(Sheet1!A:A,MATCH(TRIM(LEFT(B2,SEARCH(",",B2 )-1)),Sheet1!C:C,0)) Copy down -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Robert" wrote: I have: Sheet1 with empoyee numbers on A:A and last names on C:C Sheet2 with concantinated names (lastname,firstname) on B:B (always with a comma between if that helps). I need some kind of lookup/search formula on sheet2 A:A to extract the last name from sheet2 B:B, then take this extracted last name and match it up on sheet1 C:C , then return the emplyee # of sheet1 AA from the same row. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find text from 1 col in 2nd col & return value from 3rd col
Robert wrote:
Hello, I have: Sheet1 with empoyee numbers on A:A and last names on C:C Sheet2 with concantinated names (lastname,firstname) on B:B (always with a comma between if that helps). I need some kind of lookup/search formula on sheet2 A:A to extract the last name from sheet2 B:B, then take this extracted last name and match it up on sheet1 C:C , then return the emplyee # of sheet1 AA from the same row. Thanks in advance for any help, Robert In Sheet2!A2 and fill down: =INDEX(Sheet1!A:A,MATCH(LEFT(B2,FIND(",",B2)-1),Sheet1!C:C,0)) This will not work properly if any duplicate last names exist. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find text from 1 col in 2nd col & return value from 3rd col
Hi,
If you names on sheet1 are in ascending sort then: =LOOKUP(MID(B1,FIND(",",B1)+1,30),Sheet1!C:C,Sheet 1!A:A) if not =OFFSET(Sheet1!A$1,MATCH(MID(B1,FIND(",",B1)+1,30) ,Sheet1!C:C,)-1,) adjust the "," by adding a space after the comma if there is one in your data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Robert" wrote: Hello, I have: Sheet1 with empoyee numbers on A:A and last names on C:C Sheet2 with concantinated names (lastname,firstname) on B:B (always with a comma between if that helps). I need some kind of lookup/search formula on sheet2 A:A to extract the last name from sheet2 B:B, then take this extracted last name and match it up on sheet1 C:C , then return the emplyee # of sheet1 AA from the same row. Thanks in advance for any help, Robert |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find text from 1 col in 2nd col & return value from 3rd col
This worked, thanks. I added error correction to it:
=IF(ISERROR(INDEX(Technicians!A:A,MATCH(TRIM(LEFT( B36,SEARCH(",",B36)-1)),Technicians!C:C,0))),"",INDEX(Technicians!A:A, MATCH(TRIM(LEFT(B36,SEARCH(",",B36)-1)),Technicians!C:C,0))) "Max" wrote: In Sheet2, Place this in A2: =INDEX(Sheet1!A:A,MATCH(TRIM(LEFT(B2,SEARCH(",",B2 )-1)),Sheet1!C:C,0)) Copy down -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Robert" wrote: I have: Sheet1 with empoyee numbers on A:A and last names on C:C Sheet2 with concantinated names (lastname,firstname) on B:B (always with a comma between if that helps). I need some kind of lookup/search formula on sheet2 A:A to extract the last name from sheet2 B:B, then take this extracted last name and match it up on sheet1 C:C , then return the emplyee # of sheet1 AA from the same row. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value between range & return text | Excel Worksheet Functions | |||
FIND function does not return ZERO when text is not found | Excel Worksheet Functions | |||
Find text and return - NOOB question | Excel Discussion (Misc queries) | |||
Find $ in a string of text and return numbers | Excel Discussion (Misc queries) | |||
Look for similar text and find the largest value and return value | Excel Discussion (Misc queries) |