Google Sheets

How to Use XLOOKUP in Google Sheets

Use XLOOKUP in Google Sheets to return matching values from any direction, handle missing results, and avoid fragile VLOOKUP column indexes.

May 26, 2026/5 min read

XLOOKUP in Google Sheets searches one range and returns a matching value from another range. It is usually cleaner than VLOOKUP because the lookup column does not need to be the first column, and the return range does not need a fragile column index.

Use it for lead owners, invoice details, product prices, project metadata, category labels, or any sheet where one table needs to pull fields from another.

The syntax

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

search_key - the value you want to find
lookup_range - the single row or column to search
result_range - the row or column to return from
[missing_value] - optional value to show when no match exists
[match_mode] - optional match behavior, such as exact or wildcard
[search_mode] - optional search direction

For most operating sheets, use exact matches and provide a clear missing value.

Basic XLOOKUP example

Say column A contains company names and column C contains account owners. If E2 contains the company to search for, use:

=XLOOKUP(E2, A:A, C:C, "Not found")

That searches column A for the company in E2 and returns the owner from column C.

Unlike VLOOKUP, the return column does not need to sit to the right of the lookup column.

Return a value from the left

If column C contains invoice IDs and column A contains client names, XLOOKUP can search column C and return from column A:

=XLOOKUP(F2, C:C, A:A, "No client found")

That pattern is useful when a sales pipeline or invoice template needs to pull context from a table that was not arranged for VLOOKUP.

Use XLOOKUP in a CRM or project sheet

In a CRM lead tracker, you might search an account ID and return the current owner:

=XLOOKUP(A2, Leads!A:A, Leads!D:D, "Missing owner")

In a project tracker, you might search a task ID and return the workstream:

=XLOOKUP(B2, Tasks!A:A, Tasks!E:E, "Unassigned")

The formula stays readable because lookup and result ranges are named directly.

XLOOKUP vs VLOOKUP in Google Sheets

Need	Better choice
Return values to the left	XLOOKUP
Avoid column index numbers	XLOOKUP
Keep compatibility with older VLOOKUP-heavy files	VLOOKUP
Search simple tables already arranged left to right	Either

TIP

XLOOKUP is easier to maintain when columns get inserted, moved, or hidden because it points directly to the lookup and return ranges.

Common XLOOKUP issues

Issue	Cause	Fix
`Not found` appears for real values	Extra spaces or mismatched formats	Clean both lookup fields
Formula returns the wrong row	Duplicate lookup keys	Use a truly unique ID
Formula expands poorly	Full-column ranges over very large files	Use bounded ranges when performance matters
Wildcards do not work	Match mode not set for wildcard matching	Use match mode `2`

The Griddy way

XLOOKUP is stronger than VLOOKUP, but it still depends on choosing the right lookup key and result range.

"Use the account ID to pull owner, stage, and next follow-up date into this Google Sheets pipeline"

Griddy can find the columns, write the lookups, and avoid brittle formulas that break when the table changes.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Use cleaner lookups in live operating sheets

XLOOKUP is a strong fit for CRM, pipeline, invoice, and project templates where lookup and return columns may move over time.

Sales

All posts