LOOKUP COLUMN VALUE

[Guest]

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?

 

[Fred Smith]

Yes, lookup Vlookup in Help.

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

 

[vezerid]

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

 

[vezerid]

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

 

[Lori]

Try filling down from C1 in sheet2:

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