Forum Discussion

TerryBennett's avatar
TerryBennett
Copper Contributor
Nov 08, 2025

Nesting SortBy and Filter functions

Can someone suggest where I've gone wrong with this syntax please:

 

=SORTBY(FILTER(Worklist,List!I2:I500=0),List!A2:A500,1,List!G2:G500,1,List!E2:E500,1)

 

Currently returning the #VALUE! error.

 

"Worklist" is a table on worksheet "List".

 

All I am trying to do is to filter out all of the zero values in column 'I' and then sort the result by columns 'A', 'G' and 'E'

 

I'm sure I'm doing something stupid!

 

Many thanks.

 

excel
Formulas and Functions

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 10, 2025

    You can filter first but then the sort must reference columns of the filtered array, not the original.

    = SORT(FILTER(worklistTbl, worklistTbl[Column4]=0), {1,3,2})

    With LET the formula might read

    = LET(
    filteredTable, FILTER(worklistTbl,worklistTbl[Column4]=0),
    SORT(filteredTable, {1,3,2})
  )

     

  • TerryBennett's avatar
    TerryBennett
    Copper Contributor
    Nov 10, 2025

    Thanks both Oliver and m_tarler - much appreciated.

     

    Oliver, not sure I follow your syntax directly after SORT (what are the other letters?) but the screenshot isn't too clear.

     

    MT, some of those functions are not familiar to me so I'll need to study them!

  • kspittler12's avatar
    kspittler12
    Copper Contributor
    Nov 10, 2025

    The #VALUE! error is likely because the SORTBY ranges (List!A2:A500, etc.) don’t match the number of rows returned by the FILTER function. You need to sort using columns from the filtered result, not the original sheet. =IFERROR(
      SORTBY(
        FILTER(Worklist, Worklist[I]=0),
        Worklist[A], 1,
        Worklist[G], 1,
        Worklist[E], 1
      ),
      "No matching data"
    )

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 10, 2025
    =FILTER(SORTBY(Worklist,List!A2:A500,1,List!G2:G500,1,List!E2:E500,1),List!I2:I500=0)

    Does this return the intended result in your sheet as well?

    Currently i can't attach a sample file that's why i've added a screenshot that shows the output if the data is in the same sheet. The shared formula works across sheets in my sample file.

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Nov 10, 2025

      The original problem was that the lengths of the FILTERed array is different than the length for the SORTBY array(s).

      Oliver, your solutions doesn't work because the SORTBY array is no longer in the same order as the array you are using to FILTER by.  (i.e. the letter B is lined up with a number 2 so shouldn't be included in the output)

      so you need to apply both operations to both sets.  For example using Oliver's set-up try:

      =LET(s, SORTBY(HSTACK(Worklist,List!I2:I500),List!A2:A500,1,List!G2:G500,1,List!E2:E500,1),
          FILTER(TAKE(s,,1),TAKE(s,,-1)=0)
          )

       

Resources