find text from 1 col in 2nd col & return value from 3rd col

find text from 1 col in 2nd col & return value from 3rd col

Post by Um9iZXJ » Fri, 10 Jul 2009 07:46:01



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
 
 
 

find text from 1 col in 2nd col & return value from 3rd col

Post by TWF4 » Fri, 10 Jul 2009 08:11:01


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://www.yqcomputer.com/
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 
 
 

find text from 1 col in 2nd col & return value from 3rd col

Post by smarti » Fri, 10 Jul 2009 08:25:32


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.
 
 
 

find text from 1 col in 2nd col & return value from 3rd col

Post by U2hhbmUgRG » Fri, 10 Jul 2009 09:28:03

Hi,

If you names on sheet1 are in ascending sort then:

=LOOKUP(MID(B1,FIND(",",B1)+1,30),Sheet1!C:C,Sheet1!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
 
 
 

find text from 1 col in 2nd col & return value from 3rd col

Post by Um9iZXJ » Fri, 10 Jul 2009 11:35:01


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)))