Excel Compare Two Arrays and Return the First Mismatched Row Value
Question details
Compare corresponding values in two three-column Excel arrays and return the value from a separate fourth column for the first row that contains any mismatch.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to check rows in A2:C4 against their corresponding rows in E2:G4 and retrieve the related value in H2:H4 for the first nonmatching row.
- Observed behavior
- The desired result is the value in column H from the earliest row where at least one paired cell in the two arrays is different.
Make sure both arrays have the same number of rows and columns, and align corresponding records on the same rows. Confirm that H2:H4 has one return value for each compared row.
Use INDEX, MATCH, and MMULT to return the first mismatched row value
Use this formula when each row must be compared across multiple columns and you want the value from column H for the first row with one or more differences.
MMULT totals the cell differences in each row. MATCH identifies the first row whose total is greater than zero, and INDEX returns the associated value from the return range.
Click the cell where you want the returned value to appear, such as J2.
Type =INDEX(H2:H4,MATCH(TRUE,MMULT(--(A2:C4<>E2:G4),TRANSPOSE(COLUMN(A2:C4)^0))>0,0)) and press Enter.
The formula returns the value from H2:H4 that is on the first row where A:C does not match the corresponding cells in E:G.
In older Excel versions that do not evaluate the formula normally, select the formula cell, enter the formula in the formula bar, and press Ctrl+Shift+Enter instead of Enter.
Display a custom message when the arrays match completely
Use this version when you want a clear result instead of an error if no row contains a mismatch.
Find the First Mismatched Array Row with WPS Spreadsheets
WPS Spreadsheets lets you compare corresponding ranges with INDEX, MATCH, and MMULT, then return the value linked to the first row that differs. Enter the formula directly in a worksheet and adjust the ranges to suit your data.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the workbook containing the two arrays and return column.
- 2. Choose an output cell: Click an empty cell where the first mismatched row result should be displayed.
- 3. Enter the mismatch formula: Enter =INDEX(H2:H4,MATCH(TRUE,MMULT(--(A2:C4<>E2:G4),TRANSPOSE(COLUMN(A2:C4)^0))>0,0)) in the formula bar.
- 4. Press Enter and check the returned value: Press Enter to return the H-column value associated with the first row containing a difference.
Frequently Asked Questions
What does the MMULT part of the formula do when comparing Excel arrays?
MMULT adds the TRUE/FALSE comparison results across each row after they are converted to 1 and 0 with --. A total greater than 0 means that row has at least one mismatched cell.
How can I return the row number of the first mismatch instead of a value from column H?
Use =MATCH(TRUE,MMULT(--(A2:C4<>E2:G4),TRANSPOSE(COLUMN(A2:C4)^0))>0,0). The result is the position within the compared ranges; add ROW(A2)-1 if you need the worksheet row number.
Why does the first-mismatch formula return #N/A?
#N/A usually means no mismatches were found, so MATCH could not locate TRUE. Wrap the formula in IFERROR and provide a message such as "No mismatches" if needed.
Can I compare more than three columns with this Excel formula?
Yes. Expand both comparison ranges by the same number of columns, such as A2:F100 and H2:M100. Keep the ranges identical in size and update the return range to contain the same number of rows.
Do older Excel versions require a special way to enter this formula?
Some older Excel releases may require Ctrl+Shift+Enter to confirm the formula as an array formula. In newer Excel versions, pressing Enter is normally sufficient.
