1. Advanced Formulas & Functions

Mastering Excel’s advanced formulas helps you automate tasks, analyze complex datasets, and create smart spreadsheets. Below are the key topics:

ARRAYFORMULAS / Dynamic Arrays

Dynamic Arrays automatically spill results into adjacent cells. Use formulas like FILTER, SORT, UNIQUE, SEQUENCE to generate dynamic ranges without copying formulas manually.

INDEX-MATCH

A powerful alternative to VLOOKUP or XLOOKUP for searching and retrieving values.
Example: =INDEX(B2:B10, MATCH("Apple", A2:A10, 0))

XLOOKUP & XMATCH (Modern Excel)

More flexible than VLOOKUP. Allows searches both vertically and horizontally with default exact match.
Example: =XLOOKUP("Apple", A2:A10, B2:B10)

IF with AND/OR/NESTED

Use logical functions inside IF statements to apply multiple conditions.
Example: =IF(AND(A2>50, B2="Yes"), "Qualified", "Not Qualified")

TEXT Functions

Useful for manipulating and cleaning text data:

• TEXTJOIN: Combines text with delimiter – =TEXTJOIN(", ", TRUE, A2:A5)
• CONCAT: Joins strings – =CONCAT(A1, B1)
• LEFT, RIGHT, MID: Extract parts of a string
• TRIM: Removes extra spaces – =TRIM(A1)

DATE Functions

Perform date calculations and manage time-based data:

• EOMONTH: Last day of a month – =EOMONTH(TODAY(), 1)
• NETWORKDAYS: Count working days – =NETWORKDAYS(A1, B1)
• DATEDIF: Difference between dates – =DATEDIF(A1, B1, "y")

INDIRECT & OFFSET

INDIRECT references a cell indirectly by string – =INDIRECT("A"&B1)
OFFSET returns a reference that is offset from a starting cell – =OFFSET(A1,2,1)

LET and LAMBDA (Custom Functions)

LET defines variables inside formulas for readability and performance.
=LET(x, A1+10, x*2) means A1+10 is calculated once and reused.

LAMBDA lets you create custom functions in Excel like programming languages.

Example:

=LAMBDA(x, x*10)(5)