How to Compare Four Columns and Find Matching Records in Excel
Question details
The user needs to compare four specific fields in one dataset against rows in another dataset and return a MATCH only when all four column values are identical.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reconciling or cross-referencing complex datasets where an exact row match depends on four separate column criteria.
- Observed behavior
- Requires a formula to output "MATCH" for complete row matches and "NO MATCH" otherwise, without dealing with partial matches initially.
Ensure both datasets are formatted consistently (e.g., remove trailing spaces and standardize text/number formats) to prevent false mismatches during the comparison.
Use BYROW and LET Functions to Find Exact Matches
This advanced array formula checks each row in your comparison range against your target row and counts matching cells simultaneously.
The formula evaluates all four cells in a row at once. It scans the target dataset, and if the count of matching cells equals 4, it flags the row as a MATCH.
Keep in mind that identifying the exact field that differs is more complicated, as one row could have multiple partial matches across the right-side dataset.
Click on the cell where you want the first MATCH/NO MATCH result to appear (for example, F3).
Enter the following formula: =LET(m, BYROW($I$3:$L$7, LAMBDA(r, SUM(--(r=A3:D3)))), IF(MAX(m)=4, "MATCH", "NO MATCH"))
Change $I$3:$L$7 to the absolute range of your reference dataset, and update A3:D3 to represent the four columns in the row you are currently evaluating.
Press Enter to execute the formula, then click and drag the fill handle down to apply the comparison logic to the rest of your rows.
Use COUNTIFS for Simple Multi-Column Matching
If you are using an older version of Excel that does not support dynamic arrays or LAMBDA functions, use COUNTIFS.
Compare Complex Datasets Easily with WPS Spreadsheet
WPS Office fully supports advanced dynamic array functions like LET, BYROW, and LAMBDA, making complex multi-column comparisons completely effortless.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your datasets.
- 2. Enter the comparison formula: Click the target cell and paste the LET or COUNTIFS formula tailored to your data range.
- 3. Fill down: Press Enter, double-click the fill handle, and instantly apply the matching logic to all records.
Frequently Asked Questions
Why is my formula returning 'NO MATCH' when the data looks identical?
Hidden spaces, trailing spaces, or mismatched data types (such as numbers stored as text) can cause formulas to fail. Use the TRIM() function or ensure formats are identical before running the comparison.
Can I compare more than four columns using the BYROW method?
Yes, you can simply expand your array ranges (for example, A3:E3 instead of A3:D3) and change the condition from MAX(m)=4 to MAX(m)=5 to accommodate the fifth column.
What if I want to know exactly which column didn't match?
Identifying the exact mismatch requires a much more complex formula or the use of Power Query, because a single row might partially match multiple reference rows. Applying Conditional Formatting to highlight duplicate values is often an easier visual alternative to spot differences.
