Learning Objectives

What You'll Learn Today

By the end of this session, you will be able to:

Section 1

Lookup Functions

Find and retrieve data efficiently from tables and ranges

💭
Think About It

How would you find the price of a specific product from a list of 10,000 items?

Manual search would take forever! Let's learn how Excel can do it instantly.

🔍 Lookup & Reference Functions
VLOOKUP Vertical Lookup
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Searches for a value in the first column of a table and returns a value in the same row from another column.

Parameter Description Example
lookup_value The value to search for "AAPL" or A2
table_array The table range to search in $A$1:$D$100
col_index_num Column number to return (1-based) 3 (for 3rd column)
range_lookup FALSE = exact match, TRUE = approximate FALSE (recommended)
📊 Practical Example: Stock Price Lookup
Stock Data Table
A B C D
Ticker Company Price Sector
AAPL Apple Inc. $178.50 Technology
MSFT Microsoft $378.90 Technology
GOOGL Alphabet $141.25 Technology
JPM JPMorgan $195.60 Financial

Formula: =VLOOKUP("GOOGL", A1:D5, 3, FALSE)

Result: $141.25

How it works:

  1. Excel searches for "GOOGL" in the first column (A)
  2. Finds "GOOGL" in row 4
  3. Moves to column 3 (Price column)
  4. Returns the value: $141.25
⚠️
Common Mistakes:
  • Forgetting FALSE for exact match (defaults to approximate)
  • Not using absolute references ($A$1:$D$5) when copying formulas
  • Looking up values in columns other than the first one
XLOOKUP Modern Lookup (Excel 365/2021+)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The modern, more powerful replacement for VLOOKUP. It can look in any direction, returns exact matches by default, and handles errors gracefully.

Parameter Description Example
lookup_value The value to search for "AAPL"
lookup_array The range to search A1:A5
return_array The range to return values from C1:C5
if_not_found Value if not found (optional) "Not Found"
Old Way

VLOOKUP

=VLOOKUP("GOOGL", A1:D5, 3, FALSE)
  • Must count columns
  • Can only look right
  • No default error handling
New Way

XLOOKUP

=XLOOKUP("GOOGL", A1:A5, C1:C5, "Not Found")
  • Select return range directly
  • Can look any direction
  • Built-in error handling
💡
Pro Tip: If you have Excel 365 or Excel 2021+, use XLOOKUP instead of VLOOKUP. It's more intuitive, more powerful, and less error-prone!
INDEX-MATCH The Power Combo
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

The combination of INDEX and MATCH functions creates the most flexible lookup method. Works in all Excel versions and can handle lookups in any direction.

INDEX

=INDEX(range, row_num, [col_num])

Returns the value at a specific position in a range.

MATCH

=MATCH(value, range, 0)

Returns the position of a value in a range (0 = exact match).

📊 Example: Employee Salary Lookup
Employee Data
A B C D
Emp ID Name Department Salary
1001 Rahul Sharma Finance $85,000
1002 Priya Patel Marketing $72,000
1003 Amit Kumar Finance $91,000

Step 1 - Find row position:

=MATCH(1003, A2:A4, 0) → Returns 3 (3rd row)

Step 2 - Get the salary:

=INDEX(D2:D4, 3) → Returns $91,000

Combined Formula:

=INDEX(D2:D4, MATCH(1003, A2:A4, 0)) → Returns $91,000

🏆
Why Use INDEX-MATCH?
  • Works when lookup column is NOT on the left
  • More efficient with large datasets
  • Inserting columns won't break your formula
  • Works in all Excel versions

📊 Lookup Functions Comparison

Feature VLOOKUP XLOOKUP INDEX-MATCH
Look Left ❌ No ✓ Yes ✓ Yes
Exact Match Default ❌ No ✓ Yes ✓ Yes
Error Handling ❌ No ✓ Yes ⚠ With IFERROR
All Excel Versions ✓ Yes ❌ 365/2021+ ✓ Yes
Ease of Use ⭐⭐⭐ ⭐⭐⭐⭐⭐ ⭐⭐⭐⭐
Section 2

Logical Functions

Make decisions and handle errors in your models

🧠 Logical Functions
IF Conditional Logic
=IF(logical_test, value_if_true, value_if_false)

Tests a condition and returns one value if TRUE, another if FALSE. This is the foundation of decision-making in Excel.

Parameter Description Example
logical_test Condition to evaluate A1>100
value_if_true Result if condition is TRUE "High"
value_if_false Result if condition is FALSE "Low"
📊 Example: Sales Commission Calculator
Commission Structure: Sales > $10,000 = 10%, otherwise 5%
A B C
Salesperson Sales Commission Rate
Rahul $8,000 =IF(B2>10000, 10%, 5%)
Priya $15,000 =IF(B3>10000, 10%, 5%)
Amit $10,500 =IF(B4>10000, 10%, 5%)

Results:

  • Rahul: $8,000 ≤ $10,000 → 5%
  • Priya: $15,000 > $10,000 → 10%
  • Amit: $10,500 > $10,000 → 10%

Nested IF Statements

When you have multiple conditions, you can nest IF functions:

📊 Example: Credit Rating System

Formula:

=IF(A1>=750, "Excellent",
  IF(A1>=700, "Good",
    IF(A1>=650, "Fair",
      IF(A1>=600, "Poor", "Bad")
    )
  )
)
750+
Excellent
700-749
Good
650-699
Fair
600-649
Poor
<600
Bad
AND / OR Multiple Conditions
=AND(condition1, condition2, ...)
=OR(condition1, condition2, ...)

Combine multiple conditions. AND returns TRUE only if ALL conditions are true. OR returns TRUE if ANY condition is true.

AND Function

All conditions must be TRUE

Formula Result
=AND(5>3, 10>5) TRUE
=AND(5>3, 10<5) FALSE

OR Function

At least one condition must be TRUE

Formula Result
=OR(5>3, 10<5) TRUE
=OR(5<3, 10<5) FALSE
📊 Example: Loan Approval Criteria

Requirements: Credit Score ≥ 700 AND Income ≥ $50,000

Formula: =IF(AND(B2>=700, C2>=50000), "Approved", "Rejected")

Applicant Credit Score Income Status
Rahul 720 $55,000 Approved ✓
Priya 680 $60,000 Rejected ✗
Amit 750 $45,000 Rejected ✗
IFERROR Error Handling
=IFERROR(value, value_if_error)

Catches and handles errors gracefully. Essential for professional models that shouldn't display #N/A, #DIV/0!, etc.

📊 Example: Safe VLOOKUP

Without IFERROR:

=VLOOKUP("TSLA", A1:D5, 3, FALSE)

Result: #N/A (if TSLA not found)

With IFERROR:

=IFERROR(VLOOKUP("TSLA", A1:D5, 3, FALSE), "Not Found")

Result: Not Found

💡
Common Error Types:
  • #N/A - Value not found (VLOOKUP, MATCH)
  • #DIV/0! - Division by zero
  • #VALUE! - Wrong data type
  • #REF! - Invalid cell reference
  • #NAME? - Misspelled function name
Section 3

Date Functions

Work with dates for financial calculations and reporting

📅 Date & Time Functions
TODAY / NOW Current Date & Time
=TODAY() - Returns current date
=NOW() - Returns current date and time

These functions automatically update to show the current date/time. Essential for dynamic reports.

TODAY()

2026-03-04

Today's date only

NOW()

2026-03-04 23:16

Date and current time

⚠️
Note: These functions recalculate every time the workbook opens or changes. For static dates, use Ctrl+; (semicolon) to enter today's date as a fixed value.
DATE / YEAR / MONTH / DAY Date Components
=DATE(year, month, day)
=YEAR(date) | =MONTH(date) | =DAY(date)

Create dates from components or extract parts of a date. Useful for building dynamic date ranges.

📊 Examples
Formula Result Description
=DATE(2026, 3, 15) 3/15/2026 Create a specific date
=YEAR("3/15/2026") 2026 Extract year
=MONTH("3/15/2026") 3 Extract month
=DAY("3/15/2026") 15 Extract day
=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1) 4/1/2026 First day of next month
EDATE / EOMONTH Month Calculations
=EDATE(start_date, months) - Same day, different month
=EOMONTH(start_date, months) - End of month

Essential for financial modeling! Calculate maturity dates, payment due dates, and month-end dates.

📊 Financial Modeling Examples
Scenario Formula Result
Loan matures in 12 months =EDATE(TODAY(), 12) 3/4/2027
3 months ago =EDATE(TODAY(), -3) 12/4/2025
End of current month =EOMONTH(TODAY(), 0) 3/31/2026
End of next month =EOMONTH(TODAY(), 1) 4/30/2026
End of fiscal year (Dec) =EOMONTH(TODAY(), 12-MONTH(TODAY())) 12/31/2026
💰
Financial Modeling Use: These functions are crucial for calculating payment schedules, bond maturity dates, lease payments, and depreciation periods.
DATEDIF Date Difference (Hidden Gem)
=DATEDIF(start_date, end_date, unit)

Calculate the difference between two dates in years, months, or days. Note: This is a "hidden" function - it doesn't appear in Excel's function list!

Unit Returns Example
"Y" Complete years Age in years
"M" Complete months Loan tenure
"D" Days Days overdue
"YM" Months (ignoring years) Remaining months after years
📊 Example: Employee Tenure

Start Date: 1/15/2020 | End Date: TODAY()

Formula Result Meaning
=DATEDIF("1/15/2020", TODAY(), "Y") 6 6 complete years
=DATEDIF("1/15/2020", TODAY(), "YM") 1 + 1 additional month
=DATEDIF("1/15/2020", TODAY(), "D") 2,240 Total days employed
Section 4

Text Functions

Clean, combine, and format text data for analysis

📝 Text Functions
LEFT / RIGHT / MID Extract Text
=LEFT(text, num_chars) - Extract from start
=RIGHT(text, num_chars) - Extract from end
=MID(text, start_num, num_chars) - Extract from middle

Extract specific characters from text strings. Perfect for parsing IDs, codes, or formatted data.

📊 Example: Extracting Account Code Parts

Account Code: IND-FIN-2026-001

Extract Formula Result
Country Code (first 3) =LEFT("IND-FIN-2026-001", 3) IND
Serial Number (last 3) =RIGHT("IND-FIN-2026-001", 3) 001
Department (chars 5-7) =MID("IND-FIN-2026-001", 5, 3) FIN
Year (chars 9-12) =MID("IND-FIN-2026-001", 9, 4) 2026
LEN / FIND / SEARCH Text Length & Position
=LEN(text) - Length of text
=FIND(find_text, within_text, [start_num]) - Find position (case-sensitive)
=SEARCH(find_text, within_text, [start_num]) - Find position (case-insensitive)

Find the length of text or the position of specific characters. Often combined with MID for dynamic extraction.

📊 Example: Extract Domain from Email

Email: rahul.sharma@woxsen.edu

Step 1: Find @ position

=FIND("@", "rahul.sharma@woxsen.edu") → 13

Step 2: Extract domain (everything after @)

=MID("rahul.sharma@woxsen.edu", 14, LEN("rahul.sharma@woxsen.edu"))

→ woxsen.edu

CONCATENATE / TEXTJOIN Combine Text
=CONCATENATE(text1, text2, ...) or use & operator
=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)

Combine multiple text strings. TEXTJOIN is more powerful and can handle ranges with delimiters.

📊 Example: Building Full Names & Addresses
A B C
First Name Last Name Full Name
Rahul Sharma =A2 & " " & B2
Priya Patel =CONCATENATE(A3, " ", B3)

Results:

  • =A2 & " " & B2 → Rahul Sharma
  • =CONCATENATE(A3, " ", B3) → Priya Patel
💡 TEXTJOIN for Multiple Values

=TEXTJOIN(", ", TRUE, A1:A5)
Combines all non-empty cells in A1:A5 with ", " between them.

TRIM / CLEAN / Case Functions Clean & Format Text
=TRIM(text) - Remove extra spaces
=CLEAN(text) - Remove non-printable characters
=UPPER(text) | =LOWER(text) | =PROPER(text)

Clean messy data imported from other systems. Essential for data preparation before analysis.

📊 Data Cleaning Examples
Original Text Function Result
" Rahul Sharma " =TRIM(A1) "Rahul Sharma"
"RAHUL SHARMA" =PROPER(A2) "Rahul Sharma"
"rahul.sharma" =UPPER(A3) "RAHUL.SHARMA"
"RAHUL.SHARMA" =LOWER(A4) "rahul.sharma"
💡
Data Cleaning Combo:
=PROPER(TRIM(CLEAN(A1)))
This formula removes extra spaces, non-printable characters, and capitalizes each word properly!
TEXT Format Numbers as Text
=TEXT(value, format_text)

Convert numbers to formatted text. Essential for creating labels, reports, and concatenated strings with proper formatting.

📊 Formatting Examples
Value Formula Result
1234567.89 =TEXT(A1, "$#,##0.00") $1,234,567.89
0.1234 =TEXT(A2, "0.00%") 12.34%
44920 =TEXT(A3, "MMMM DD, YYYY") March 04, 2026
44920 =TEXT(A4, "DD-MMM-YY") 04-Mar-26

Practical Use - Dynamic Labels:

="Revenue as of " & TEXT(TODAY(), "MMMM DD, YYYY")

→ Revenue as of March 04, 2026

Excel Lab

Hands-On Practice

Apply what you've learned with these exercises

📥 Download Practice Files

Download the data files and practice guide to work through the exercises.

Practice Guide
📖 Download Practice Guide (MD)

The practice guide contains step-by-step instructions for all exercises with expected results.

View all practice files →

🎯 Practice Exercises

Exercise 1: Employee Database Lookup

Create a lookup system that:

  • Takes an Employee ID as input
  • Returns their Name, Department, and Salary
  • Shows "Not Found" if ID doesn't exist

Hint: Use XLOOKUP or INDEX-MATCH with IFERROR

Exercise 2: Loan Payment Schedule

Build a payment schedule that:

  • Calculates payment dates (monthly for 12 months)
  • Shows the day of each month (1st, 15th, or last day)
  • Flags weekends with "Payment Due on Friday" message

Hint: Use EDATE, EOMONTH, and IF with WEEKDAY

Exercise 3: Data Cleaning Challenge

Given messy customer data:

  • " JOHN DOE " → "John Doe"
  • "john.doe@email.com " → "john.doe@email.com"
  • Extract username and domain separately

Hint: Use TRIM, PROPER, and FIND with LEFT/MID

📚 Function Quick Reference - Click to Flip

Knowledge Check

Test Your Understanding

Let's see what you've learned!

Summary

Key Takeaways

📝 What We Covered Today

  • VLOOKUP - Search first column, return any column (use FALSE for exact match)
  • XLOOKUP - Modern replacement, more flexible, built-in error handling
  • INDEX-MATCH - Most flexible, works in all Excel versions, can look any direction
  • IF, AND, OR, IFERROR - Build conditional logic and handle errors gracefully
  • Date Functions - TODAY, EDATE, EOMONTH, DATEDIF for financial calculations
  • Text Functions - LEFT, RIGHT, MID, TRIM, PROPER, TEXT for data cleaning

📋 Quick Reference Card

Lookup Functions
  • VLOOKUP(val, table, col, FALSE)
  • XLOOKUP(val, range, return, "error")
  • INDEX(range, MATCH(val, range, 0))
Logical Functions
  • IF(test, true, false)
  • AND(cond1, cond2)
  • IFERROR(formula, "fallback")
Date Functions
  • TODAY() / NOW()
  • EDATE(date, months)
  • EOMONTH(date, months)
Text Functions
  • LEFT(text, n) / RIGHT(text, n)
  • TRIM(text) / PROPER(text)
  • TEXT(value, "format")
📚 Next Session

Lecture 3: Financial Statements Overview
We'll cover the three main financial statements - Income Statement, Balance Sheet, and Cash Flow Statement. Understanding how they interconnect is fundamental to building financial models.
Reading: Pignataro, Ch. 3–4