LAMBDA for custom functions in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the LAMBDA function in Excel?

LAMBDA lets you create your own custom functions using Excel formulas without VBA or macros. You can reuse these functions like built-in ones.

beginner

How do you define parameters in a LAMBDA function?

You list parameters as the first arguments inside LAMBDA, followed by the formula using those parameters. For example, =LAMBDA(x, y, x + y) defines two parameters x and y.

intermediate

How can you use a LAMBDA function after defining it?

You can call it directly by passing arguments, or save it with a name using the Name Manager to reuse it anywhere in the workbook.

beginner

What happens if you forget to provide all parameters when calling a LAMBDA function?

Excel will return a #CALC! error because the function expects all parameters to be provided for calculation.

advanced

Can LAMBDA functions call themselves? What is this called?

Yes, LAMBDA functions can call themselves for repeated calculations. This is called recursion.

What does the LAMBDA function in Excel allow you to do?

A Import data from the web

B Automatically format cells

C Create custom reusable functions without VBA

D Create pivot tables

LAMBDA lets you write your own functions using formulas that you can reuse, without needing VBA or macros.

How do you specify inputs for a LAMBDA function?

A By listing parameters first inside the LAMBDA formula

B By naming cells outside the formula

C By using the IF function

D By creating a table

Parameters are listed as the first arguments inside the LAMBDA function before the formula.

What tool in Excel helps you save a LAMBDA function for reuse?

A Conditional Formatting

B Data Validation

C Goal Seek

D Name Manager

Name Manager lets you assign a name to your LAMBDA function so you can use it anywhere in the workbook.

What error appears if you call a LAMBDA function without all required parameters?

A #CALC!

B #REF!

C #VALUE!

D #NAME?

Missing parameters cause a #CALC! error because Excel cannot calculate the function properly.

What is it called when a LAMBDA function calls itself?

A Iteration

B Recursion

C Looping

D Nesting

Recursion is when a function calls itself to perform repeated calculations.

Explain how to create and use a simple LAMBDA function that adds two numbers.

Think about how you write a formula with inputs and reuse it.

Describe the benefits of using LAMBDA functions instead of repeating formulas in Excel.

Consider how custom functions help avoid mistakes and save time.

Practice

1. What does the LAMBDA function in Excel allow you to do?

easy

A. Convert text to numbers

B. Create your own custom formulas with parameters

C. Automatically sort data in a column

D. Create charts from data ranges

Solution

1. Step 1: Understand the purpose of LAMBDA

   LAMBDA lets you create custom formulas that can take inputs and return results, like your own mini-functions.

2. Step 2: Compare with other options

   Sorting, converting text, and creating charts are done by other Excel features, not LAMBDA.

3. Final Answer:

   Create your own custom formulas with parameters -> Option B

4. Quick Check:

   LAMBDA = custom formulas [OK]

Hint: LAMBDA = your own formula with inputs [OK]

Common Mistakes:

• Thinking LAMBDA sorts or formats data
• Confusing LAMBDA with built-in functions like SORT
• Assuming LAMBDA creates charts

2. Which of these is the correct syntax to define a LAMBDA function that adds two numbers x and y?

easy

A. =LAMBDA(x, y, x + y)

B. =LAMBDA(x + y)

C. =LAMBDA(x, y; x + y)

D. =LAMBDA(x, y, x - y)

Solution

1. Step 1: Check LAMBDA syntax

   LAMBDA syntax is =LAMBDA(parameter1, parameter2, ..., calculation). So =LAMBDA(x, y, x + y) is correct.

2. Step 2: Identify errors in other options

   =LAMBDA(x + y) misses parameters, C uses wrong separator (;), D subtracts instead of adding.

3. Final Answer:

   =LAMBDA(x, y, x + y) -> Option A

4. Quick Check:

   Correct LAMBDA syntax uses commas and parameters [OK]

Hint: LAMBDA needs parameters, commas, and a formula [OK]

Common Mistakes:

• Using semicolons instead of commas
• Omitting parameters
• Mixing up addition and subtraction

3. Given the formula =LAMBDA(x, y, x * y)(3, 4), what is the result shown in the cell?

medium

A. 7

B. 34

C. 12

D. Error

Solution

1. Step 1: Understand the LAMBDA call

   The LAMBDA takes two inputs x and y, then returns x * y. Here, x=3 and y=4.

2. Step 2: Calculate the multiplication

   3 multiplied by 4 equals 12.

3. Final Answer:

   12 -> Option C

4. Quick Check:

   3 * 4 = 12 [OK]

Hint: Multiply inputs inside LAMBDA to get result [OK]

Common Mistakes:

• Adding instead of multiplying
• Concatenating numbers as text
• Expecting an error from correct syntax

4. What is wrong with this LAMBDA formula? =LAMBDA(x, y, x + y(2, 3))

medium

A. Incorrect function call syntax inside LAMBDA

B. Missing closing parenthesis

C. Parameters x and y are not defined

D. LAMBDA cannot add numbers

Solution

1. Step 1: Analyze the formula structure

   The formula tries to add x + y(2, 3), but y is a parameter, not a function, so y(2, 3) is invalid.

2. Step 2: Identify the syntax error

   You cannot call parameters like functions inside LAMBDA. The correct call is outside the LAMBDA definition.

3. Final Answer:

   Incorrect function call syntax inside LAMBDA -> Option A

4. Quick Check:

   Parameters are values, not callable functions [OK]

Hint: Don't call parameters like functions inside LAMBDA [OK]

Common Mistakes:

• Trying to call parameters as functions
• Confusing LAMBDA parameters with named functions
• Ignoring parentheses placement

5. You want to create a reusable LAMBDA function named DOUBLE that doubles any number input. Which steps correctly create and use this function?

hard

A. Write =DOUBLE(5) without defining DOUBLE anywhere.

B. Write =LAMBDA(x, x*2)(5) directly in a cell without naming it.

C. Define DOUBLE as =LAMBDA(x, x+2) in Name Manager, then use =DOUBLE(5).

D. Define DOUBLE as =LAMBDA(x, x*2) in Name Manager, then use =DOUBLE(5) in a cell.

Solution

1. Step 1: Define the LAMBDA function correctly

   In Name Manager, create a name DOUBLE with formula =LAMBDA(x, x*2) to double input x.

2. Step 2: Use the named function in cells

   After defining, use =DOUBLE(5) in any cell to get 10.

3. Final Answer:

   Define DOUBLE as LAMBDA(x, x*2) and call DOUBLE(5) -> Option D

4. Quick Check:

   Named LAMBDA + call = reusable function [OK]

Hint: Name LAMBDA in Name Manager, then call by name [OK]

Common Mistakes:

• Using wrong formula (x+2 instead of x*2)
• Calling named function without defining it
• Trying to use LAMBDA without naming for reuse