Forum Discussion

7 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jan 07, 2020

    Hi Sabeel, 

    In the attached file, the formula in non-array M2 is: 

    =IFERROR(INDEX(A$2:A$9,AGGREGATE(15,6,
    ROW(A$2:A$9)/(COUNTIF(E$2:E$3,A$2:A$9)=0)-1,
    ROW()-1)),"")

    The foregoing formula returns the unmatched chassis numbers in Column M, as shown in the snapshot below: 

    Thereafter, this simple VLOOKUP formula returns the values in Columns N and O: 

    =IFNA(VLOOKUP($M2,
    $A2:$C9,
    MATCH(N$1,$A$1:$C$1,0),0),"")

    Cheers, 

    Twifoo

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Jan 07, 2020

        Sabeel 

        In that case, you have to convert the ranges to tables, such that the formula in M2 will be as shown in the snapshot below: 

        Thereafter, copy the last row in the Unmatched Table until the new last row returns empty text (""). 

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Jan 07, 2020

    Sabeel 

    Please find the two files, one with an Array Formula to get the desired data and another with Power Query based solution.

     

    For the formula solution, you will find the below Array Formula in M2 which is copied across and down.

    =IFERROR(INDEX(A$2:A$9,SMALL(IF(ISNA(MATCH($A$2:$A$9,$E$2:$E$3,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$2:A2))),"")

     

Resources