Data Analytics FULL Course 2026 with GenAI | Part 07

In Data Analytics FULL Course 2026 – Part 07, you move into advanced Excel lookup and text-handling functions that are extremely important for data cleaning, data extraction, and interview preparation.

This lecture focuses on two major categories:

1. MATCH & INDEX functions (lookup logic)
2. REGEX functions (text pattern extraction, validation & replacement)

These functions help you work with real-world messy data, where simple formulas are not enough.

Functions Covered in Part 07

In this lecture, you learn 6 important advanced Excel functions:

🔹 Lookup Functions

1. MATCH
2. INDEX
3. INDEX + MATCH (combined)

🔹 REGEX (Text Pattern) Functions

4. REGEXEXTRACT
5. REGEXREPLACE
6. REGEXMATCH / REGEXTEST

These functions are commonly used in:

• Data cleaning
• HR & sales analysis
• Email & phone validation
• Interview questions
• Automation tasks

MATCH Function – Find the Position of a Value

What Does MATCH Do?

The MATCH function finds the position of a value inside a range.

Syntax:

=MATCH(lookup_value, lookup_array, match_type)

• 0 → Exact match (most common)
• 1 → Less than
• -1 → Greater than

Example:

Find the position of “Niha” in the Employee Name column:

=MATCH("Niha", B2:B9, 0)

✅ Output: 5

📌 MATCH does not return the value, it returns the position number.

INDEX Function – Get a Value from Row & Column

What Does INDEX Do?

The INDEX function returns a value based on:

• Row number
• Column number

Syntax:

=INDEX(array, row_number, column_number)

Example:

Find the value at Row 6, Column 2:

=INDEX(A1:F9, 6, 2)

✅ Output: Vikram

📌 INDEX is powerful when you already know the position.

INDEX + MATCH – The Most Important Interview Function

Why Combine INDEX & MATCH?

• MATCH finds the position
• INDEX returns the value from that position

This combination replaces VLOOKUP limitations and is a common interview question.

Example: Find Salary Using Employee ID

Question:
Find the salary of employee with ID E109

Formula:

=INDEX(D2:D8, MATCH("E109", A2:A8, 0))

✅ Output: 75,000

📌 This works like XLOOKUP but is more flexible.

Dynamic INDEX MATCH Example

If Employee ID is entered in a cell:

=INDEX(Salary, MATCH(EmployeeID_Cell, EmployeeID_Column, 0))

You can change the ID and results update automatically.

REGEX Functions – Advanced Text Handling in Excel

REGEX functions are used when:

• Data is unstructured
• Text patterns vary
• Cleaning is required

These are advanced functions, but extremely powerful.

REGEXEXTRACT – Extract Text Using a Pattern

Use Case 1: Extract Username from Email

From:

rahul.sharma@gmail.com

Extract:

rahul.sharma

Formula:

=REGEXEXTRACT(A2,"^[^@]+")

📌 Best use cases:

• Username extraction
• Domain extraction
• IDs from text

Use Case 2: Extract Email Domain

=REGEXEXTRACT(A2,"@(.+)$")

✅ Output:

gmail.com

REGEXREPLACE – Remove or Replace Patterns

Use Case: Clean Phone Numbers

Remove:

• +
• spaces

Formula:

=REGEXREPLACE(A2,"[^0-9]","")

✅ Output: Clean numeric phone number

📌 Used heavily in data cleaning projects.

REGEXMATCH – Check If Pattern Exists (TRUE/FALSE)

Use Case 1: Check Gmail Email

=REGEXMATCH(A2,"gmail.com")

✅ Returns: TRUE / FALSE

Use Case 2: Check If Name Has Space

=REGEXMATCH(A2," ")

Useful to detect first + last name format.

REGEXTEST – Pattern Validation (New Function)

REGEXTEST is used to validate data formats.

Validate Indian Mobile Number (+91)

=REGEXTEST(A2,"^\+91")

TRUE → Indian number
FALSE → Not Indian number

Validate Email Ending with .com

=REGEXTEST(A2,"\.com$")

Why REGEX Functions Are Important for Data Analysts

REGEX helps you:

• Clean messy real-world data
• Validate emails & phone numbers
• Extract required information
• Reduce manual effort
• Work efficiently with AI tools like ChatGPT

📌 Tip:
No one memorizes REGEX patterns — even professionals use ChatGPT to generate patterns.

MATCH vs INDEX vs XLOOKUP (Quick Comparison)

📌 Learn all — use XLOOKUP in work, INDEX MATCH in interviews.

What’s Coming Next?

In the next lecture, you’ll learn:

• Excel Statistical Functions
• Mean, Median, Variance
• Real business use cases
• Analytical thinking in Excel

After that:

• Data Cleaning
• Charts & Dashboards
• SQL starts from Part 11

Download Dataset