Forum Discussion

Twifoo's avatar
Twifoo
Silver Contributor
Jan 06, 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

Sabeel's avatar
Sabeel
Copper Contributor
Jan 06, 2020

 

 

Twifoo 

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jan 06, 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 (""). 

    • Sabeel's avatar
      Sabeel
      Copper Contributor
      Jan 06, 2020

      entering the  cell data show blank and adjuscent data errorTwifoo 

    • Sabeel's avatar
      Sabeel
      Copper Contributor
      Jan 06, 2020

       

       

      Twifoo 

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

        Sabeel 

        Excel introduced AGGREGATE in 2010. The #NAME? error presupposes that your version is 2007 or earlier. Thus, I modified the formula in M2 to probably suit your version, as shown in the snapshot below: 

Resources