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 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)

Answer 2

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