Excel LAMBDA Function - More Complex Examples

Опубликовано: 04 Октябрь 2024
на канале: Sele Training

11,574

275

You asked for more examples of the brand-new Excel LAMBDA function. Here are some complex formulas that make better use of LAMBDA.

Introduction to LAMBDA Video:    • Excel LAMBDA Function
Advanced Excel Functions Video:    • 10 Advanced Excel Functions with Down...
Dynamics Array Formulas Video:    • Excel Dynamic Array Formulas

Support me with your Amazon purchases, click here: https://amzn.to/2Kxp533

Contents 0:00
1. Count Words 0:23
2. Count Words Array 1:54
3. Most Frequent 3:19
4. Last Value 4:36
5. Filter List 6:05

CountWords
=LAMBDA(words,LEN(TRIM(words))-LEN(SUBSTITUTE(TRIM(words)," ",""))+1)

CountWordsArray
=LAMBDA(words,SUMPRODUCT(LEN(TRIM(words))-LEN(SUBSTITUTE(words," ",""))+1))

MostFrequent
=LAMBDA(list,INDEX(list,MODE(MATCH(list,list,0))))

LastValue
=LAMBDA(list,name,INDEX(list,MAX(IF(list=name,ROW(list)-MIN(ROW(list))+1)),2))

FilterList
=LAMBDA(range,year,category,product,FILTER(range,IF(year="",1,OFFSET(range,0,0,,1)=year)*IF(category="",1,OFFSET(range,0,1,,1)=category)*IF(product="",1,OFFSET(range,0,2,,1)=product),""))

#seletraining #excel #lambda