Forum Discussion

Immortalis's avatar
Immortalis
Brass Contributor
Feb 05, 2024
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 ...
excel
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Feb 05, 2024

    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

Immortalis's avatar
Immortalis
Brass Contributor
Feb 06, 2024

Rodrigo_ 

I had to make some minor changes but YES! That works!!

 

Thank you SOOOOOO much! You're truly amazing!!! 🙂

Rodrigo_'s avatar
Rodrigo_
Iron Contributor
Feb 06, 2024
I'm glad that we finished this. 😄
no problem mate.
  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Feb 07, 2024
    can you Direct message me your actual workbook you're working for?
  • Immortalis's avatar
    Immortalis
    Brass Contributor
    Feb 06, 2024
    That modification doesn't work still. If I change 170 to 160, it returns blank. If I change 170 to 171 I still get a circular reference error.
  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Feb 06, 2024
    or just simply use other cell for your formula.
  • Immortalis's avatar
    Immortalis
    Brass Contributor
    Feb 06, 2024
    Also, Even in youre Book3 you sent, if you make another entry then copy the formula to the next cell, the cell doesnt find the entry and returns blank
  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Feb 06, 2024
    you put the formula on cell AQ170, right?
    try to read the formula again carefully.
    you include that cell (AQ170) in the formula. try to adjust is since it will really cause you a circular loop that can't be resolved.

    To fix this, you’ll need to adjust the ranges in your formula so that they don’t include cell AQ170. If you’re comparing the values in cells AO170 and AP170 with the values in the range BaseInfo!$U$15:BaseInfo!$W$38, you can modify your formula like this:
    =IFERROR(INDEX(BaseInfo!$W$15:$W$38, SMALL(IF((MMULT(--(AO170:AP170<>BaseInfo!$U$15:BaseInfo!$W$38),TRANSPOSE(COLUMN(AO170:AP170)^0))>0)*(BaseInfo!$U$15:BaseInfo!$U$38=_SectionCalc), ROW(BaseInfo!$U$15:BaseInfo!$U$38)-ROW(BaseInfo!$U$15)+1), ROW(A1))),"")
  • Immortalis's avatar
    Immortalis
    Brass Contributor
    Feb 06, 2024

    Rodrigo_ 

     

    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?

Resources