How to Find Matching Data in Two Excel Sheets

Nothing stalls a project faster than trying to figure out which pieces of data match across two different Excel sheets. Whether you're comparing customer lists, monthly sales reports, or product inventories, this task can feel like a painstaking manual process. This guide will walk you through several effective methods to find and match data in two Excel sheets, from simple formulas to more advanced tools built right into the program.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Choose Your Goal: Highlight Matches or Pull Matching Data?

Before you start, it's helpful to know what you want your end result to be. Generally, you're trying to do one of two things:

• Highlight Matching Cells: You simply want a visual way to see which values from Sheet1 also exist in Sheet2. You don’t need to pull any additional information over, just a quick color-coded confirmation is enough. For this, Conditional Formatting is your best friend.
• Pull Related Data: You want to confirm a match and pull corresponding data from the second sheet on the same row. For example, if you find a matching OrderID in Sheet A and Sheet B, you might want to pull the ShippingStatus from Sheet B into Sheet A. For this, formulas like VLOOKUP, XLOOKUP, or INDEX/MATCH are perfect.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: Quickly Highlight a Dataset Using Conditional Formatting

If you're only looking for a visual indication within the active spreadsheet, Conditional Formatting is the fastest way to get it done. It uses a formula to check for a value's existence in another list and then applies a specific format (e.g., a green background) to cells that match. Let’s say you have a list of new signups (Sheet1) and a list of all current customers (Sheet2).

Step-by-Step Instructions:

1. Select Your Unique ID List in Sheet 1: Start by highlighting the column in your first sheet you want to check for matches. Let's assume you're looking for Account IDs in Sheet A.
2. Apply Your Conditional Formatting Rule: With your Account IDs column highlighted, go to the "Home" tab on the main navigation menu of Excel. Find "Conditional Formatting > New Rule".
3. A popup titled "New Formatting Rule" will appear, which lists different potential rules that can be created. Choose "Use a formula to determine which cells to format".
4. Write the Formula: In the formula input bar, enter the following code snippet, ensuring your spreadsheet information matches the example for it to work correctly.

=COUNTIF(Sheet_A!$A:$A) > 0

Breaking Down the Formula:

• The $A:$A part specifies that the column A in Sheet_A is being checked against the reference list. The COUNTIF function will return a count greater than 0 if it finds a match.
• The function checks if the list contains any matching entries in the specified column.

1. Set the Format of Sheet1: Click the ‘OK’ button. Set your desired formatting to highlight the matching cells, such as applying a green background color for matches.

Method 2: Match Data Between Two Sheets Using VLOOKUP

For those not as experienced, the VLOOKUP function is popular in Excel for data analytics. VLOOKUP allows you to search for a value in a column and return a corresponding value from another column within the same row.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

VLOOKUP Step-by-Step Instructions:

• Lookup Value: Define the unique ID or key, ensuring you can easily identify the data in your worksheet.
• Table Array: This refers to the data range in the second sheet where the VLOOKUP function searches for the match.
• Column Index Number: Specify the column number from which the matching data should be retrieved.
• Range Lookup: Use FALSE to find an exact match.

=VLOOKUP(C2, 'Sheet2'!A:B, 2, FALSE)

The above formula searches for the value in cell C2 in the second sheet and returns the match from the second column within the defined range.

Method 3: Using INDEX and MATCH

For more advanced users, pairing the INDEX and MATCH functions offers a flexible approach to data retrieval. Unlike VLOOKUP, INDEX and MATCH work well on data not situated in the leftmost column.

INDEX and MATCH Example:

=INDEX('Sheet2'!B:B, MATCH(C2, 'Sheet2'!A:A, 0))

The MATCH function locates the position of the lookup value, and the INDEX function retrieves the data from the specified position within another column.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 4: Advanced Use with Power Query

Power Query is an advanced tool within Excel for those dealing with large data sets or needing to merge data across different spreadsheets. It allows for data transformation and consolidation without complex formulas.

Final Thoughts

Excel provides a variety of tools for matching data between sheets. Conditional Formatting offers quick visual checks, while VLOOKUP and INDEX/MATCH serve for more detailed data retrieval. For large datasets, Power Query is invaluable for simplifying data management.

Analysts and marketers often compare sheets as their data lies across various platforms. By utilizing Excel’s powerful tools, or opting for a solution like Graphed, users can effectively manage and streamline data processes.