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.


Download Practice Workbook

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


6 Suitable Methods to Match Data from 2 Worksheets in Excel

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.

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

📌 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.

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

📌 Step 2:

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

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

📌 Step 3:

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

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

📌 Step 4:

➤ Choose the Vertical radio button from the options.

➤ Press OK.

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

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.

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

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

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


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.

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

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.

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


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.

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

And the next one represents the data from Sheet2.

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

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:

=IF(B5=Sheet2!B5,"Yes","no")

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

📌 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.

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


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.

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

📌 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:

=B5=Sheet2!B5

➤ Press Format.

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

📌 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.

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

📌 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.

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

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

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


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.

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

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).

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

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:

=IFERROR(VLOOKUP(B5,Sheet2!B5:C14,2,FALSE),"Not Found")

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

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

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


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:

=IFERROR(INDEX(Sheet2!B5:C14,MATCH(Sheet1!B5,Sheet2!B5:B14,0),2),"Not Found")

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

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

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


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.


You May Also Like to Explore

VLOOKUP and Return All Matches in Excel (7 Ways)

Lookup Partial Text Match in Excel (5 Methods)

INDEX MATCH with 3 Criteria in Excel (4 Examples)

INDEX MATCH across Multiple Sheets in Excel (With Alternative)

Find Matches or Duplicate Values in Excel (8 Ways)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo