SOLVED

# Compare arrays, find value that doesn't exist.

Brass Contributor

# 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 2nd array, find the first result that DOESN'T match an return the 4th value of that row from the 2nd array. (if that makes any sense).

Any help would be VERY appreciated.

30 Replies

# Re: Compare arrays, find value that doesn't exist.

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).

# Re: Compare arrays, find value that doesn't exist.

I tried it out in my workbook and it came back with #VALUE!

# Re: Compare arrays, find value that doesn't exist.

it's an array formula, so rather than pressing Enter, you should Ctrl+Shift+Enter to input it.

# Re: Compare arrays, find value that doesn't exist.

Yes, I did that.
best response confirmed by Immortalis (Brass Contributor)
Solution

# Re: 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

# Re: Compare arrays, find value that doesn't exist.

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?

# Re: Compare arrays, find value that doesn't exist.

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!

# Re: Compare arrays, find value that doesn't exist.

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!

# Re: Compare arrays, find value that doesn't exist.

@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.

# Re: Compare arrays, find value that doesn't exist.

IT sounds good... t returned #N/A though.

# Re: Compare arrays, find value that doesn't exist.

_Sectioncalc is a defined name so shouldn't have quotes. Still returns #N/A however.

# Re: Compare arrays, find value that doesn't exist.

i thought that was a text value, could you be more specific, what's the range of your _Sectioncalc

# Re: Compare arrays, find value that doesn't exist.

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.

# Re: Compare arrays, find value that doesn't exist.

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.

# Re: Compare arrays, find value that doesn't exist.

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.

# Re: Compare arrays, find value that doesn't exist.

do you mind sharing your sample file here?

# Re: Compare arrays, find value that doesn't exist.

@ere's my sample book.

# Re: Compare arrays, find value that doesn't exist.

So apparently...

``INDEX(\$U\$15:\$U\$114,MATCH(\$Y\$3,\$U\$15:\$U\$114,0))=Y3``

Works 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.

# Re: Compare arrays, find value that doesn't exist.

@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

1 best response

Accepted Solutions
best response confirmed by Immortalis (Brass Contributor)
Solution

# Re: 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