Overview

This article explains how to use the VLOOKUP formula in Microsoft Excel to merge data from two different lists. The formula lets you search for a value in the first column of a range and return a value from another column. This is especially useful when you need to merge data from two different lists based on a common identifier, such as Student IDs or Exam Numbers. Using a VLOOKUP formula ensures accuracy and efficiency when dealing with large datasets.

Prerequisites

• You must have a licensed copy of Microsoft Excel.
• You must have a basic understanding of Microsoft Excel formulas and navigation.

Instructions

This example explains how to use a VLOOKUP formula to insert Admission Numbers held in one worksheet into another worksheet:

• 1. Open both the Excel workbooks (spreadsheets) containing the datasets:
     
  2. Ensure that the common identifier, for example, Student ID, is present in both datasets. This will be used to compare the data and merge the relevant part of the two datasets together. 
  3. In one of the Excel workbooks, select + to create a new Sheet:
     
  4. Copy the content from the other Excel workbook into the new Sheet:
     1. Select Sheet 2.
     2. Navigate to the other Excel workbook.
     3. Press CTRL + A to select the content (press CTRL + A):
        
     4. Press CTRL + C to copy the content.
     5. Return to Sheet 2 in the original Excel workbook.
     6. Select cell A1:
        
     7. Press CTRL + V to paste the content you copied into Sheet 2:
        
  5. Check that your worksheets (Sheet 1 and Sheet 2) contain the appropriate data:
     
• 1. In the primary worksheet (where you want the merged data to appear), in this example, Sheet 1 insert a new column:
     
  2. Label this column appropriately, for example, Admission Number:
     
• 1. Select the first cell under the heading in the new column, for example, C2.
  2. Type in the following formula: =VLOOKUP(A2,Sheet2!B2:C6,3,FALSE). When you try this using different datasets, replace A2 with the correct cell reference, and Sheet2!B2:C6 with the relevant range in the second datasource (in this example, Sheet 2). Also, change the 3 used in this example to the column you want to display when you do this using another dataset. You can use your mouse to locate and select the range. FALSE is included in the formula to ensure it looks for an exact match: 
     
  3. Press Enter. In the example, the result 120001 will populate Sheet 1, Cell C2:
     
• 1. Select the cell containing the VLOOKUP formula, for example, C2:
     
  2. Highlight the range part of the formula, for example, Sheet2!B2:C6:
     
  3. Press F4 to make it absolute (resulting in Sheet2!$B$2:$C$6). By making it absolute, whenever you copy the formula, it will always reference the same range:
     
• 1. Select the cell containing the VLOOKUP formula, in this example, C2.
  2. Use the fill handle to drag the formula down or double-click it to fill the column:
     

• Check a few entries to ensure that the correct data has been retrieved - for example, look at Sheet 1, and make sure the correct Admission Numbers are being displayed from Sheet 2:
  

Outcome

You have successfully merged the data using the VLOOKUP formula:

You can use this for much more than just adding Admission Numbers for your students, although this can be particularly handy if Student IDs have changed in your MIS but not in Sisra Analytics, meaning you need to match the new Student IDs with the old ones. You could, for example, use it to merge two completely separate lists of grades, even if they contain different numbers of students. It’s always much quicker and safer than attempting these merges by eye!

Updated 09 May 2025 14:51