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
@ere's my sample book.
Immortalis
try this:
=IFERROR(INDEX(Baseinfo!$X$15:$X$38, SMALL(IF((MMULT(--($AO$137:$AQ$137<>Baseinfo!$U$15:Baseinfo!$W$38), TRANSPOSE(COLUMN($AO$137:$AQ$137)^0))>0)*(Baseinfo!$U$15:Baseinfo!$U$38=_SectionCalc), ROW(Baseinfo!$U$15:Baseinfo!$U$38)-ROW(Baseinfo!$U$15)+1), ROW(A1))), "")I attached your sample file with the formula
- It just returns a blank.