How to Master Data Lookup in Excel with XLOOKUP: A Comprehensive Guide

Microsoft Excel is a powerhouse when it comes to organizing and analyzing data, and one of its most powerful functions for data lookup is XLOOKUP. If you’ve been using VLOOKUP or HLOOKUP and find yourself running into limitations, XLOOKUP is here to revolutionize the way you search for and retrieve data in your spreadsheets. In this article, we’ll explore the ins and outs of XLOOKUP, understand its syntax, and delve into practical examples to illustrate its versatility.

Table of Contents

1. Understanding the Basics of XLOOKUP.

1.1 Syntax.

Below is the XLOOKUP function syntax.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

1.2 Parameters.

`lookup_value`: The value to search for.
`lookup_array`: The range to search for the lookup_value.
`return_array`: The range from which to return a corresponding value.
`[if_not_found]`: The value to return if no match is found (optional).
`[match_mode]`: Specifies the type of match (optional).
`[search_mode]`: Specifies the search order (optional).

2. Excel XLOOKUP Examples.

2.1 Example 1: Basic XLOOKUP.

Let’s start with a straightforward example. Suppose you have a list of products and their prices, and you want to find the price of a specific product using its name.
```
Product,| Price
-------------------
Laptop,| 800
Smartphone,| 500
Tablet,| 300
Desktop,| 1000
Monitor,| 200
Keyboard,| 50
Mouse,| 30
Printer,| 150
Headphones,| 80
```
Below is the formula which uses XLOOKUP to implement it.
```
= XLOOKUP("Laptop", A2:A10, B2:B10)
```
In this formula:
`lookup_value` is “Laptop“.
`lookup_array` is the range A2:A10 (containing product names).
`return_array` is the range B2:B10 (containing corresponding prices).
If “Laptop” is in the list, XLOOKUP will return the price associated with it.

2.2 Example 2: Handling Errors with XLOOKUP.

XLOOKUP allows you to handle errors more gracefully than its predecessors.
Suppose you want to display a custom message when a product is not found.
```
= XLOOKUP("Tablet", A2:A10, B2:B10, "Product Not Found")
```
In this modified formula, if “Tablet” is not in the list, XLOOKUP will return the custom message “Product Not Found” instead of an error.

2.3 Example 3: Approximate Match with XLOOKUP.

Unlike VLOOKUP, XLOOKUP supports both exact and approximate matches.
Let’s say you have a list of grades and corresponding grade boundaries, and you want to find the grade for a specific score.

Below is the example dataset.

Grade,| Grade Boundary
-------------------
A,| 90
B,| 80
C,| 70
D,| 60
F,| 0

Below is the formula.

= XLOOKUP(75, D2:D6, C2:C6, "Not Found", 1)

In this example:
`lookup_value` is 75 (the score to search for).
`lookup_array` is the range D2:D6 (containing grade boundaries).
`return_array` is the range C2:C6 (containing corresponding grades).
`[if_not_found]` is “Not Found“.
`[match_mode]` is 1, indicating an approximate match.

2.4 Example 4: Two-Way Lookup with XLOOKUP.

While VLOOKUP and HLOOKUP are limited to one-dimensional searches, XLOOKUP shines in two-way lookups.
Let’s consider a table of sales data where you want to find the sales amount for a specific product and month.

Example data set.

Product,| Jan,| Feb,| Mar,| Apr,| May
-------------------
Laptop,| 100,| 120,| 130,| 110,| 95
Smartphone,| 80,| 90,| 85,| 100,| 75
Tablet,| 50,| 60,| 55,| 70,| 65
Desktop,| 120,| 130,| 110,| 100,| 105
Monitor,| 30,| 25,| 35,| 40,| 45
Keyboard,| 10,| 15,| 20,| 25,| 30
Mouse,| 5,| 8,| 7,| 10,| 12
Printer,| 40,| 35,| 45,| 50,| 55
Headphones,| 25,| 30,| 28,| 35,| 32

Formula.

= XLOOKUP("Laptop", A2:A10 & B1:F1, B2:F10)

In this formula:
`lookup_value` is “Laptop“.
`lookup_array` combines the product names in A2:A10 with the month names in B1:F1.
`return_array` is the entire range B2:F10 (containing sales data).
This formula will return the sales amount for “Laptop” in the respective month.

2.5 Example 5: XLOOKUP with Wildcards.

XLOOKUP supports wildcard characters for more flexible searches.
Suppose you have a list of countries, and you want to find the capital city, but the country names might be entered with variations.

Example dataset.

Country,| Capital
-------------------
United States,| Washington
United Kingdom,| London
France,| Paris
Germany,| Berlin
Canada,| Ottawa
Australia,| Canberra
Japan,| Tokyo
India,| New Delhi
China,| Beijing