Forum Discussion
Compare arrays, find value that doesn't exist.
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 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!
Immortalis
you can add an additional condition to the formula to check if the cell in the “U” column is equal to _SectionCalc
updated formula:
=INDEX(AQ137:AQ164, MATCH(TRUE,
(MMULT(--(BaseInfo!$U$15:BaseInfo!$W$114<>AO137:AQ137),
TRANSPOSE(COLUMN(BaseInfo!$U$15:BaseInfo!$W$114)^0))>0)*
(BaseInfo!$U$15:BaseInfo!$U$114="_SectionCalc"), 0))(BaseInfo!$U$15:BaseInfo!$U$114="_SectionCalc") checks if the cell in the “U” column is equal to _SectionCalc. The * operator acts as a logical AND, so both conditions must be true for a row to be considered a match.
As always Ctrl+Shift+Enter after typing the formula.
- It just returns a blank.
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
So apparently...
INDEX($U$15:$U$114,MATCH($Y$3,$U$15:$U$114,0))=Y3Works just fine on it's own, but when I incorporate it into your formula it returns #N/A.
Is there anything you can do to solve that?
Thank you.
@ere's my sample book.
- do you mind sharing your sample file here?
- The dates are the same. I even changed the formula to
BaseInfo!$U$15:BaseInfo!$U$114=AY135
And copied the date from BaseInfo!$U column to AY135 and it still says false. have you checked it's format? they must be the same. in order to make it work.
there might be an issue with how the dates are being compared. Excel stores dates as numbers, so if the format in _SectionCalc is different from the format of the dates in BaseInfo!$U$15:BaseInfo!$U$114, they might not match even if they represent the same date.- The problem seems to be
BaseInfo!$U$15:BaseInfo!$U$114=_SectionCalc
This returns FALSE when I've verified it is true.
_Sectioncalc is a date. The date changes from week to week but in any week, _Sectioncalc is the date of the friday in that week. i thought that was a text value, could you be more specific, what's the range of your _Sectioncalc
- _Sectioncalc is a defined name so shouldn't have quotes. Still returns #N/A however.
- IT sounds good... t returned #N/A though.