Introduction

Looking for a hands-on way to master XLOOKUP? This XLOOKUP practice exercises XLS download gives you 20 real-world problems with solutions in a single Excel file. Perfect for students, professionals, and anyone upgrading from VLOOKUP or HLOOKUP.

For years, Excel users relied on VLOOKUP and HLOOKUP to search data. But these functions had limitations—like not being able to look to the left, breaking when columns were inserted, and requiring exact match settings.

Enter XLOOKUP: a modern replacement introduced in Excel 365 and Excel 2021. It is more powerful, flexible, and easier to use.

In this guide, you’ll get:

  • A clear explanation of XLOOKUP.
  • 20 real-world practice exercises.
  • A free downloadable XLOOKUP Practice Excel File (XLS) with problems and solutions.
XLOOKUP Practice Exercises XLS Download

How XLOOKUP Works

The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value → The value you want to search.
  • lookup_array → The column or row where you search.
  • return_array → The column or row from which to return results.
  • if_not_found → What to show if no match is found.
  • match_mode → Exact match (default), approximate, or wildcard.
  • search_mode → Search from first to last or reverse.

Unlike VLOOKUP, XLOOKUP works both vertically and horizontally, can look left, and has built-in error handling.

Example Dataset (in the practice file)

Employee IDNameDepartmentSalaryLocation
101AliceHR50,000New York
102BobFinance60,000Chicago
103CharlieIT70,000San Francisco
104DianaMarketing55,000Boston
105EthanSales65,000Seattle

Basic Lookups

  1. Find the Department of Employee ID 103.
    =XLOOKUP(103, A2:A6, C2:C6) → IT
  2. Find the Salary of “Bob”.
    =XLOOKUP("Bob", B2:B6, D2:D6) → 60,000
  3. Get the Location of Employee ID 105.
    =XLOOKUP(105, A2:A6, E2:E6) → Seattle
  4. Return the Employee Name who works in “Marketing”.
    =XLOOKUP("Marketing", C2:C6, B2:B6) → Diana
  5. If Employee ID 106 is searched, return “Not Found”.
    =XLOOKUP(106, A2:A6, B2:B6, "Not Found") → Not Found

Error Handling & Wildcards

  1. Find Department of Employee ID 108 (missing data).
    =XLOOKUP(108, A2:A6, C2:C6, "Not Available") → Not Available
  2. Find Employee Name starting with “Ch” (wildcard).*
    =XLOOKUP("Ch*", B2:B6, B2:B6, "No Match", 2) → Charlie
  3. *Find Location ending with “ton” (wildcard).
    =XLOOKUP("*ton", E2:E6, E2:E6, "No Match", 2) → Boston

Horizontal Lookup (HLOOKUP replacement)

  1. Find Employee ID for “Ethan” (row lookup).
    =XLOOKUP("Ethan", B2:B6, A2:A6) → 105
  2. Find Department for “Alice”.
    =XLOOKUP("Alice", B2:B6, C2:C6) → HR

Multiple Scenarios

  1. Get Salary of Employee ID in cell G2 (dynamic).
    =XLOOKUP(G2, A2:A6, D2:D6, "Invalid ID")
  2. Return Employee Name if Location = “Chicago”.
    =XLOOKUP("Chicago", E2:E6, B2:B6) → Bob
  3. Find Employee ID for Department “Sales”.
    =XLOOKUP("Sales", C2:C6, A2:A6) → 105
  4. If searching for “Zoe”, return custom text.
    =XLOOKUP("Zoe", B2:B6, D2:D6, "Employee not found") → Employee not found
  5. Find Department of Employee with the lowest Salary.
    =XLOOKUP(MIN(D2:D6), D2:D6, C2:C6) → HR
  6. Find Location of Employee with highest Salary.
    =XLOOKUP(MAX(D2:D6), D2:D6, E2:E6) → San Francisco

These 20 problems cover basic lookups, wildcards, error handling, reverse lookups, approximate matches, and horizontal lookups — everything you need to master XLOOKUP.

Why Use This XLOOKUP Practice XLS?

These 20 problems cover:

  • Basic lookups
  • Wildcards
  • Error handling
  • Reverse lookups
  • Approximate matches
  • Horizontal lookups

With this XLOOKUP practice exercises XLS download, you’ll quickly move from beginner to confident Excel pro.

Ready to start? Download the free practice file here and begin mastering XLOOKUP today.