XLOOKUP syntax: what each argument does
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])The first three arguments are required. Square brackets mark optional arguments in this syntax description; do not type the brackets into your formula.
- lookup_value: the ID or other value you want to find.
- lookup_array: one column or row to search.
- return_array: the corresponding values to return, aligned with the search range.
- if_not_found: a message or value for an absent key. Omitting it produces #N/A.
- match_mode: 0 for exact matching (the default), -1 for exact or next smaller, 1 for exact or next larger, or 2 for wildcards.
- search_mode: 1 searches first to last (the default); -1 searches last to first. Binary modes 2 and -2 require ascending and descending sorted data respectively.
For product IDs, keep match_mode at 0. A nearby ID is a different product, even if an approximate match could find one.
Start with an exact product lookup
On a separate Orders sheet, put P-204 in A2. The catalog below lives on Products. You want the price from column C.
| Row | A: ID | B: Product | C: Price |
|---|---|---|---|
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-204 | Stapler | 12.00 |
| 4 | P-318 | Markers | 7.25 |
=XLOOKUP(A2,Products!$A$2:$A$4,Products!$C$2:$C$4,"Not found",0)The result is 12.00. A2 supplies the ID, the first range contains the IDs to search, and the second range contains prices in the same row order. The fourth argument supplies the missing-value text; 0 explicitly requests an exact match. Exact matching is also the default if you omit that argument.
Dollar signs keep the catalog ranges fixed when you copy the formula down Orders. A2 then becomes A3, while the catalog stays in place. The lookup and return ranges must have matching dimensions.
Look to the left without rearranging columns
If Orders!B2 contains Stapler and you need its ID from Products column A, search the names in B and return A:
=XLOOKUP(B2,Products!$B$2:$B$4,Products!$A$2:$A$4,"Not found",0)This returns P-204. A standard VLOOKUP cannot return a column to the left of its lookup column. This left-lookup example is for manual use; it is outside our generator’s current scope.
Handle a missing product explicitly
Change Orders!A2 to P-999. The price formula above returns Not found because that ID is absent. This message only handles a missing match; it does not suppress an error stored in the matched price cell. Restore A2 to P-204 before continuing.
Return the last matching record
Suppose the catalog grows: add P-204, Stapler, and 13.50 in Products row 5. The default first-to-last search still returns the original 12.00. To return the last occurrence instead, extend both ranges and set the sixth argument to -1:
=XLOOKUP(A2,Products!$A$2:$A$5,Products!$C$2:$C$5,"Not found",0,-1)The result is 13.50. “Last” means lowest in the selected range, so it represents the latest price only when your records are ordered that way. This does not add duplicate prices. Remove the hypothetical row 5 or keep it outside the original ranges when testing the earlier examples.
Choose between XLOOKUP and VLOOKUP
For the rightward price lookup, the equivalent VLOOKUP is:
=VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE)VLOOKUP uses a column number inside a table; XLOOKUP points directly at the return range. That can make a formula easier to read when a table has many columns. Both examples return the first matching ID, so duplicate catalog IDs require a data decision rather than a different spelling of the formula.
Troubleshoot the result before filling down
A #VALUE! error can indicate incompatible range sizes: A2:A4 cannot align with C2:C5. If a known ID returns Not found, inspect spaces and number-versus-text differences. If copying down changes the catalog, add the $ markers to both source ranges. A #NAME? error may mean your Excel version does not recognize XLOOKUP.
The exact and left lookup examples also work in Google Sheets. See Google’s XLOOKUP reference for its argument names, or our Sheets formula guide for locale and separator checks.
Before pasting into a shared workbook
Check the recipient’s Excel version. Microsoft documents that XLOOKUP is unavailable in Excel 2016 and Excel 2019. For those workbooks, use INDEX MATCH for a left lookup. See Microsoft’s XLOOKUP reference for availability and arguments.