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

XLOOKUP in Excel

Learn how to find values in Excel tables using XLOOKUP. This lesson covers exact matching, custom not-found messages, horizontal and vertical lookups, reverse searches, and practical student and course fee examples.

Learning objectives: Understand XLOOKUP syntax, search for a student's marks, retrieve course fees, handle missing records, and use match and search modes.

1. What Is XLOOKUP?

XLOOKUP searches for a value in one range and returns the corresponding value from another range. It can search vertically or horizontally and uses exact matching by default.

For example, a trainer at CIIT Training Institute can search for a student's name and retrieve that student's marks without manually searching through every row.

XLOOKUP: Find a Student's Marks Student Records Samadhan 85 Samiksha 92 Pradnya 76 Lookup value Samiksha Returned marks 92 Search the name → Find the matching row → Return the marks Illustrative example for Excel training

Illustration: XLOOKUP finds the requested student and returns the corresponding marks.

2. XLOOKUP Syntax

Excel Formula
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Argument Meaning Example
lookup_value The value you want to find. "Samiksha"
lookup_array The range in which Excel searches. A2:A6
return_array The range from which Excel returns a value. B2:B6
if_not_found Optional result if no match is found. "Not Found"
match_mode Controls exact or approximate matching. 0
search_mode Controls the search direction or method. 1
Important: The square brackets indicate optional arguments in the syntax. Do not type those brackets into the formula itself. XLOOKUP is available in supported modern Excel versions, including Microsoft 365 and Excel 2021 or later.

3. Basic XLOOKUP Example

Assume student names are in A2:A6 and marks are in B2:B6. You want to find Samiksha's marks.

Cell Student Name Marks
Row 2 Samadhan 85
Row 3 Samiksha 92
Row 4 Pradnya 76
Row 5 Omkar 68
Row 6 Neha 88

Formula

Excel Formula
=XLOOKUP("Samiksha",A2:A6,B2:B6)

Step-by-step explanation

  1. "Samiksha" is the value Excel must find.
  2. A2:A6 is the range containing student names.
  3. B2:B6 is the range containing marks.
  4. Excel finds Samiksha in the second data row.
  5. Excel returns the corresponding value from column B: 92.

4. Use a Cell Reference Instead of Typed Text

Instead of typing a student name directly into the formula, you can enter the name in another cell. This makes the lookup easier to reuse.

Suppose E2 contains the name Samiksha.

Excel Formula
=XLOOKUP(E2,A2:A6,B2:B6)

When you change E2 to Neha, the formula returns 88. The formula itself does not need to be rewritten.

5. Handle Missing Values

If a lookup value is not found, XLOOKUP normally returns #N/A. You can provide a custom message using the optional fourth argument.

Excel Formula
=XLOOKUP(E2,A2:A6,B2:B6,"Student Not Found")

If E2 contains a name that does not appear in A2:A6, Excel displays Student Not Found instead of #N/A.

Remember: A custom not-found message handles a missing match. It does not automatically resolve every other possible formula error.

6. Retrieve Course Fees

XLOOKUP is useful beyond student marks. A training institute can use it to retrieve course fees from a course list.

Course Fee (₹)
Advanced Excel 8,000
SQL Server 12,000
Core Java 15,000
Python 14,000

Assume course names are in A2:A5, fees are in B2:B5, and D2 contains the course name to search.

Excel Formula
=XLOOKUP(D2,A2:A5,B2:B5,"Course Not Found")

If D2 contains SQL Server, the formula returns ₹12,000. The fee values are illustrative training examples.

7. Match Modes

The match_mode argument determines how XLOOKUP matches the lookup value.

Value Behavior
0 Exact match. This is the default.
-1 Exact match or the next smaller item.
1 Exact match or the next larger item.
2 Wildcard match using supported wildcard characters.

Example: Exact Match

Excel Formula
=XLOOKUP(E2,A2:A6,B2:B6,"Not Found",0)

This explicitly requests an exact match.

Example: Wildcard Match

If the lookup value in E2 is a partial student name such as Sam*, wildcard matching can find a name beginning with Sam.

Excel Formula
=XLOOKUP(E2,A2:A6,B2:B6,"Not Found",2)

In wildcard mode, * matches any number of characters and ? matches one character. A tilde can be used to escape wildcard characters when searching for them literally.

8. Search Modes and Reverse Search

The search_mode argument controls the direction in which Excel searches the lookup range.

Value Behavior
1 Search from first item to last. This is the default.
-1 Search from last item to first.
2 Binary search on ascending-sorted data.
-2 Binary search on descending-sorted data.

Example: Find the Last Matching Record

If the same student appears multiple times in a list and you want the last matching occurrence, use reverse search.

Excel Formula
=XLOOKUP(E2,A2:A10,B2:B10,"Not Found",0,-1)

Excel searches from the bottom of the specified lookup range toward the top and returns the corresponding value from the return range.

Important: Binary search modes require correctly sorted data. If the data is not sorted as required, the result may be incorrect.

9. Common XLOOKUP Mistakes

Problem Possible Cause Solution
#N/A The lookup value is missing or does not match. Check spelling, spaces, and the lookup range.
Wrong returned value The return range does not correspond to the lookup range. Check the row or column alignment of both ranges.
#NAME? The Excel version may not support XLOOKUP or the function is misspelled. Verify the Excel version and formula spelling.
Unexpected approximate match A non-default match mode was used. Use match_mode 0 for exact matching.
Incorrect binary search result The lookup range is not sorted correctly. Sort the data as required or use a normal search mode.

10. Practical Exercises

Exercise 1: Student Marks

  1. Create a table containing student names and marks.
  2. Use XLOOKUP to find Samadhan's marks.
  3. Use a cell reference to search for Neha.
  4. Display Student Not Found for an unknown name.

Exercise 2: Course Fees

  1. Enter four course names and their fees.
  2. Search for SQL Server and retrieve its fee.
  3. Change the lookup value to Python.
  4. Test the formula with an invalid course name.

Exercise 3: Reverse Search

  1. Create a list where one student's name appears more than once.
  2. Assign a different score to each occurrence.
  3. Use search_mode -1 to return the last matching score.
  4. Compare the result with the default search mode.

Important Points to Remember

  • XLOOKUP searches one range and returns a corresponding value from another.
  • Exact matching is the default behavior.
  • Use the if_not_found argument to display a custom message.
  • Use cell references to make lookup formulas reusable.
  • Match modes support exact, approximate, and wildcard matching.
  • Reverse search finds the last matching occurrence.
  • Binary search modes require appropriately sorted data.

Summary

In this lesson, you learned how XLOOKUP searches for values, returns matching results, handles missing records, and uses match and search modes. You practiced retrieving student marks and course fees using practical Excel formulas.

XLOOKUP makes data retrieval easier and more flexible. In the next topic, we will learn about INDEX and MATCH, another powerful lookup technique used in Excel.