CODEWITHSUNDEEP
excelexcel-formulaoffice365
Lionel Rowe
Lionel Rowe

Reputation: 5983

Using a LAMBDA as the 2nd argument to FILTER

In Excel, I can easily pass a LAMBDA as the 2nd parameter to MAP, as expected:

=MAP({1, 2, 3}, LAMBDA(x, x = 1))
// { TRUE, FALSE, FALSE }

If I try to do the same with FILTER, I get an error:

=FILTER({1, 2, 3}, LAMBDA(x, x = 1))
// #CALC!

I have a few related questions:

  1. Is there actually some way of using a LAMBDA as the 2nd parameter here that I'm missing?
  2. The official documentation for FILTER mentions nothing about the type or any restrictions for the parameters — it simply says "based on criteria you define". Where can I find documentation for Excel formulas that explicitly gives such info (e.g. "the nth parameter must be a string", "must be an absolute reference given in R1C1 format", "must be a number between -283 and 87845", "must be a range of cells and not an array literal", "must be a range of cells or an array literal but not an array cell reference for some reason", etc etc)
  3. If I can't use a LAMBDA, how would I filter an array literal?
  4. Does Microsoft have any roadmap for realeasing such obviously useful features that would give the consistent API for MAP and FILTER that any sane person would expect?

If I sound salty it's because I'm salty

Upvotes: 8

Views: 2025

Answers (2)

JvdV
JvdV

Reputation: 76000

Yes you can but you have to know the difference between the lambda-helper function MAP() and the actual LAMBDA() function.

Syntax for MAP():

=MAP(array1, lambda_or_array<#>)

Map is a build-in lambda helper, and since the sole function is to apply a lambda in an iterative calculation you can apply =MAP({1,2,3},LAMBDA(x,x=1)).

Syntax for creating a LAMBDA() in a cell as per official documentation:

=LAMBDA([parameter1, parameter2, ...],calculation)(function call)

The difference here is that with the actual lambda you still ought to create your own custom function. For this the syntax is a little different and in contrast to the build-in functions like MAP(), REDUCE() etc; you'd still need to somehow include the input. This is done through an extra set of paranthesis at the end of the function: =LAMBDA(x,x=1)({1,2,3}).¹

So now we know that with the build-in lambda-helpers, the input is an actual parameter within the function's syntax we could apply both to your FILTER():

=FILTER({1,2,3},MAP({1,2,3},LAMBDA(x,x=1)))

Or²:

=FILTER({1,2,3},LAMBDA(x,x=1)({1,2,3})

See how the MAP() function does make live a little easier? One could also use LAMBDA() to create their own custom function as per footnote below and call it:

=FILTER({1,2,3},TEST({1,2,3})

¹ One would usually create a custom named lamda function in the name manager. In such cases you'd not need to include the source at the end but call the function including the input in the 1st parameter. e.g: a custom named function called TEST() which refers to =LAMBDA(x,x=1) would output {TRUE,FALSE,FALSE} when called =TEST({1,2,3}).

² If you do want to use array literals, consider to incorporate LET() to be able to chuck the array into a named variable first. e.g: =LET(x,{1,2,3},FILTER(x,LAMBDA(y,y=1)(x))).

Upvotes: 7

Ike
Ike

Reputation: 13064

To answer your third question: you can use this formula:

=LAMBDA(x,FILTER(x,x=2))({1,2,3})

Or - if you want to keep the columns:

=BYCOL({1,2,3},LAMBDA(x,FILTER(x,x=2,"")))

enhancement to the first version to pass the filter-value:

=LAMBDA(arrValues, filterBy, FILTER(arrValues,arrValues = filterBy))({1,2,3};3)

Upvotes: 1

Related Questions