Chapter 7: Advanced Formulas & Functions

Advanced Formulas & Functions — this is where Excel becomes really powerful and you start solving real business/school problems efficiently.

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you. We’ll use real-life examples (continuing our tea shop theme where possible), clear syntax, common mistakes, and practice tips. All examples work in Excel 365 / 2021 / 2024 / 2025–2026 versions (XLOOKUP is now widely available).

Let’s dive in!

1. Logical Functions: IF, AND, OR, IFS, SWITCH

These help make decisions in formulas.

A. IF – Classic single condition Syntax: =IF(logical_test, value_if_true, value_if_false)

Example – Bonus eligibility

• Column A: Sales Amount
• Column B: Bonus In B2: =IF(A2>=5000, “Yes – ₹500”, “No”) → If sales ≥ ₹5000 → “Yes – ₹500”, else “No”

B. AND / OR – Test multiple conditions

• AND: All must be TRUE
• OR: At least one TRUE

Example – Eligible for discount? Assume columns: Qty (C), City (D) In E2: =IF(AND(C2>=50, D2=”Mumbai”), “10% Discount”, “No Discount”) → Only if Qty ≥50 AND City = Mumbai → discount

With OR: =IF(OR(C2>=100, D2=”Navi Mumbai”), “Free Delivery”, “No”)

C. IFS – Multiple conditions (replaces deep nested IF) Syntax: =IFS(test1, result1, test2, result2, …, [default])

Example – Grade based on score In B2 (score in A2): =IFS(A2>=90, “A+”, A2>=80, “A”, A2>=70, “B”, A2>=60, “C”, TRUE, “Fail”) → Checks from top → first TRUE wins Last TRUE is default (like “else”)

D. SWITCH – Exact match on one value Syntax: =SWITCH(expression, value1, result1, value2, result2, …, [default])

Example – Month number to name In B2 (month number 1–12 in A2): =SWITCH(A2, 1, “Jan”, 2, “Feb”, 3, “Mar”, …, 12, “Dec”, “Invalid”)

IFS vs SWITCH

• Use IFS when conditions are different (>, <, AND/OR)
• Use SWITCH when checking exact matches on one value (faster to read for categories)
• IFS is more flexible for inequalities; SWITCH is cleaner for exact lists.

Tip: Avoid deep nesting (>3 IFs) → use IFS/SWITCH instead.

2. Lookup Functions: VLOOKUP, HLOOKUP, INDEX & MATCH, XLOOKUP (Modern)

A. VLOOKUP – Vertical lookup (classic, but limited) Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example – Price list Sheet “Prices”: A1:B5 → Item | Price

• Chai | 15
• Coffee | 35

In sales sheet, item in A2: =VLOOKUP(A2, Prices!A:B, 2, FALSE) → exact match price

Limits: Can’t look left, must count columns, no dynamic.

B. HLOOKUP – Horizontal (rarely used) Similar but searches across row.

C. INDEX & MATCH – Flexible replacement (pre-XLOOKUP king) =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example (same price list): =INDEX(Prices!B:B, MATCH(A2, Prices!A:A, 0)) → Finds row number with MATCH → returns price from INDEX

Can look left/right/up/down.

D. XLOOKUP – Modern, best choice (Excel 365/2021+) Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Advantages:

• Looks left/right
• Exact/approximate/wildcard
• Custom “Not Found” message
• Search first/last

Example 1 – Basic lookup =XLOOKUP(A2, Prices!A:A, Prices!B:B, “Item Not Found”)

Example 2 – Return multiple columns (spill) =XLOOKUP(A2, Prices!A:A, Prices!B:D, “Not Found”) → Returns price + category + supplier (3 columns spill)

Example 3 – Approximate match (tax slab) =XLOOKUP(sales, slabs, rates, , -1) → -1 = exact or next smaller

Recommendation (2026): Use XLOOKUP whenever possible — it’s simpler, safer, and more powerful than VLOOKUP/INDEX-MATCH.

3. Text Functions: LEFT, RIGHT, MID, CONCAT, TEXTJOIN, TRIM

Clean and combine text.

A. LEFT / RIGHT / MID – Extract parts

• LEFT(text, num_chars)
• RIGHT(text, num_chars)
• MID(text, start_num, num_chars)

Example – Phone number cleanup Cell A2: “022-45678901” B2: =LEFT(A2,3) → “022” C2: =RIGHT(A2,8) → “45678901” D2: =MID(A2,5,8) → “45678901”

B. CONCAT – Join text (replaces &) =CONCAT(A2,” – “,B2) → “Chai – Mumbai”

C. TEXTJOIN – Join with delimiter (ignores empties) Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2],…)

Example – Combine tags =TEXTJOIN(“, “, TRUE, C2:C10) → “Mumbai, Thane, Pune” (skips blanks)

D. TRIM – Remove extra spaces =TRIM(A2) → “Rahul Sharma” (removes leading/trailing/multiple spaces)

Real use: Clean imported data (customer names, addresses).

4. Date & Time Functions: TODAY, NOW, DATE, EDATE, NETWORKDAYS

Handle dates smartly.

A. TODAY / NOW

• =TODAY() → current date (updates daily)
• =NOW() → current date + time

B. DATE – Build date from year/month/day =DATE(2026,1,26) → 26-Jan-2026

C. EDATE – Add months (great for EMI dates) =EDATE(A2, 3) → 3 months after date in A2

D. NETWORKDAYS – Working days between two dates (excludes weekends + holidays)

Example – Project days Start: B2 = 01-Jan-2026 End: C2 = 31-Mar-2026 Holidays: List in F2:F10 (e.g., Republic Day 26-Jan)

In D2: =NETWORKDAYS(B2, C2, F2:F10) → Counts Mon–Fri, skips weekends + holidays

Tip: Use NETWORKDAYS.INTL if weekend is not Sat-Sun (e.g., Fri-Sat off).

Quick Summary Table – Chapter 7

Homework / Practice (Do This Today!)

1. In your practice file → new sheet “Advanced”
2. Create a small price lookup table (Item | Price | Category)
3. In sales area: Use XLOOKUP to pull price & category from item name
4. Add sales column → use IFS to categorize: >10000=”High”, >5000=”Medium”, else “Low”
5. Use SWITCH on a month number column → show month name
6. Clean names: Use TRIM + CONCAT (First & Last name)
7. Add dates: Use TODAY(), EDATE (expiry 3 months later)
8. Calculate NETWORKDAYS between order date & delivery date (add 2–3 holidays)