Function Problems

How to Compare Two Columns in Excel and Find Mismatched Values

Adam Davis Oct 10, 2026 869 views

Question details

The user needs to compare reference numbers and their corresponding values across two Excel tables to find mismatched data and identify missing references.

How to Compare Two Columns in Excel and Find Mismatched Values

Product: Excel
Device & OS: not provided
Scenario: Comparing two datasets to reconcile data, identify discrepancies between paired values, and locate records that are missing from either table.
Observed behavior: The user wants to output specific status labels such as 'Value does not match' or 'Reference missing' based on the column comparison results.

Before you start

Ensure that your reference numbers in both tables are formatted consistently (e.g., both formatted as Text or both as Numbers) to avoid false mismatch errors during the comparison.

Solution 1Recommended

Use VLOOKUP and IF Formulas to Compare Column Pairs

The fastest way to compare two column pairs and automatically flag mismatched or missing values using combined Excel functions.

By nesting VLOOKUP inside an IF function, you can check if the value from the second table matches the first table. Adding IFERROR helps handle situations where the reference number does not exist in the second table.

Select a blank cell

Click on a blank cell (e.g., C2) directly next to the first data pair in your first table.

Enter the nested formula

Input the formula: =IFERROR(IF(VLOOKUP(A2,D:E,2,0)<>B2,"Value does not match",""),"Reference missing"). In this formula, A2 is the reference number, D:E is the second table's range, and B2 is the value to compare against.

Apply to the entire column

Press Enter to see the result. Click the cell again, grab the fill handle in the bottom-right corner, and drag it down to apply the formula to all rows.

Use VLOOKUP and IF Formulas to Compare Column Pairs

Formula Explanation: VLOOKUP retrieves the paired value. The IF statement compares it to your original value, outputting 'Value does not match' if they differ. IFERROR catches missing references and outputs 'Reference missing'.

Solution 2

Use Power Query to Merge and Compare Tables

Ideal for large datasets in Excel 365 to visually identify unique and mismatched references without writing complex formulas.

Work Smarter with WPS Office

Easily Compare and Reconcile Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities, fully supporting VLOOKUP, XLOOKUP, and nested IF functions to quickly compare columns, identify mismatches, and reconcile massive datasets.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the two tables you want to compare.
2. Input the comparison formula: Click on a blank cell adjacent to your first table and type =IFERROR(IF(VLOOKUP(A2,D:E,2,0)<>B2,"Mismatch",""),"Missing").
3. Auto-fill the column: Press Enter, then double-click the small square at the bottom right of the cell to instantly apply the comparison logic to thousands of rows.

Fully compatible with Microsoft Excel formulas and the .xlsx file format.Supports advanced conditional formatting to visually highlight mismatches.Lightweight, fast, and completely free to use for your daily data analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning a mismatch when the values look identical?

This usually happens due to hidden trailing spaces or mismatched data types (e.g., numbers stored as text). Use the TRIM function to remove extra spaces, or ensure both columns share the exact same number format.

Can I highlight the mismatched cells instead of using a formula output text?

Yes. You can select your value column, go to Home > Conditional Formatting > New Rule, and use a formula like =$B2<>VLOOKUP($A2,$D:$E,2,FALSE) to automatically apply a red background color to any mismatched values.

Is there a quick way to compare two columns in the same row without VLOOKUP?

If you are strictly comparing two columns row-by-row (e.g., Column A and Column B), you can simply use the exact formula =A2=B2, which will return TRUE if they match and FALSE if they do not. Alternatively, select both columns, press Ctrl+G, click Special, select 'Row differences', and click OK to highlight discrepancies.

Excel & Data Problems