5 Ways to Match Data from Two Excel Sheets

Introduction to Data Matching in Excel

Excel is a powerful tool widely used for data analysis, budgeting, tracking expenditures, and more. One of its essential capabilities is matching data between different sheets. This technique is crucial in various scenarios, such as:

• Comparing sales data from different periods.
• Merging customer records from multiple databases.
• Verifying financial transactions or audit trails.

5 Ways to Match Data from Two Excel Sheets

VLOOKUP Function

The VLOOKUP function is one of the most straightforward methods to match data between two Excel sheets. Here’s how you can use it:

1. Open your workbook with both sheets you want to compare.
2. In the sheet where you want to find matching data, enter the following formula:

    
    =VLOOKUP(A2, ‘Sheet2’!A:B, 2, FALSE)
    

🔍 Note: A2 is the cell in the current sheet you want to match, 'Sheet2'!A:B refers to the data range in the other sheet, 2 means the column number to return data from, and FALSE ensures an exact match.

INDEX and MATCH Functions

While VLOOKUP is easy to use, it has limitations like looking up values to the left. The INDEX and MATCH functions overcome this:

1. In the cell where you want the result:

    
    =INDEX(‘Sheet2’!B:B, MATCH(A2, ‘Sheet2’!A:A, 0))
    

🔍 Note: MATCH finds the position, and INDEX retrieves the value. This combination is more flexible than VLOOKUP.

Using Power Query

Power Query, available in Excel 2010 and later, provides advanced data transformation capabilities:

1. Go to Data > Get Data > From Other Sources > Blank Query.
2. Enter the following M code to load data from both sheets:

    
    let
        Source1 = Excel.CurrentWorkbook(){[Name=“Sheet1”]}[Content],
        Source2 = Excel.CurrentWorkbook(){[Name=“Sheet2”]}[Content],
        MergeData = Table.NestedJoin(Source1, {“ID”}, Source2, {“ID”}, “MatchedData”, JoinKind.LeftOuter),
        ExpandData = Table.ExpandTableColumn(MergeData, “MatchedData”, {“ColumnB”}, {“MatchedColumn”})
    in
        ExpandData
    

🔍 Note: Adjust sheet names and column names as per your data.

Conditional Formatting for Visual Matching

Sometimes, you might not need to merge or compare values directly but to visually highlight matches:

1. Select the range to format.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose “Use a formula to determine which cells to format”.
4. Use a formula like:

    
    =A2=‘Sheet2’!A2
    

to highlight cells that match from Sheet2 in Sheet1.

Using Macros for Automated Matching

For repetitive matching tasks, VBA macros can be your ally:

1. Open the VBA editor by pressing ALT + F11.
2. Insert a new module and paste the following code:

    
    Sub MatchData()
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = ThisWorkbook.Sheets(“Sheet1”)
        Set ws2 = ThisWorkbook.Sheets(“Sheet2”)

    For i = 2 To ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
        For j = 2 To ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
            If ws1.Cells(i, 1).Value = ws2.Cells(j, 1).Value Then
                ws1.Cells(i, 3).Value = ws2.Cells(j, 2).Value
                Exit For
            End If
        Next j
    Next i
End Sub
</code>

🔍 Note: This macro assumes the first column on both sheets contains matching keys and copies data from the second sheet to the third column in the first sheet.

In summary, mastering data matching in Excel allows you to perform complex data operations efficiently, from simple comparisons to merging vast datasets with precision. Whether you choose VLOOKUP for simplicity, INDEX/MATCH for flexibility, Power Query for advanced processing, Conditional Formatting for visual cues, or VBA macros for automation, Excel offers a solution for every level of data handling needs.

What are the limitations of VLOOKUP?

+

VLOOKUP cannot look to the left; it only searches for the key value in the first column of the lookup range. Also, it can only return values from one column.

Can INDEX and MATCH be more efficient than VLOOKUP?

+

Yes, INDEX and MATCH can be more efficient because they do not scan the whole column, reducing lookup time significantly for large datasets.

When should I use Power Query?

+

Power Query is ideal for complex data transformations or when dealing with multiple data sources or large datasets requiring regular updates.