Menu
Ask a question Report

Copying Excel data from one sheet to another based upon contents of a cell

Posts
2
Registration date
Monday July 8, 2019
Status
Member
Last seen
July 10, 2019
 - - Latest reply: TrowaD
Posts
2499
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 11, 2019
 - Jul 11, 2019 at 11:42 AM
Excel Ask a question Report
Hi, I have a spreadsheet containing two sheets.

Sheet 1 contains the following:

Col A - username
Col B - Full Name
Col C - Email address
Col D - Phone number

Sheet 2 contains the following:

Col A - Active Directory group name
Col B - user name

I require a macro/formula to look at the username in Sheet 2, col B, find it in Sheet 1 Col A, and then copy the contents of Columns B, C and D back to Sheet 2, Columns C, D and E

The end result is that Sheet 2 will be populated with the AD Group Name, Username, Full Name, email addy and phone number.

Hope you can help.

Regards
Dittdott
See more 

1 reply

Report Respond to TrowaD
Posts
2499
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 11, 2019
 355
0
Thank you
Hi DittDott,

Excel has the VLOOKUP function for that.

Formula for Sheet2 C2:
=VLOOKUP(B2,Sheet1!$A$2:$D$6,2,0)

Formula for Sheet2 D2:
=VLOOKUP(B2,Sheet1!$A$2:$D$6,3,0)

Formula for Sheet2 E2:
=VLOOKUP(B2,Sheet1!$A$2:$D$6,4,0)

Adjust the range $A$2:$D$6 to match yours, check the results, then select C2, D2 and E2 and drag the formulas down as far as needed.

Best regards,
Trowa
Report Respond to DittDott
DittDott
Posts
2
Registration date
Monday July 8, 2019
Status
Member
Last seen
July 10, 2019
 -
Awesome! thank you so much, bit of a numpty when it comes to Excel, I never use. Really appreciate the feedback. Have an awesome day Trowa :)
Report Respond to TrowaD
TrowaD
Posts
2499
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 11, 2019
 355 -
Glad I could help!
Respond to TrowaD
Reply Ask a question