How to find rows that appear in both datasets

The fastest way to find matching records between two Excel lists is to use a VLOOKUP or INDEX/MATCH formula that checks whether each row in one list exists in the other. You add a helper column to one dataset, write a formula that searches the second dataset, and then filter to show only the matches.

If your lists are small (under 1,000 rows each), VLOOKUP works fine. If they are larger or your data is messy, INDEX/MATCH is more reliable because it finds exact matches without requiring your data to be sorted in a particular way.

The result is a new column that marks which rows match, so you can sort or filter to see only the records that appear in both places. This is useful when you are reconciling two versions of a customer list, checking which invoices were paid, or verifying that records from one system made it into another.

Key Takeaways

  • A VLOOKUP or INDEX/MATCH formula in a helper column can check whether each row in one list appears in the other list.
  • VLOOKUP requires the lookup column to be the leftmost column in your data range, while INDEX/MATCH works with any column.
  • Wrapping your formula in IFERROR() prevents error messages when a row does not match, making it easier to spot the mismatches.
  • After your formula runs, you can filter or sort by the helper column to see only matching rows, or copy them to a new sheet.

Using VLOOKUP to find matches

VLOOKUP searches for a value from one list inside another list and returns a result if it finds a match. To find the intersection, you use VLOOKUP to check whether each ID or key field in List A exists anywhere in List B.

Open your spreadsheet with both datasets. Put List A on one sheet and List B on another sheet, or arrange them side by side. In a blank column next to List A (say, column C), click the first data cell and type this formula:

=IFERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE),"No Match")

Replace A2 with the cell containing your first key value (the ID, email, or name you are matching on). Replace Sheet2!A:A with the range in List B that contains the same type of data. The FALSE at the end means "find an exact match only." The IFERROR wrapper returns "No Match" instead of an error if the value is not found.

Press Enter. If the value in A2 exists in List B, the cell shows the matched value. If it does not exist, it shows "No Match." Copy this formula down to every row in List A. Then filter column C to show only rows that do not say "No Match"—those are your intersecting records.

Using INDEX/MATCH for more control

INDEX/MATCH is more flexible than VLOOKUP because it can search any column, not just the leftmost one. It also works better when your data has extra spaces, mixed uppercase and lowercase, or other small inconsistencies.

In a blank column next to List A, type this formula:

=IFERROR(INDEX(Sheet2!A:A,MATCH(A2,Sheet2!A:A,0)),"No Match")

The MATCH part searches for the value in A2 within List B and returns its row number. The INDEX part then retrieves the value from that row. The 0 in MATCH means "exact match only." Like VLOOKUP, wrap it in IFERROR to show "No Match" instead of an error.

Copy the formula down to every row. The results are identical to VLOOKUP, but INDEX/MATCH is easier to adjust if you need to search a different column or return a different piece of information.

Filtering to see only the matching rows

Once your formula column is complete, you have marked every row as either a match or a non-match. To see only the rows that appear in both datasets, use Excel's AutoFilter feature.

Click any cell in your data range. Go to the Data menu and click AutoFilter. A dropdown arrow appears in the header row of each column. Click the dropdown arrow in your helper column (the one with "Match" or "No Match"). Uncheck "No Match" so only the matching rows show. Excel hides the non-matching rows without deleting them.

You can now copy the visible matching rows to a new sheet, or leave the filter in place while you work with them. If you want to see all rows again, click the dropdown arrow and check "No Match" again.

Handling messy data and special cases

Real data often has extra spaces, different capitalization, or slight spelling differences that prevent formulas from matching. Before you write your formula, clean up both lists using Excel's TRIM function to remove extra spaces and UPPER or LOWER to standardize capitalization.

In a temporary column, type =TRIM(UPPER(A2)) and copy it down. This creates a cleaned version of your key field. Then use that cleaned column in your VLOOKUP or INDEX/MATCH formula instead of the original. After you finish finding matches, you can delete the temporary columns.

If your lists use different formats for the same data—such as one list showing "John Smith" and the other showing "Smith, John"—you will need to reformat one list to match the other before the formula can find matches. This usually means using formulas to rearrange the text, or manually editing one list.

Copying matched rows to a new sheet

After filtering to show only matches, you may want to copy them to a separate sheet for reporting or further work. Select all the visible (filtered) rows, including the header. Press Ctrl+C to copy. Create a new sheet, click the first cell, and press Ctrl+V to paste. Excel pastes only the visible rows, not the hidden ones.

If you want to remove the helper column from your copied data, select that column, right-click, and choose Delete. The matched data is now in a clean sheet with no formula column.

You can also use this filtered view to create a pivot table, a chart, or a summary report. The filter stays active, so any analysis you do includes only the matching records.

Finding records in one list but not the other

Sometimes you need the opposite: records that appear in List A but not in List B. Use the same formula approach, but this time you are looking for the "No Match" results instead of filtering them out.

After your formula column is complete, filter to show only "No Match" rows. These are the records in List A that have no match in List B. This is useful for finding missing invoices, customers who were deleted, or records that failed to sync between systems.

You can also run the same process in reverse: put List B as your main list and check which rows do not match List A. This shows you records in List B that are not in List A, which catches additions or duplicates in the second dataset.

Frequently Asked Questions

What if my lists have different column names or are organized differently?

Column names do not matter for the formula—only the data in the cells. Make sure you are matching on the same type of information (ID to ID, email to email). If the key field is in column A in one list and column C in the other, adjust the formula to reference the correct column.

Can I find the intersection without adding a helper column?

Not easily in standard Excel. The helper column approach is the most straightforward. However, if you have Excel 365, you can use the FILTER function to show only rows where a COUNTIF formula finds a match in the other list, all in one formula without a helper column.

Why does my formula show #N/A or #REF! errors?

An #N/A error means the VLOOKUP or MATCH did not find the value—this is normal and expected for non-matching rows. Wrap your formula in IFERROR to replace the error with readable text like "No Match." A #REF! error means your formula references a sheet or range that does not exist; check your sheet names and range addresses.

How do I handle duplicate values in my lists?

VLOOKUP and INDEX/MATCH find the first match only. If List B has the same ID twice, the formula still returns a match for that ID in List A, but it does not tell you about the duplicate. If duplicates matter, you will need to identify and clean them separately before finding the intersection.

What is the fastest way to find intersections in very large datasets?

For lists with tens of thousands of rows, formulas can slow down your spreadsheet. Consider using Excel's built-in data tools like removing duplicates or using the Data > Subtotals feature. For very large datasets, a database program or a dedicated data tool may be faster and more reliable than Excel formulas.