Forum Discussion

ericGuyaderBerger's avatar
ericGuyaderBerger
Copper Contributor
May 07, 2021
Solved

Excel FILTER built-in function

Hi,   I tried but didn't succed to use FILTER and OFFSET functions in the same formula in order to get values located in the 2nd column of a table, only for rows matching a criteria. It looks like ...
excel
Formulas and Functions
  • ericGuyaderBerger's avatar
    ericGuyaderBerger
    Copper Contributor
    May 07, 2021

    Thank you, this solution takes me a step further, as I can get the only values of my second column matching the criteria based on first column values.
    But if it works as a cell's formula, it still doesn't work as a validation formula.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 07, 2021

      ericGuyaderBerger 

      Excel doesn't allow to use array formulas for data validation. You may generate spill by FILTER, even in another sheet (and hide it if necessary), use reference on this spill in data validation

      • ericGuyaderBerger's avatar
        ericGuyaderBerger
        Copper Contributor
        May 07, 2021

        SergeiBaklan 

         

        Thanks again for this new reply.

         

        Spill reference works like a charm!  

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 07, 2021

      ericGuyaderBerger 

      Thus formula is literally as above, use second column as first parameter and formula for criteria on first column as second parameter.

      By the way, I try to play with Analyze Data on such table

      asking

      filter 'Two' where 'One' is a1 as table

      Result is

      with generated formula

      =IF(SUM(ISERROR(Table1)*1,MIN(SUM(1-ISNUMBER(Table1[Two])),SUM(1-ISTEXT(Table1[Two]))))>0,NA(),SORT(UNIQUE(FILTER(Table1[Two],Table1[One]="a1"))))

      Bit overcomplicated, but that's robot. We may skip error handling and take only FILTER part.

Resources