Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

INDEX & MATCH in Excel

Learn how INDEX and MATCH work together to retrieve information from Excel datasets. Practice exact lookups, row and column matching, two-way lookups, and dynamic searches using student records and course information.

Learning objectives: Understand INDEX and MATCH individually, combine them for lookups, use exact matching, retrieve values from a two-way table, and compare INDEX & MATCH with XLOOKUP.

1. What Is INDEX?

The INDEX function returns a value from a specified position within a range or array. You provide a row number and, optionally, a column number to identify the value to return.

Syntax
=INDEX(array, row_num, [column_num])

Example: Retrieve Student Marks

Assume student marks are stored in B2:B6. The first value belongs to Samadhan, the second to Samiksha, and so on.

Cell Student Marks Position in B2:B6
Row 2 Samadhan 85 1
Row 3 Samiksha 92 2
Row 4 Pradnya 76 3
Row 5 Omkar 68 4
Row 6 Neha 88 5
Excel Formula
=INDEX(B2:B6,2)
Result: 92

INDEX returns the second value in the specified range. The position is relative to B2:B6, not the worksheet row number.

2. What Is MATCH?

The MATCH function searches for a value in a single row or column and returns its relative position within that range. It returns a position, not the corresponding value from another column.

Syntax
=MATCH(lookup_value, lookup_array, [match_type])

The match_type argument controls the matching behavior:

Value Behavior
0 Find an exact match.
1 Find the largest value less than or equal to the lookup value. The lookup array must be sorted in ascending order.
-1 Find the smallest value greater than or equal to the lookup value. The lookup array must be sorted in descending order.

Example: Find Samiksha's Position

Excel Formula
=MATCH("Samiksha",A2:A6,0)

If the names are in A2:A6 in the order shown above, the formula returns 2 because Samiksha is the second item in that range.

Important: Use match_type 0 for an exact text or number match. The approximate modes require the lookup data to be sorted correctly.

3. Combine INDEX and MATCH

INDEX and MATCH become especially useful when combined. MATCH identifies the relative position of a value, and INDEX returns the value at that position from another range.

Lookup value: Samiksha MATCH Finds position 2 in A2:A6 INDEX Returns the second value from B2:B6 Final result: 92 marks

Diagram: MATCH finds the position; INDEX retrieves the corresponding value.

Formula

Excel Formula
=INDEX(B2:B6,MATCH("Samiksha",A2:A6,0))

Step-by-step calculation

  1. MATCH("Samiksha",A2:A6,0) finds Samiksha's position and returns 2.
  2. INDEX receives 2 as its row position.
  3. INDEX(B2:B6,2) returns the second value in B2:B6.
  4. The final result is 92.

4. Use a Cell Reference

A cell reference makes the formula reusable. Suppose E2 contains the student name you want to search for.

Excel Formula
=INDEX(B2:B6,MATCH(E2,A2:A6,0))

If E2 contains Neha, the formula returns 88. Change E2 to Omkar and it returns 68, without changing the formula.

Practical tip: Use absolute references such as $A$2:$A$6 and $B$2:$B$6 when copying formulas and keeping the lookup ranges fixed.
Formula with Absolute References
=INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))

5. Handle a Missing Match

If MATCH cannot find the lookup value, it returns #N/A. You can use IFERROR to display a more helpful message.

Excel Formula
=IFERROR(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Student Not Found")

If E2 contains a name that is not present in A2:A6, the formula displays Student Not Found.

Remember: IFERROR can also hide other errors. When a formula fails, check the source data and ranges instead of automatically assuming the student is missing.

6. Two-Way Lookup Using INDEX and MATCH

A two-way lookup retrieves a value based on both a row label and a column heading. For example, you may want to find a student's score for a particular subject.

Student Excel SQL Python
Samadhan 85 82 88
Samiksha 92 90 94
Pradnya 76 84 80
Omkar 68 72 75
Neha 88 86 91

Assume student names are in A2:A6, subject headers are in B1:D1, and marks are in B2:D6. Cell G2 contains a student name and H2 contains a subject name.

Excel Formula
=INDEX(B2:D6,MATCH(G2,A2:A6,0),MATCH(H2,B1:D1,0))

Step-by-step explanation

  1. The first MATCH finds the row position of the student.
  2. The second MATCH finds the column position of the subject.
  3. INDEX uses both positions to return the value at their intersection.
Example result: If G2 is Samiksha and H2 is Python, the formula returns 94.

7. INDEX and MATCH for Course Fees

The same technique can retrieve course fees from a list. Suppose course names are in A2:A5 and their fees are in B2:B5. Cell D2 contains the course to search for.

Course Fee (₹)
Advanced Excel 8,000
SQL Server 12,000
Core Java 15,000
Python 14,000
Excel Formula
=IFERROR(INDEX(B2:B5,MATCH(D2,A2:A5,0)),"Course Not Found")

If D2 contains SQL Server, the formula returns ₹12,000. These fee amounts are sample training data.

8. INDEX and MATCH vs XLOOKUP

Feature INDEX & MATCH XLOOKUP
Exact match Use MATCH with 0. Exact match is the default.
Return from another range INDEX returns the value at the matched position. Uses a return array.
Missing value message Can be handled with IFERROR or IFNA. Has an optional if_not_found argument.
Two-way lookup Combine row MATCH and column MATCH. Can also use nested XLOOKUP formulas.
Compatibility Available in many older Excel versions. Requires a supported modern Excel version.

Both methods are useful. INDEX and MATCH are especially valuable when learning position-based lookups and working with older Excel versions.

9. Common Mistakes and Solutions

Problem Cause Solution
#N/A MATCH cannot find the requested value. Check the lookup value, spelling, and lookup range.
Wrong result The INDEX return range does not correspond to the lookup range. Verify that the ranges are aligned correctly.
Unexpected approximate match MATCH uses 1 or -1 instead of 0. Use 0 for an exact match.
Formula changes after copying Relative references move when copied. Use absolute references where appropriate.
Wrong two-way result The row or column MATCH uses the wrong range. Verify both lookup ranges and the INDEX array.

10. Practical Exercises

Exercise 1: Student Marks

  1. Create a student table with names and marks.
  2. Use MATCH to find Samiksha's position.
  3. Use INDEX to retrieve the second mark.
  4. Combine INDEX and MATCH to retrieve Neha's marks.
  5. Use IFERROR to handle a missing student name.

Exercise 2: Two-Way Lookup

  1. Create a table of students and marks for Excel, SQL, and Python.
  2. Enter a student name in a separate cell.
  3. Enter a subject name in another cell.
  4. Retrieve the intersecting mark using INDEX and two MATCH functions.

Exercise 3: Course Fees

  1. Create a course fee list.
  2. Search for SQL Server using INDEX and MATCH.
  3. Test a course name that does not exist.
  4. Display Course Not Found when there is no exact match.

Important Points to Remember

  • INDEX returns a value from a specified position.
  • MATCH returns the relative position of a lookup value.
  • Use MATCH type 0 for exact matching.
  • Combine INDEX and MATCH to retrieve related information.
  • Use two MATCH functions for a two-way lookup.
  • Use IFERROR or IFNA to handle missing matches appropriately.
  • Use absolute references when copying formulas across cells.

Summary

In this lesson, you learned how INDEX returns values, how MATCH finds relative positions, and how to combine both functions for exact lookups, two-way lookups, and course fee searches.

INDEX and MATCH provide a flexible way to retrieve data from structured worksheets. In the next topic, we will learn about SUMIFS and COUNTIFS for conditional calculations and counting.