Find Matching Values in Two Excel Lists: 3 Key Ways

Learning how to find matching values in two Excel lists is an essential skill for daily administrative, financial, or logistics operations. Whether reconciling client databases, cross-referencing inventory counts, or verifying outgoing payments against issued invoices, manually matching records wastes valuable time and introduces a high risk of human error.

If you work with large volumes of messy records, we recommend reviewing our guide to first and last names into columns in Excel to standardize your cells before matching, or checking out how to how to find duplicates and remove them in spreadsheets to clean up redundant rows.

Below, we break down 3 proven methods to cross-reference lists and retrieve matching data with complete precision.

1. Cross-reference data using the modern XLOOKUP function

In modern versions such as Microsoft 365 and Office 2021, the XLOOKUP function is the ultimate standard, comfortably replacing the classic VLOOKUP since it does not require lookup columns to be placed on the left side.

1. Click on an empty column next to your primary list (for instance, cell C2).
2. Type the following formula, adjusting the cell ranges as needed:
   =XLOOKUP(A2, D$2:D$100, D$2:D$100, "No match")
3. In this formula, A2 is the single value you want to check, D$2:D$100 represents the target comparison range, and "No match" is the custom fallback message if no coincidence exists.
4. Drag down the lower right corner of the cell (fill handle) to apply the calculation across all remaining rows.

To explore advanced parameters regarding exact matching and wildcards, consult the official Microsoft Support documentation on the XLOOKUP function.

2. Highlight matching entries with visual Conditional Formatting

If you do not need to populate values into a new helper column and simply want Excel to dynamically highlight cells shared across both columns with a background color, conditional formatting is the quickest route:

1. Select both target lists simultaneously while holding down the Ctrl key (for example, ranges A2:A50 and B2:B50).
2. On the top ribbon, navigate to Home > Conditional Formatting.
3. Go to Highlight Cells Rules > Duplicate Values...
4. In the dialog box, ensure the dropdown is set to "Duplicate" and choose a soft green or yellow fill.
5. Click OKAny cells existing in both lists will instantly be colored.

This visual method pairs seamlessly with our technical tutorial on how to compare two columns in Excel across different sheets. using rule-based formatting.

3. Use VLOOKUP for backward compatibility with legacy versions

When you need to find matching values in two Excel lists within workbooks shared across older versions (such as Excel 2019, 2016, or 2010), combining VLOOKUP with IFERROR ensures complete compatibility:

1. Place your cursor in the adjacent helper cell next to your source record.
2. Enter the formula:
   =IFERROR(VLOOKUP(A2, $E$2:$E$100, 1, FALSE), "No match")
3. The parameter 1 returns the exact found value, while the argument FALSE enforces an exact match without approximations.
4. If the item exists in both lists, the matching value is displayed; if not, a clean label appears instead of the #N/A formula error.

Best practices when cross-referencing spreadsheet data

When attempting to find matching values in two Excel lists, matching mismatches often stem from minor text formatting discrepancies:

• Trailing and leading spaces: An unseen whitespace character before or after a text entry prevents an exact match. Clean your dataset beforehand using =TRIM(A2) .
• Number stored as text vs. true numeric: If one column stores ID numbers as pure numeric values while the other formats them as text strings, Excel will treat them as unequal even if they look identical.
• PDF table extractions: If your source tables originated from scanned digital statements or invoices, review first how how to convert a protected PDF to Word for free to extract clean tabular data without corrupted values.

Mastering formulas to find matching values in two Excel lists eliminates tedious manual checks and guarantees data integrity across your business reports.

Related productivity guides

• Need to email your completed workbooks as attachments? Learn how to reduce PDF file size without losing quality to stay within email file limits.
• If you need to combine annexes, invoices, and exported balance sheets into one tidy document, check out how to how to merge PDF files for free without software.