Google Sheets XLOOKUP Match Mode: Practical Guide

What is XLOOKUP and match_mode in Google Sheets

Google Sheets supports XLOOKUP as a flexible replacement for traditional lookups. The match_mode parameter controls how the function interprets the lookup_key against the lookup_array. Available modes include 0 for exact matches, -1 for exact matches or next smaller values, 1 for exact matches or next larger values, and 2 for wildcard matching. When you combine match_mode with search_mode, you can decide whether the lookup searches from top to bottom or bottom to top. This combination makes XLOOKUP particularly powerful for datasets with gaps, inconsistencies, or patterns. In this guide we focus on how to use google sheets xlookup match mode effectively, with practical examples you can adapt in your own sheets. The goal is to give you a solid mental model and robust formulas you can reuse in real-world projects.

Exact-match example: straightforward lookups

Exact matching is the most common scenario when keys are unique and the data is well-structured. In Google Sheets, you provide the lookup_key, a lookup_array, and a return_array. The optional if_not_found argument helps keep your sheet clean when there is no match. Finally, the match_mode argument is set to 0 to require an exact match. This setup is ideal for inventory SKUs, student IDs, or any dataset where a precise key exists. Below is a minimal, copy-pasteable example that you can test in your own sheet.

• A2: lookup key in the left column
• A2:A10: lookup_array where to search
• B2:B10: return_array with corresponding results
• "Not found": fallback if no match
• 0: exact match only

If you prefer a named range, you can replace A2:A10 with a named range like Keys and Returns with ReturnCol.

Nearest-match examples: -1 and 1

In cases where the exact key might be missing, match_mode -1 or 1 helps you retrieve the nearest value either below or above the search key. This is especially useful in pricing tiers or grade thresholds where an exact row may not exist. Sorting the lookup_array is important for predictable results. We’ll show two formulas side-by-side and explain the behavior.

• -1: exact match or next smaller value (requires the lookup_range to be sorted ascending for predictable results)
• 1: exact match or next larger value (same sorting caveat)

If your data isn’t sorted, consider wrapping XLOOKUP with a sort or using an alternative approach to guarantee deterministic results.

Wildcard matching: using match_mode 2

Wildcard matching allows flexible lookups when keys follow patterns rather than exact values. Use * to represent any number of characters and ? for a single character. This is handy for names, codes, or product catalogs with consistent prefixes or suffixes. Note that wildcard searches are text-based, so numeric fields won’t respond to wildcards in the same way. The example below demonstrates a common pattern.

• "Jo*": wildcard pattern for names starting with Jo
• match_mode 2: enables wildcard matching

Tip: When using wildcards, ensure data is text; numeric data will not match as expected.

Practical tips and pitfalls

This section covers best practices and common mistakes as you work with XLOOKUP match mode in Google Sheets. Use IFNA or IFERROR to handle missing values gracefully so end users don’t see #N/A or #VALUE errors. For example:

Be mindful of data sorting when using -1 or 1; the nearest-match behavior depends on the sort order of the lookup_array. If duplicates exist, XLOOKUP will return the first match it encounters unless you implement a different lookup strategy. For large datasets, consider limiting the lookup range to improve performance and maintainability.

Advanced variants: named ranges and performance tips

Leverage named ranges to simplify formulas and readability. For instance, define Keys for A2:A10 and Values for B2:B10, then rewrite the formula as a cleaner reference:

If your data changes frequently, caching results with volatile functions can help—but be cautious about staleness. You can also combine XLOOKUP with other logic, such as IF, ISNA, or FILTER, to build robust data pipelines in sheets that act as lightweight databases.