Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Find Similar Data in Two Excel Sheets

Cody Schneider••9 min read

Comparing two Excel sheets to find data that exists on both lists is a common but often frustrating task. Whether you're matching a list of event attendees against your customer database or comparing product inventories from different months, finding the overlap shouldn't take hours of manual scanning. This guide will walk you through four effective methods to find similar data in two Excel sheets, from quick visual highlights to more powerful techniques for large datasets.

GraphedGraphed

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.

Watch Graphed demo video

Method 1: Visually Highlight Matching Data with Conditional Formatting

If you just need a quick visual confirmation of which values in one list also appear in another, conditional formatting is your best friend. This method doesn't move or change your data, it simply applies formatting (like a cell color) to highlight the matches, making them easy to spot at a glance.

This approach works best for instant visual checks and on smaller data sets where you don't need to create a separate list of the matching values.

Step-by-Step Guide:

Let's imagine you have two sheets. Sheet1 has a list of email addresses from a recent webinar, and Sheet2 has your master list of all customer emails. You want to highlight the emails in Sheet1 that are also in your customer list in Sheet2.

  1. Select Your Data: In Sheet1, select the cells you want to check. For our example, let's say this is range A2:A100.
  2. Open Conditional Formatting: On the Home tab of the ribbon, click Conditional Formatting, then New Rule.
  3. Choose the Rule Type: In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format."
  4. Enter the Formula: Type the following formula into the formula box:

=COUNTIF(Sheet2!$A$2:$A$500, A2)>0

Let's break that down:

  • COUNTIF(...): This function counts how many times a certain value appears within a specified range.
  • Sheet2!$A$2:$A$500: This is the range on Sheet2 that contains your master customer list. The dollar signs ($) are important - they create an absolute reference, meaning this range won't change as Excel applies the rule to different cells in your selection on Sheet1.
  • A2: This is the relative reference to the first cell of your selection in Sheet1. As Excel checks each cell in your selected range (A2, A3, etc.), this reference will automatically update.
  • >0: The formula checks if the count is greater than zero. If an email from Sheet1 appears in Sheet2 at least once, the condition is true.
  1. Set the Format: Click the Format... button. Choose a fill color (like light green), a font color, or a border that will make the matches stand out. Click OK.
  2. Apply the Rule: Click OK again to close the rule dialog box. Instantly, all the email addresses in your Sheet1 list that also exist in Sheet2 will be highlighted in the color you chose.

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.

Method 2: Use VLOOKUP to Find and Fetch Matching Data

When you need more than just a highlight, VLOOKUP (Vertical Lookup) is a classic Excel function for the job. It not only confirms if a match exists but can also pull related data over from the second sheet. For finding similarities, we'll use it in a simpler way: just to see if a value from Sheet1 can be found in Sheet2.

Use VLOOKUP when you need to create a new column confirming the match or want a formula-based approach that returns a value (or an error) for each row.

Step-by-Step Guide:

Using our same webinar attendee list (Sheet1) and customer list (Sheet2), let's create a helper column in Sheet1 to flag the matches.

  1. Create a Helper Column: In Sheet1, add a new column header in cell B1, such as "Is Customer?".
  2. Enter the VLOOKUP Formula: In cell B2 (the first row of your new column), type the following formula:

=VLOOKUP(A2, Sheet2!A:A, 1, FALSE)

Here's what each part does:

  • A2: The value you want to look for (the email in the current row).
  • Sheet2!A:A: The lookup range. The formula will search for the value from A2 within the entire column A of Sheet2.
  • 1: The column index number. It tells Excel to return the value from the first column of the table_array if a match is found. In this case, it will just return the email address itself.
  • FALSE: This is extremely important! It tells VLOOKUP to look for an exact match. If omitted, the function might return incorrect results.
  1. Interpret the Results: Press Enter and drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to all your rows.
  • If a match is found, the cell will display the email address - confirming it exists in Sheet2.
  • If a match is not found, the cell will show the #N/A error.
  1. Clean it Up (Optional but Recommended): The #N/A errors aren't very elegant. You can wrap your formula in an IFNA function to show a cleaner message.

=IFNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE), "Not a Customer")

Now, instead of getting an error, the cell will simply say "Not a Customer." Much better!

GraphedGraphed

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.

Watch Graphed demo video

Method 3: A Simpler Approach with the COUNTIF Function

If VLOOKUP feels a bit complicated for what you need, the COUNTIF function offers a more straightforward alternative. It does one job perfectly: it counts how many times a value appears in a range. If the count is anything other than zero, you know you have a match.

This is often the quickest formula-based method if all you need is a simple "Yes/No" or "Match/No Match" answer in a helper column.

Step-by-Step Guide:

  1. Set Up Your Helper Column: Just like with VLOOKUP, add a new column in Sheet1 with a header like "Match Found?".
  2. Enter the COUNTIF Formula: In the first cell of that new column, type:

=COUNTIF(Sheet2!A:A, A2)

This formula checks the entire column A in Sheet2 (Sheet2!A:A) and counts how many times it finds the value from cell A2 of your current sheet.

  1. Review the Output: Drag the formula down. The column will fill with numbers.
  • A 0 means the email from that row was not found in Sheet2.
  • A 1 or higher means the email was found.
  1. Make it More Readable with IF: Instead of raw numbers, you can nest the COUNTIF inside an IF statement for a clearer message.

=IF(COUNTIF(Sheet2!A:A, A2) > 0, "Yes", "No")

This formula now reads like plain English: "If the count of the email from A2 in Sheet2's list is greater than zero, display 'Yes', otherwise display 'No'."

Method 4: Supercharge Your Comparison with Power Query

For larger datasets, frequent comparisons, or more complex matching needs, Excel's formulas can become slow and cumbersome. This is where Power Query shines. Power Query (also known as Get & Transform Data) is a data transformation engine built into Excel that is designed to handle big data and automate repetitive tasks.

Use Power Query when you're working with thousands of rows, you need to perform this comparison regularly, or you want to generate a new, clean table containing only the matching records without messing up your original data.

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.

Step-by-Step Guide:

  1. Format Data as Tables: First, convert both of your data ranges into official Excel tables. Select your data in Sheet1 and press Ctrl+T. Make sure "My table has headers" is checked and click OK. Give the table a descriptive name (e.g., "WebinarList") in the "Table Design" tab. Repeat this process for the data in Sheet2 (e.g., name it "CustomerList").
  2. Load "WebinarList" into Power Query: Select any cell in your "WebinarList" table. Go to the Data tab and click From Table/Range. The Power Query Editor will open.
  3. Load "CustomerList" into Power Query: Follow the same process to load your "CustomerList" table into Power Query as well. You will now have two queries, one for each table.
  4. Merge the Queries: In the Power Query Editor, go to the Home tab, click the drop-down for Merge Queries, and select Merge Queries as New.
  5. Configure the Merge: A dialog box will appear.
  • In the top dropdown, select your WebinarList query.
  • In the bottom dropdown, select your CustomerList query.
  • Click on the header of the column you want to match on in both tables (e.g., the "Email" column). The columns will highlight.
  • In the "Join Kind" dropdown, select Inner (only matching rows). This tells Power Query to only keep the rows that exist in both tables.
  • Click OK.
  1. Expand the Results: Power Query creates a new table with a column containing the results from your second table. Click the two-arrow icon on the header of this new column, uncheck "(Use original column name as prefix)", and click OK.
  2. Load the Final Table: You now have a clean table showing only the attendees who are also customers. On the Home tab, click Close & Load. Power Query will load this new combined data into a brand new worksheet in your workbook.

The best part? This process is repeatable. If your source tables update, just go to your new results table, right-click, and select Refresh to get the latest matches automatically.

Which Method Should You Choose?

With several options available, here's a quick guide to picking the right one:

  • For a quick visual overview: Use Conditional Formatting to highlight matches directly in your sheet without changing anything.
  • For a simple check in a helper column: Use COUNTIF with an IF statement to get a clean "Yes" or "No".
  • To find matches and grab related data: The VLOOKUP function is your go-to tool.
  • For large datasets and automated, repeatable workflows: Power Query is by far the most powerful and efficient choice.

Final Thoughts

Finding matching data between two sheets in Excel doesn't have to involve endless scrolling or complex array formulas. By using built-in tools like Conditional Formatting, VLOOKUP, COUNTIF, or the powerful Power Query engine, you can quickly find the insights you need and make sure your data is telling a consistent story.

As helpful as these methods are, the manual work of exporting data from various sources into spreadsheets just to compare them is still a huge time sink. Many teams find themselves constantly pulling reports from tools like Shopify, Google Analytics, HubSpot, and their ad platforms into Excel or Google Sheets. This is where we built Graphed to help. We connect directly to all your data sources, allowing you to instantly build real-time reports and dashboards just by asking questions in plain English - no more exporting CSVs or wrestling with VLOOKUP formulas late at night.

Related Articles