LOOKUP COLUMN VALUE
 

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?

LOOKUP COLUMN VALUE
 

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?

LOOKUP COLUMN VALUE
 

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?

LOOKUP COLUMN VALUE
 

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?

LOOKUP COLUMN VALUE
 

thanks for the code. Sheet1 columnB will sometimes contain a middle name.

will that make this impossible?



"vezerid" wrote:

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?

LOOKUP COLUMN VALUE
 

No, it will not make it impossible. My concern now is, what will be the
structure of Sheet2!B1? Will it always be nicknames followed by last
name? Is there a case that the nickname is not a prefix of the actual
first name? SAM is a prefix of SAMUEL. KOSTIS, as is my name, is not a
prefix of KONSTANDINOS, which is my formal name.

And if the prefix rule does not hold, do we have any guarantee that the
last names are unique?

Kostis

samuel wrote:
thanks for the code. Sheet1 columnB will sometimes contain a middle name.

will that make this impossible?



"vezerid" wrote:

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?

LOOKUP COLUMN VALUE
 

Try filling down from C1 in sheet2:

=INDEX(Sheet1!A:A,MATCH(SUBSTITUTE(B1," ","*"),Sheet1!B:B,0))

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?