For Sheet2!C1, maybe try:

=INDEX(Sheet1!$A$1:$A$100,MATCH(1,(LEFT(Sheet1!$A$ 1:$A$100,1)=LEFT(A1,1))*(MID(Sheet1!$A$1:$A$100,2, LEN(Sheet1!$A$1:$A$100))=MID(B1,FIND("
",B1)+1,LEN(B1))),0))

This is an array formula so you should enter it with Ctrl+Shift+Enter.

It assumes that your codes consist of the first letter of the first
name and the entire last name. It also assumes no variations (e.g.
middle names etc).

HTH
Kostis Vezerides

samuel wrote:
let me try this

Sheet1
A1 B1
SHOLDER SAMUEL HOLDER
BSMITH BARBARA SMITH
SHEET 2
A1 B1 C1
SAM SAM HOLDER
BARB BARB SMITH

I want to find the value of Sheet2:B1 in the B column of Sheet1 and record
the value of Sheet1:A1 into Sheet2:c1

The problem is the Sheet1 B column cotains full names. Any thoughts?


"Fred Smith" wrote:

Yes, lookup Vlookup in Help.

If you still need help, post back with more specific information.

--
Regards,
Fred


"samuel" wrote in message
...
i have 2 different worksheets.

Sheet 1:
A B C D
LastName FirstName FirstName LastName

Sheet 2:
A B
UserID FirstName Middle LastName

I want to search Sheet2 Column B for containing Sheet1 column D

If Sheet1 D is found in Sheet2 B then return Sheet2 A into Sheet1 A

Is there a way to do this?