Any Excel expert here? Need help.

[razibhasan]

Greetings!
I am learning Excel myself. Right now I'm in a problem doing something. I have a workbook with three columns. Column 1 contains Email addresses, Column 2 contains First Name and Column 3 contains Last Name.

My second workbook contains only email addresses (a few) from first work book. How I can get other fields easily from first workbook? I mean first name and last name?

Can anyone help me regarding this?

Any help will be appreciated.

Regards.

 

[mayest]

Assume that on Sheet1 you have email, first name, last name in columns A, B, and C. On Sheet2 you just have email addresses in column A. To pull in the first name and last name that match a given email address you can use VLookup(), but that won't work if your email addresses are anywhere other than the first column.

This solution will work no matter what column the email address is in on Sheet1. I'm assuming that the sheet1 data is in A2:A4:

To get first name: =INDEX(Sheet1!$A$2:$C$4,MATCH(A2,Sheet1!$A$2:$A$4,0),2)
To get last name: =INDEX(Sheet1!$A$2:$C$4,MATCH(A2,Sheet1!$A$2:$A$4,0),3)

Those formulas get the email address from A2 on Sheet2. Then, they look it up in column A of Sheet1. Finally, they return the first or last name, as appropriate.

 

[razibhasan]

Hi, thanks for your reply. I've uploaded my file here >> http://www.send-file.com/C5300F05C515BF0B . Can you please review it? I've also sent you a PM. Please check that.

Respectfully,
Razib

 

[dderolph]

Originally posted by: mayestThis solution will work no matter what column the email address is in on Sheet1. I'm assuming that the sheet1 data is in A2:A4:

To get first name: =INDEX(Sheet1!$A$2:$C$4,MATCH(A2,Sheet1!$A$2:$A$4,0),2)
To get last name: =INDEX(Sheet1!$A$2:$C$4,MATCH(A2,Sheet1!$A$2:$A$4,0),3)

Those formulas get the email address from A2 on Sheet2. Then, they look it up in column A of Sheet1. Finally, they return the first or last name, as appropriate.

Do those formulas need to be in certain cells?

 

[mayest]

Razib,

The problem is that it seems that your lists of email addresses are from two different sources. Some of them on Source have one or two spaces at the end, and their matching address on the other sheet may no space or a different number of spaces. So, you need to clean up your data. I would create a new column and put =Trim(A1) and then copy it down. Then, copy that entire new column and do a Paste Special Values over the original data. Do that on both sheets and then the formulas will work.

 

[mayest]

Originally posted by: dderolph
Do those formulas need to be in certain cells?

No, you can put them anywhere as long as they are referencing the appropriate cells and ranges.