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

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
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 :)
TrowaD
Posts
2499
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 11, 2019
355 -
Glad I could help!
Respond to TrowaD