How to Match Data in Excel from 2 Worksheets

There are several simple and easy methods available in Microsoft Excel to match data from 2 worksheets. We can use functions, formulas, or apply commands from Excel ribbons and get our desired outputs. In this article, you’ll get to know all suitable procedures to match data from 2 worksheets and return values accordingly.

How to Match Data from 2 Worksheets in Excel: 6 Suitable Methods

1. Compare and Match Data from 2 Worksheets by Viewing Side-by-Side in Different Workbooks

Let’s assume we have two different workbooks named Book 1 and Book 2. Both workbooks have some data starting from the same row in Column B. Now we’ll display these workbooks side-by-side and compare data visually for matches.

📌 Step 1:

➤ Go to the first workbook Book 1.

➤ Under the View tab, select the View Side by Side command from the Window drop-down.

📌 Step 2:

➤ Select Synchronous Scrolling under the View Side by Side option.

📌 Step 3:

➤ Click on the Arrange All option. A dialog box will appear.

📌 Step 4:

➤ Choose the Vertical radio button from the options.

➤ Press OK.

Like in the screenshot below, you’ll be displayed two workbooks side-by-side and you’ll be able to compare data and find matches easily here. This method is helpful when you have to compare a small amount of data visually and find matches side-by-side from two workbooks.

As we’ve enabled Synchronous Scrolling, so if we scroll down in a workbook another workbook will follow the action accordingly.

2. Compare and Match Data from 2 Worksheets by Viewing Side-by-Side in Similar Workbook

Now we’ll compare two different worksheets in a similar workbook. In the following picture, there are two worksheets named Sheet1 and Sheet2 lying in an Excel workbook. What we’ll do here is split these spreadsheets into two different workbooks and then follow the previous method to compare data and find matches side-by-side.

📌 Steps:

➤ Go to the View ribbon.

➤ Select the command New Window from the Window group of commands.

You’ll find two different workbooks as shown in the following picture. Both workbooks will have similar worksheets. Now you can easily repeat the first method to compare data for Sheet1 from the first workbook and Sheet2 from the second workbook and find matches visually.

3. Match Data Side-by-Side from 2 Worksheets in Same Workbook and Return Outputs in Excel

In this section, we’ll apply the IF function to find matches side-by-side from two worksheets. The IF function will return the message Yes for the matched data and No for the non-matches. The following picture represents Sheet1.

And the next one represents the data from Sheet2.

To return outputs with Yes and No for all matches and non-matches respectively in Sheet1, let’s go through the steps below.

📌 Step 1:

➤ In the output cell C5 in Sheet1, type the following formula:

📌 Step 2:

➤ Press Enter and use Fill Handle to autofill the rest of the cells in Column C under the Match header.

And you’ll find all the return values with Yes and No for the matches and non-matches side-by-side from two worksheets.

4. Match Data Side-by-Side from 2 Worksheets in Excel by Highlighting Rows

We can also find matches and highlight them with color by using Conditional Formatting. Let’s go through the following procedures to execute the output.

📌 Step 1:

➤ Under the Home tab, select the option New Rule from the Conditional Formatting drop-down.

A dialog box named New Formatting Rule will appear.

📌 Step 2:

➤ Select the rule type option Use a formula to determine which cells to format.

➤ In the Rule Description box, type the following formula:

➤ Press Format.

📌 Step 3:

➤ In the Format Cells window, go to the Fill tab.

➤ Select a color that will highlight the matches from two worksheets.

➤ Press OK.

📌 Step 4:

➤ In the New Formatting Rule dialog box, you’ll find the preview of the highlighted cell with text at the bottom.

➤ Press OK and you’re done.

In Sheet1, you’ll notice all the matches highlighted with the specified color.

5. Use VLOOKUP to Match Data from 2 Worksheets and Return Values in Excel

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from the specified column. By using this VLOOKUP function, we’ll now find matches from two worksheets and return values based on the matches from the second worksheet.

In the following picture, two columns are present with ID and Name and Address headers.

And here’s the second worksheet where the complete data are stored. What we’ll do is find matches of the ID and names in the second worksheet (Sheet2), extract addresses for the corresponding matches, and display them in the first worksheet (Sheet1).

As the VLOOKUP function will return a #N/A error if a match is not found, so we’ll combine the VLOOKUP function with the IFERROR function. It’ll return a customized message Not Found if the VLOOKUP function does not find a match in Sheet2.

So, the required formula in the first output cell C5 in Sheet1 will be:

After pressing Enter and filling down the entire column, we’ll find the following outputs under the Address header in Sheet1.

6. Apply INDEX-MATCH Formula to Match Data and Return Values after Comparing 2 Worksheets

We have a suitable alternative to the VLOOKUP function and that is the combination of the INDEX and MATCH functions. The INDEX function returns a value or reference of the cell at the intersection of a particular row and column in a given range. And the MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.

Since the INDEX-MATCH formula will return an error if a match is not found, so we’ll again use the IFERROR function in front to customize a return message for the non-matches.

The combined formula with the related functions in the output cell C5 will be now:

Press Enter, autofill the rest of the cells in Column C, and you’ll get similar outputs as found in the previous method.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope, all of these methods mentioned above will now help you to apply them in your Excel workbook when you have to match data from two different worksheets. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

<< Go Back to | Excel Match | Learn Excel