XLOOKUP function in Excel - Step-by-Step Guide

Introduction

XLOOKUP helps you find information in a table quickly. It looks for a value you want and returns matching data from another column. This saves time compared to searching manually.

When you want to find a product price by entering its name.

When you need to get a student's grade by typing their ID number.

When you want to match employee names to their phone numbers.

When you want to replace older lookup formulas with a simpler one.

When you want to find data even if it is not sorted.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for typing

Step 2: Type

- the selected cell

Formula input starts

💡 Start typing =XLOOKUP(

Step 3: Enter the lookup value

- inside the parentheses after =XLOOKUP(

The first argument is set

💡 This is the value you want to find, like a product name or ID

Step 4: Type a comma and select the lookup array

- inside the formula after the lookup value

The second argument is set

💡 This is the column or range where Excel looks for the value

Step 5: Type a comma and select the return array

- inside the formula after the lookup array

The third argument is set

💡 This is the column or range where Excel gets the matching result

Step 6: Close the parentheses and press Enter

- at the end of the formula

The formula runs and shows the matching result in the cell

Before vs After

Before

You have a list of product names in column A and prices in column B. You want to find the price of 'Apple'.

After

In a cell, you type =XLOOKUP("Apple", A:A, B:B) and it shows the price of Apple from column B.

Settings Reference

match_mode

📍 4th argument in XLOOKUP formula

Controls how Excel finds the lookup value

Default: 0

search_mode

📍 5th argument in XLOOKUP formula

Controls the direction and method of search

Default: 1

if_not_found

📍 optional 4th argument in XLOOKUP formula

Value to return if lookup value is not found

Default: Error if not found

Common Mistakes

Using XLOOKUP with unsorted data but setting search_mode to binary search.

Binary search requires sorted data and can give wrong results if data is not sorted.

Use the default search_mode (1) for unsorted data.

Not closing the parentheses in the formula.

Excel shows an error and does not calculate the formula.

Always close the formula with a closing parenthesis ) before pressing Enter.

Selecting wrong ranges for lookup_array and return_array with different sizes.

XLOOKUP requires both ranges to have the same number of rows or columns.

Make sure both lookup_array and return_array cover the same number of cells.

Summary

XLOOKUP finds a value in one column and returns a matching value from another column.

It works with unsorted data and can replace older lookup formulas.

Remember to match the sizes of lookup and return ranges and close the formula properly.