how to get data in sheet2 from sheet1 in excel

Question

I have two worksheets,

Sheet1
Column A = Deptname
Column B = Headname
Column C = Username

Sheet2
Column A = Headname (???)
Column B = Username
Column C = UserID

"Headname" column in Sheet2 is blank and what I wanted to do is to get "Headname" from Sheet1 by using "Username".

I have tried to use VLookup but it did not work if username in Sheet1 and Sheet2 is not exactly same.

E.G, given two differents Username as shown on below.

• Username in Sheet1 is "Jenny Oh" and "Chan Shu Mei"
• Username in Sheet2 is "ITC - Jenny Ong" and "IA: Chan Shu Mei"

Any ideas whether it can be done? Thankf in advance for any help.

Answer 1

You can access cells in another sheet like this: =sheet1!B53;

Answer 2

sory, i don't really get what you meant.

sheet1

username: Chan Shu Mei, Jenny Oh, Peter Chan, Ryan Ong

headname: BB, AA, CC, DD

dept: Y, X, Z, W

sheet 2

username: Peter Chan, ITC - Jenny Ong, IA: Chan Shu Mei

headname: ?? ?? ??

How do I get headname in sheet2 if username in both sheets are not exactly same. Thanks in advance for any help

Answer 3

Sounds to me like you're asking "how do I do regular expressions in Excel?", which is answered nicely here, for example. :-)

Answer 4

You seem to be asking "can I have excel match two strings when they're not exactly the same?"

The short answer is "no"

A computer cannot automatically determine that "Jenny Ong" and "ITC - Jenny Ong" are the same person without additional information.

The longer answer is "maybe"

You may be able to do some transformation to one or both of the names so that they are identical. For example, if all the entries in spreadsheet 2 are in the format "something - name" you might be able to use MID and FIND to extract just the name.

I think this formula should return the name of the person in the first record: =MID(B1,FIND(" - ", B1)+3,9999)