Excel Lookup matching field to Auto Fill/Populate Data

jetli87
jetli87 used Ask the Experts™
on
I have an xls file called "Masterlist" that has hundreds of contacts, i.e. Member ID, first name, last name, phone, and email in respective columns/rows.

I have another xls template that only contains Member ID in a column.  I need a formula/method to auto fill/populate the remaining info, i.e. first name, last name, phone, and email per row that matches Member ID on the template file from the Masterlist xls file.  

I'm assuming it's a vlookup or lookup formula, but need a bit more direction and most efficient method.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Power Query is the best tool to achieve this if you are using Excel 2010 or later. Or you can also use VBA to achieve this.
Finance Analyst
Commented:
Yes, VLOOKUP would be a way to do it.

Assuming Member Data file on Sheet1:
Column     Value
A                 member id
B                 first name
C                 last name
D                 phone
E                 email

Template file
Column      Formula/Value
A                 member id
B                 =VLOOKUP($A2,Sheet1!$A:$E,2,FALSE)
C                 =VLOOKUP($A2,Sheet1!$A:$E,3,FALSE)
D                 =VLOOKUP($A2,Sheet1!$A:$E,4,FALSE)
E                 =VLOOKUP($A2,Sheet1!$A:$E,5,FALSE)

The syntax for VLOOKUP is:
=VLOOKUP(Lookup Value, Lookup Range, Column Offset, Lookup type)

Lookup Value - your member id
Lookup range - the full data set master list with member id in the left most column of range
Column Offset - the column from the lookup range from which to return the value. You will notice that the only difference between the formulas is this parameter. It increases to return from the relevant column in the source. This can be set to be dynamic if so required by matching on a column header.
Lookup type - This decides whether to find a close match or an exact match; FALSE looks for an exact match.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial