Forum Discussion
Filter with search not returning excepted values
The formula is something of a monster and you might like to consider the use of the LET function to separate the process of selecting the columns you wish to return from that of filtering records. Assuming the defined names refer to columns of a single table, CHOOSECOLS would offer a more succinct approach to selecting columns and assigning the reduced array to a LET variable.
That aside, it is your use of OR in the filter criterion that I would focus on. OR returns only a single value when used with array arguments. The straightforward solution to getting an array of criteria is to use the "+" operator instead. Otherwise you could use MAP/LAMBDA to combine the two criteria using OR.
The formula is something of a monster and you might like to consider the use of the LET function to separate the process of selecting the columns you wish to return from that of filtering records. Assuming the defined names refer to columns of a single table, CHOOSECOLS would offer a more succinct approach to selecting columns and assigning the reduced array to a LET variable.
That aside, it is your use of OR in the filter criterion that I would focus on. OR returns only a single value when used with array arguments. The straightforward solution to getting an array of criteria is to use the "+" operator instead. Otherwise you could use MAP/LAMBDA to combine the two criteria using OR.