Exceljet

Quick, clean, and to the point

LAMBDA contains which things

Excel formula: LAMBDA contains which things
Summary 

This example explains the conversion of the standard Excel formula here into a custom LAMBDA function. In the example shown, cell C5 contains the custom function, copied down:

=ContainsWhichThings(B5,things,", ","")

where B5 contains the text to process, things is the named range E5:E9, the delimiter is a comma, and the default value is an empty string (""). See below for a detailed explanation.

Explanation 

The goal in this example is to use a formula to report which things exist a cell. The list of things to check for is in the named range things (E5:E9). The result is returned as a comma separated text string.

The first step in creating a custom function with the LAMBDA function is to verify the logic needed to solve the problem. The formula below will do the job and return the result seen in column C:

=TEXTJOIN(", ",1,FILTER(things,ISNUMBER(SEARCH(things,B5)),""))

This formula uses four separate functions: TEXTJOIN, FILTER, ISNUMBER, and SEARCH. The core search logic is explained in detail here. FILTER catches the output from SEARCH and returns a list of matching strings, and TEXTJOIN concatenates the values together and returns a final result.

Thinking about the logic in a more general way, we can see that there are at least four potential inputs: the text to process, the things to look for, the delimiter to use when joining final result, and a default value to return if the formula finds no matches. The formula below is a direct port to LAMBDA syntax, with the four inputs above as set up as named arguments:

=LAMBDA(text,things,delim,default,TEXTJOIN(delim,1,FILTER(things,ISNUMBER(SEARCH(things,text)),default)))

Notice the four inputs above have been defined as function arguments. Once this generic version of the function is named and defined with the Name Manager, the custom function can be used like this:

=ContainsWhichThings(B5,things,", ","")

with the same result as before.

Adding a sort option

In the LAMBDA example above, the primary benefit of making a custom function is ease of use: the custom function is easier to call and configure than the original formula.

However, if we extend the formula to sort results in the order things were found in the text, the base formula becomes significantly more complex and redundant:

=TEXTJOIN(", ",1,SORTBY(FILTER(things,ISNUMBER(SEARCH(things,B5)),""),SEARCH(FILTER(things,ISNUMBER(SEARCH(things,B5)),""),B5)))

In this version, is we sort the list returned by FILTER by the position at which things occur in the text. We do this with the SORTBY function and the main complication is in creating a sort_by argument, which is done here:

SEARCH(FILTER(things,ISNUMBER(SEARCH(things,B5)),""),B5) // sort_by

Notice the code inside the outer SEARCH is repeats code already in the formula. To clean things up, we'll want to use the LET function, but first, we'll update the existing LAMBDA code to use the new sort logic:

=LAMBDA(text,things,delim,default,
  TEXTJOIN(", ",1,
    SORTBY(
      FILTER(things,ISNUMBER(SEARCH(things,text)),""),
      SEARCH(FILTER(things,ISNUMBER(SEARCH(things,text)),""),text))
  )
)(B5,things,", ","")

The generic function above works fine fine, but is still redundant. We can reduce reduce the redundant code by assigning intermediate results to variables with the LET function. Below is a refactored version of the formula above:

=LAMBDA(text,things,delim,default,
LET(
  searchResults,FILTER(things,ISNUMBER(SEARCH(things,text)),""),
  sortedResults,SORTBY(searchResults,SEARCH(searchResults,text)),
  result,TEXTJOIN(", ",1,sortedResults),
  result
  )
)(B5,things,", ","")

Notice the primary FILTER(ISNUMBER(SEARCH())) code only appears once now, and the result is assigned to the variable "searchResults", which is used twice in the line below. Next, we'll make the sort optional, by adding a new argument called "sort":

=LAMBDA(text,things,delim,default,sort,
LET(
  searchResults,FILTER(things,ISNUMBER(SEARCH(things,text)),""),
  sortedResults,IF(sort,
  SORTBY(searchResults,SEARCH(searchResults,text)),searchResults),
  result,TEXTJOIN(", ",1,sortedResults),
  result
  )
)(B5,things,", ","",TRUE)

The "sort" argument acts like a toggle. When sort is TRUE, the function will sort search results in the order they appear in text. When sort is FALSE, the function will leave the list unsorted, and found items will appear in their original order (i.e. the order they are listed in "things").  The logic for this is handled by the IF function. This is a good example of how the LAMBDA and LET functions together make it possible to extend the behavior of a custom function.

The screen below shows the new version of the formula in action. Notice the sort argument has been set to TRUE, so results are now sorted in the order they appear in text:

LAMBDA contains which strings sorted

LAMBDA is a beta function available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.