Excel XLOOKUP and Lookup Formulas Manual: Syntax, Examples, Errors, and Reusable Templates

Overview

XLOOKUP searches one range or array and returns the related value from another range or array. A basic formula looks like this:

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

The first three arguments are the foundation:

• lookup_value: the item you want to find, such as an employee ID or product code.
• lookup_array: the row or column Excel should search.
• return_array: the row or column containing the result.

The optional arguments control what happens when there is no match, whether Excel uses an exact or approximate match, and the direction of the search. For most business lists, begin with an exact match and add the optional arguments only when the requirement is clear.

For example, if product codes are in A2:A100, product names are in B2:B100, and the code to find is in E2, use:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found")

This is often easier to audit than a long nested formula because the search and result ranges are visible in one place. Before building the formula, confirm that the lookup values are comparable: text should not contain unintended spaces, and numbers should not be stored as text in one range while being numeric in another.

Checklist by scenario

Exact-match lookup

1. Identify a unique lookup key, such as an ID, ticket number, or SKU.
2. Place the key to search in a clearly labeled cell.
3. Select the complete lookup range and the matching return range.
4. Use an explicit missing-value message.
5. Test the formula with a known match and a value that should not match.

Template:

=XLOOKUP(A2,Data!$A$2:$A$500,Data!$D$2:$D$500,"No record")

Absolute references help when you copy the formula down. If the source is an Excel Table, structured references can make the formula easier to read, for example =XLOOKUP([@ID],Employees[ID],Employees[Department],"No record").

Returning more than one column

XLOOKUP can return a range containing several adjacent columns. If the formula is entered in a suitable blank area, the results may populate neighboring cells:

=XLOOKUP(E2,A2:A100,B2:D100,"No match")

Use this when one key should return several related fields, such as a customer name, region, and account status. Check the cells to the right before entering the formula so existing data does not block the result.

Handling missing values

The fourth argument controls the result when no match exists:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found")

You can return a blank with "", but a clear message is usually safer during audits because it distinguishes a missing record from an intentionally empty source cell. Avoid wrapping every lookup in IFERROR before you understand the cause of the error; a broad error handler can hide a damaged range or misspelled reference.

Partial-match lookup

For a wildcard match, set the match mode to 2. A formula that finds a value containing the text in E2 is:

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"No match",2)

Use wildcards deliberately. An asterisk represents any sequence of characters, while a question mark represents one character. Partial matches can return an unexpected row when several records contain similar text, so exact IDs are preferable where available.

Multiple criteria

When no single column is unique, combine logical tests inside XLOOKUP:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"No match")

Here, the formula searches for the row where column A equals E2 and column B equals F2, then returns the value from column C. Label the input cells clearly and test combinations that should produce no result. For larger or more complex extracts, FILTER may be more suitable because it can return every matching row rather than only one result.

Choosing among common lookup functions

What to double-check

• Range size: The lookup and return arrays should cover corresponding rows or columns. Mismatched ranges can produce incorrect results or an error.
• Data type: A number stored as text may not match a true number. Use consistent formatting and inspect suspicious values with simple checks such as ISTEXT or ISNUMBER.
• Extra spaces: Imported data may contain leading or trailing spaces. Clean a helper column with TRIM when appropriate, while remembering that unusual nonbreaking spaces may require additional cleaning.
• Duplicate keys: XLOOKUP returns one matching result. If duplicates are possible, decide whether the first match is acceptable or whether you need FILTER, a duplicate report, or a more specific key.
• Spill space: A formula returning several cells needs an unobstructed area. Move or clear blocking content only after confirming it is safe to do so.
• Approximate-match order: If you use an approximate mode, verify that the source data is arranged as required for that lookup method. Exact match is the safer default when the data is not intentionally ordered.

For a #N/A result, first check whether the lookup value actually exists, then compare data types, spaces, punctuation, and spelling. For a #SPILL! error, inspect the cells where the result needs to appear and remove or relocate blocking content if appropriate. For a formula that displays as text, check whether the cell is formatted as Text or whether the formula begins with an apostrophe.

Common mistakes

A frequent mistake is using a return range that does not line up with the lookup range. Another is copying a formula without locking source ranges, causing them to shift down or sideways. Use dollar signs for fixed ranges, or convert stable datasets into Tables with named columns.

Do not use a partial match simply because an exact match returns #N/A. That error may indicate a hidden space, inconsistent identifier, or incorrect reference. Fix the source data or formula first. Also avoid using a whole-column reference automatically in a heavily used workbook; a defined table or bounded range can make the model easier to inspect and maintain.

When replacing VLOOKUP with XLOOKUP, test the old and new formulas against known records, missing records, duplicates, and changed column layouts. A formula can be syntactically valid while still returning the wrong business field.

When to revisit

Revisit this checklist before seasonal planning cycles, recurring reports, budget updates, inventory refreshes, or any workflow where source columns and identifiers change. It is also worth reviewing lookup formulas when a workbook is handed to another person, moved between Excel environments, or updated after a data import process changes.

Before finalizing a workbook, run this short review:

1. Test one valid exact match.
2. Test one missing value and confirm the message is useful.
3. Check for duplicates in the lookup key.
4. Verify that source and return ranges align.
5. Inspect any #N/A, #VALUE!, or #SPILL! result instead of hiding it.
6. Document unusual match modes, helper columns, or cleaning steps.

Keep a small formula reference sheet with the approved templates used by your team. When Excel features, workbook structures, or import workflows change, update that sheet and retest representative records. For a broader method for diagnosing spreadsheet and software problems, see the Software Troubleshooting Guide. If your workbook supports technical operations, the guides to SSH keys and Docker Compose troubleshooting provide related configuration checklists.