Forum Discussion
Solved
Compare arrays, find value that doesn't exist.
I have an array with 3 columns and another array with 4 columns. I'm trying to find a formula that will compare the 3 values in a row in the 1st array, compare it to three values in a row in the ...
Immortalis
it worked for me. I've just slightly revised the formula.=INDEX(H2:H4, MATCH(TRUE, MMULT(--(A2:C4<>E2:G4), TRANSPOSE(COLUMN(A2:C4)^0))>0, 0))note: this formula will only return the first mismatched from your arrays. and if you have multiple mismatches, it will need a SMALL function with ROW function as the second argument to the Nth smallest value from your arrays row numbers where there is a mismatch.
formula if multiple mismatches:=IFERROR(INDEX($H$2:$H$4, SMALL(IF(MMULT(--($A$2:$C$4<>$E$2:$G$4), TRANSPOSE(COLUMN($A$2:$C$4)^0))>0, ROW($A$2:$A$4)-ROW($A$2)+1), ROW(A1))), "")after pasting that formula (with Ctrl+Shift+Enter) example in cell B9, then copy that cell and paste it in cell B10 and B11
Let's say your first array is in the range A2:C4 and the second array is in the range E2:H4.
In the cell where you want to get the result, enter the following array formula:
=INDEX(H2:H4, MATCH(0, MMULT(--(A2:C4<>E2:G4), TRANSPOSE(COLUMN(A2:C4)^0)), 0))This formula should works by comparing each row in the first array with the corresponding row in the second array. The MMULT() is used to sum the results of the comparison for each row. then MATCH() is used to find the first row where the sum is not zero (indicating a mismatch).
I tried it out in my workbook and it came back with #VALUE!
- it's an array formula, so rather than pressing Enter, you should Ctrl+Shift+Enter to input it.
- Yes, I did that.
Immortalis
it worked for me. I've just slightly revised the formula.=INDEX(H2:H4, MATCH(TRUE, MMULT(--(A2:C4<>E2:G4), TRANSPOSE(COLUMN(A2:C4)^0))>0, 0))note: this formula will only return the first mismatched from your arrays. and if you have multiple mismatches, it will need a SMALL function with ROW function as the second argument to the Nth smallest value from your arrays row numbers where there is a mismatch.
formula if multiple mismatches:=IFERROR(INDEX($H$2:$H$4, SMALL(IF(MMULT(--($A$2:$C$4<>$E$2:$G$4), TRANSPOSE(COLUMN($A$2:$C$4)^0))>0, ROW($A$2:$A$4)-ROW($A$2)+1), ROW(A1))), "")after pasting that formula (with Ctrl+Shift+Enter) example in cell B9, then copy that cell and paste it in cell B10 and B11