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
To compare arrays and find the value that doesn't exist, you can use the `INDEX` and `MATCH` functions in Excel. Here's a formula you can use:
```excel
=INDEX(Array2[FourthColumn], MATCH(1, (Array1[Column1]=Array2[Column1])*(Array1[Column2]=Array2[Column2])*(Array1[Column3]=Array2[Column3]), 0))
```
Replace `Array1` and `Array2` with the actual ranges of your arrays, and `Column1`, `Column2`, `Column3`, and `FourthColumn` with the appropriate column headers.
This formula will compare each row of the first array with each row of the second array, and return the value from the fourth column of the first mismatched row found in the second array.
```excel
=INDEX(Array2[FourthColumn], MATCH(1, (Array1[Column1]=Array2[Column1])*(Array1[Column2]=Array2[Column2])*(Array1[Column3]=Array2[Column3]), 0))
```
Replace `Array1` and `Array2` with the actual ranges of your arrays, and `Column1`, `Column2`, `Column3`, and `FourthColumn` with the appropriate column headers.
This formula will compare each row of the first array with each row of the second array, and return the value from the fourth column of the first mismatched row found in the second array.
But it needs to work within the 2nd array. So like, cell C45. The formula checks an array for a value matching criteria and then puts the value in C45 but before doing so, checks the C column first to see if that value already exists in the column. How do I get around the circular reference error?