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
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
I had to make some minor changes but YES! That works!!
Thank you SOOOOOO much! You're truly amazing!!! 🙂
- I'm glad that we finished this. 😄
no problem mate.I've run into another issue with the formula.
IFERROR(INDEX(BaseInfo!$W$15:$W$38, SMALL(IF((MMULT(--(AO170:AQ170<>BaseInfo!$U$15:BaseInfo!$W$38),TRANSPOSE(COLUMN(AO170:AQ170)^0))>0)*(BaseInfo!$U$15:BaseInfo!$U$38=_SectionCalc), ROW(BaseInfo!$U$15:BaseInfo!$U$38)-ROW(BaseInfo!$U$15)+1), ROW(A1))),"")It works perfectly fine except when I actually go to put it in my workbook, I'm putting it in cell AQ170 and it returns a circular reference error.
For the AQ column I'm looking up data that matches the criteria and ten putting it in the AQ column after checking the column to make sure it hasn't already been put in. So it's not there then goes to put it in but then it is there so it can't use it and .... paradox. Is there any way to get around that?
- Still doesn't work for me. In my workbook it comes up with #VALUE!.
In another workbook I'm using to test things, it comes up #N/A.
Could it be because I'm using 2010?- I recreated your example and it worked so somethings going wrong converting to use in my workbook. I'll play with and see what I do. Thank you very much though! It gives me a valid starting point!
I converted it over and managed to get is to work,
INDEX(AQ137:AQ164, MATCH(TRUE, MMULT(--(BaseInfo!$U$15:BaseInfo!$W$114<>AO137:AQ137), TRANSPOSE(COLUMN(BaseInfo!$U$15:BaseInfo!$W$114)^0))>0, 0))Thank you VERY much! Would you be able to add something to it though? When it finds the line that doesn't exist, I need it to check that line and make sure the cell in the "U" column is equal to _SectionCalc.
If you could do that, then my issue is completely solved!