Quick answer
The XLOOKUP formula looks for a value in one column or row and returns the matching value from another column or row.
=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"Not found")Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])When to use it
Lookup formulas connect tables together. Dynamic array formulas go further by returning lists, tables, and spill ranges that update automatically when the source data changes.
- Return a price from a product table
- Find an employee name from an ID
- Filter open orders into a report
- Create sorted or unique lists
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Lookup value such as SKU, ID, or name |
| F:G | Reference table with the match column and return column |
| B | Returned result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Find a price by SKU
=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"Not found")Look up the SKU in A2 and return the matching price.
Find employee department
=XLOOKUP(A2,Employees[ID],Employees[Department],"Missing")Use Excel Tables so the formula expands as the employee list grows.
Return multiple columns
=XLOOKUP(A2,$F$2:$F$100,$G$2:$I$100,"Not found")Return several fields, such as price, category, and owner, from one lookup.
Approximate match for tiers
=XLOOKUP(B2,$H$2:$H$6,$I$2:$I$6,,1)Find the correct tier for a score, discount, or commission band.
Search from bottom
=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,"Not found",0,-1)Return the latest matching record when duplicate IDs exist.
Two-way XLOOKUP
=XLOOKUP(B1,$G$1:$K$1,XLOOKUP(A2,$F$2:$F$100,$G$2:$K$100))Match both a row label and a column header.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Make the lookup array and return array the same size.
- Use an exact match for IDs and codes unless you intentionally need a tier or range lookup.
- Do not hide missing data with a blank message if the report owner needs to investigate missing records.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the XLOOKUP formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.