how to use vlookup

Question

Use vlookup to filter corresponding data and migrate corresponding data to master list

Answer 1

Hi @Kalafut, Tim,

Welcome to the Microsoft Q&A forum.

I would like to provide you with the following information:

To retrieve data using the VLOOKUP function, you can refer to the example below.

Assume I have three corresponding columns: ID, Name, and Department.

To retrieve the data from the Department column based on the ID, I use the VLOOKUP function as follows.

In this example:

• The lookup value is located in cell F2
• The entire data table is within the range A2:C4
• The column from which the data is retrieved is the 3rd column

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.   

Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.

I look forward to continuing the conversation.

---

 If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

To use VLOOKUP in Excel to pull corresponding data into a master list, set up a formula that looks up a key value from your master list in a source table and returns related columns.

Basic VLOOKUP syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Where:

• lookup_value: the value in the master list to search for (for example, an ID or name).
• table_array: the range in the source sheet that contains both the lookup column and the columns to return.
• col_index_num: the column number in table_array that contains the value to return (1 = left-most column of table_array).
• range_lookup: use FALSE for an exact match, TRUE (or omitted) for approximate match.

Example: migrate corresponding data to a master list

Assume:

• Master list is on Sheet1.
• Column A on Sheet1 contains IDs.
• Source data is on Sheet2 in A2:D100, with IDs in column A and the data to bring over in columns B–D.

On Sheet1, in the first row of the target column (for example, to bring over the value from Sheet2 column C):

=VLOOKUP(A2, Sheet2!$A$2:$D$100, 3, FALSE)

Explanation:

• A2 is the ID in the master list.
• Sheet2!$A$2:$D$100 is the source table.
• 3 tells VLOOKUP to return the value from the 3rd column of that table (column C).
• FALSE forces an exact match on the ID.

Then fill this formula down the column to migrate corresponding data for all rows in the master list. Repeat with different col_index_num values (3, 4, etc.) to bring over additional fields from the source table into other columns of the master list.

Key rules for VLOOKUP:

• The lookup value must be in the left-most column of table_array.
• col_index_num counts from the left of table_array, starting at 1.
• Use FALSE for exact matches when migrating data to avoid incorrect approximate matches.

---

References:

• Use Excel built-in functions to find data in a table or a range of cells
• VLOOKUP function
• Look up values in a list of data in Excel