XLOOKUP function in Google Sheets - Step-by-Step Guide

Introduction

XLOOKUP helps you find a value in a list or table and get a matching value from another list or table. It solves the problem of searching for data quickly without scrolling or manual searching.

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

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

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

When you want to replace old lookup formulas that are harder to use.

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 with =XLOOKUP(

Step 3: Type

- formula bar or cell

Formula includes lookup value

💡 Example: =XLOOKUP("Apple",

Step 4: Type

- formula bar or cell

Formula includes lookup array (where to search)

💡 Example: =XLOOKUP("Apple", A2:A10,

Step 5: Type

- formula bar or cell

Formula includes return array (what to get)

💡 Example: =XLOOKUP("Apple", A2:A10, B2:B10)

Step 6: Press

- Enter key

Cell shows the matching value from return array

Before vs After

Before

Cell shows no value or manual search needed to find price for 'Apple'

After

Cell shows 1.20 which is the price for 'Apple' found automatically by XLOOKUP

Settings Reference

lookup_value

📍 first argument in XLOOKUP formula

The value you want to find in the lookup array

Default: none

lookup_array

📍 second argument in XLOOKUP formula

The range where Google Sheets searches for the lookup value

Default: none

return_array

📍 third argument in XLOOKUP formula

The range from which Google Sheets returns the matching value

Default: none

if_not_found

📍 fourth argument in XLOOKUP formula (optional)

Value to show if lookup value is not found

Default: "#N/A" error

match_mode

📍 fifth argument in XLOOKUP formula (optional)

How to match the lookup value

Default: 0

Common Mistakes

Using ranges of different sizes for lookup_array and return_array

XLOOKUP requires both ranges to be the same size to match correctly

Make sure lookup_array and return_array cover the same number of rows or columns

Not putting text lookup_value in quotes

Text values must be in quotes or referenced from a cell, otherwise formula errors

Use quotes around text like "Apple" or use a cell reference like A1

Forgetting to add the return_array argument

XLOOKUP needs the return array to know what to show as result

Always include the third argument with the range of values to return

Summary

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

It is easier and more flexible than older lookup functions.

Make sure lookup and return ranges are the same size for correct results.