XLOOKUP Function in Apple Numbers: Examples and Syntax
XLOOKUP in Apple Numbers: return a price from a product code using practical sample data, clear expected results, pitfalls and the official function documentation.
Reference checked: Examples have not been run in this spreadsheet application. How we verify guides
Quick answer: Return a price from a product code. A product register stores SKUs and prices in adjacent columns. XLOOKUP can retrieve the price for an exact SKU without relying on a hard-coded column position inside a larger table.
This page covers the Apple Numbers implementation specifically. Its example values and expected results are original educational data, not screenshots or claims of a live application test.
Syntax and argument order
=XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)
The argument order above follows the official Apple Numbers documentation. Use the function name shown here, and replace placeholder arguments with ranges, cell references or criteria from your workbook.
Worked example with sample data
| Row | A: SKU | B: Price |
|---|---|---|
| 2 | SKU-1 | 12 |
| 3 | SKU-2 | 17 |
| 4 | SKU-3 | 25 |
| 5 | SKU-4 | 9 |
Enter this formula in a free result cell (outside the input range):
=XLOOKUP("SKU-3",A2:A5,B2:B5,"Not found")
Expected result: 25.
Why this result is correct: The search value appears in row 4 of the table, so XLOOKUP returns the matching price of 25 from column B.
A second example or variation
=XLOOKUP("SKU-9",A2:A5,B2:B5,"Not found")
Expected result: Not found.
The fourth argument provides a useful fallback instead of an unhandled lookup error.
Common mistakes and troubleshooting
The return range must correspond to the search range. Apple also documents optional match and search modes; do not select binary search modes unless the lookup column is sorted appropriately.
Also check that the version of Apple Numbers you use supports this function. A formula pasted from another application can fail because of different syntax, date interpretation, array behavior or regional separators. If the result differs, first compare the small dataset above with the expected answer before substituting your real data.
Where this function helps
A product register stores SKUs and prices in adjacent columns. XLOOKUP can retrieve the price for an exact SKU without relying on a hard-coded column position inside a larger table. Replacing the sample cells with your own ranges lets you reuse the same reasoning. On important spreadsheets, test with a few known rows, verify the final cell formatting and keep a separate copy of the source data.
Practical checks before reusing this formula
XLOOKUP is preferable to approximate-match lookup when you need an exact identifier and a clear no-match result. Ensure the lookup and return ranges have matching dimensions.
If codes contain leading zeros, retain the lookup field as text in both tables so that 0042 is not silently treated as numeric 42.
To compare this function with other spreadsheet programs, use our eight-software function comparison. A missing guide does not mean a function is unsupported in another product; check its official documentation.
Official reference and verification
- Official Apple Numbers — XLOOKUP reference
- Verification status: Official documentation checked; example outcome independently reasoned or arithmetically checked where applicable. Not executed in Apple Numbers.
- No genuine Apple Numbers screenshots are included; this guide does not use mock application images.