Function Problems

How to Compare Two Columns in Separate Excel Tables Using Conditional Formatting

Steve K Oct 22, 2026 868 views

Question details

The user needs to highlight rows in an Excel table when two specific columns match the records in another table.

Product: Excel
Device & OS: not provided
Scenario: Comparing multi-column data across two separate tables to find and highlight exact matches across multiple criteria.
Observed behavior: Matching rows need to be automatically highlighted using conditional formatting, regardless of whether the table rows are in a different order.

Before you start

Ensure both tables have consistent data formats (e.g., avoiding mixing text and numbers) in the columns you want to compare to prevent the matching formula from returning unexpected errors.

Solution 1Recommended

Use an Array Formula in Conditional Formatting

Apply a custom MATCH formula within the conditional formatting rules to evaluate multiple criteria simultaneously across different sheets.

This method uses the MATCH function combined with ISNUMBER to evaluate multiple criteria at once by multiplying the conditions. It dynamically checks for matches across sheets and applies the highlighting even if the rows in the two tables are not arranged in the same order.

Select the target range

Highlight the data rows in your current table where you want the conditional formatting to be applied.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the matching formula

Input the formula: =ISNUMBER(MATCH(1,(Sheet2!$C$6:$C$100=$C6)*(Sheet2!$F$6:$F$100=$F6),0)). Adjust 'Sheet2' and the cell references to match your actual column data.

Apply formatting style

Click the Format button, choose your desired highlight color under the Fill tab, and click OK twice to apply the rule.

Use an Array Formula in Conditional Formatting

Pay Attention to Cell References: Ensure that the ranges for the reference table are absolute (using $ like $C$6:$C$100), while the row numbers for the active sheet are relative (like $C6) so the rule adjusts for each row.

Highlight Matching Data Easily

Use WPS Spreadsheet to Compare and Highlight Table Data

WPS Spreadsheet provides robust support for advanced conditional formatting and array formulas, allowing you to compare multi-column data across different sheets effortlessly and accurately.

1. Open your workbook: Launch WPS Office and open the spreadsheet containing your two tables.
2. Select the data area: Highlight the rows in the primary table where you want the conditional formatting to appear.
3. Access Conditional Formatting: Go to the Home tab, click Conditional Formatting > New Rule, and select the formula option.
4. Apply the rule: Input your MATCH formula, configure the fill color, and save the rule to highlight the matching records.

100% compatible with Microsoft Excel conditional formatting rules and complex array formulas.Lightweight software that processes data comparisons quickly without lagging.Clean and intuitive user interface for managing and editing your conditional formatting rules.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula not highlighting any matching rows?

This usually happens if the data types in the compared columns don't match exactly (e.g., a number is stored as text in one table and as a true number in the other). Check for trailing spaces or use the formatting tools to ensure both columns share the same data type.

Can I use VLOOKUP inside conditional formatting to compare columns?

While VLOOKUP can be used in conditional formatting, it typically only checks one criterion at a time. To check two columns simultaneously (like Tender Number and Job Value), using the MATCH function with multiplied array criteria or COUNTIFS is much more effective.

How do I clear or edit the conditional formatting rules if I make a mistake?

Go to the Home tab, click Conditional Formatting, and select Manage Rules. From there, you can edit the formula, change the formatting style, or delete the rule entirely if it is no longer needed.

Excel & Data Problems