Learn how to compare multiple columns in Excel to find and extract row matches using a powerful formula based on FILTER and XMATCH.
This tutorial is a part of our definitive guide on Excel formulas.
How to compare multiple columns in Excel?
Here are the steps to compare multiple columns and extract matching rows in Excel:
- Open Excel.
- Type the formula: =FILTER(range1, ISNUMBER(XMATCH(range1&range2&range3, range4&range5&range6, 0)))
- Press Enter.
- The formula will return the matching rows based on all three columns.
Example
In this example, we have a dataset in range B3:D10 containing product names, sales representatives, and bonus classifications. The goal is to filter and extract rows where the product and sales representative match specific criteria listed in columns F and G.
The formula in J3 not only uses the FILTER function but also combines it with XMATCH to check whether a combination of product and sales representative exists in the defined criteria list. As a result, the formula dynamically filters the table, then displaying only the relevant rows that fully match the given conditions.
Explanation
Evaluate the formula from the inside out:
B3:B10&C3:C10&D3:D10
This part concatenates (joins) values from columns B, C, and D into a single string per row. For example, if row 4 has B4 = Lemon, C4 = William, D4 = C, the result for row 4 will be “LemonWilliamC”. This ensures each row has a unique identifier based on all three columns.
F3:F10&G3:G10&H3:H10
Similarly, this concatenates values from columns F, G, and H (another dataset). Each row in this dataset is now represented as a unique string.
XMATCH(value, array, 0)
This part searches for exact matches of each concatenated row from B:D within F:H. If a match is found, XMATCH returns a position number (e.g., 1, 2, 3…). If a match is not found, it returns #N/A.
ISNUMBER(XMATCH(…))
The formula converts the position numbers into TRUE/FALSE values: the result is TRUE if the row exists in both datasets, else FALSE.
FILTER(B3:D10, ISNUMBER(…)
The FILTER function extracts only the rows where ISNUMBER is TRUE.
Conclusion
Tired of basic “compare two columns in Excel” methods? This tutorial not only goes beyond simple column comparisons but also walks you through how to compare multiple columns at once using XMATCH, FILTER, and ISNUMBER. Rather than just checking whether values exist in a single column, this method ensures that entire rows match precisely, thereby reducing false positives while also preventing overlooked differences.