how to get data in sheet2 from sheet1 in excel

问题:

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.

回答1:

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

回答2:

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)