> ## Content Index
> Fetch the complete content index at: https://macfixly.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to Use XLOOKUP in Numbers: A Step-by-Step Guide
- URL: https://macfixly.com/use-xlookup-numbers/
- Published: 2026-10-09T05:20:00.000Z
- Updated: 2026-10-09T05:20:00.000Z
- Description: Discover a step-by-step guide on how to use XLOOKUP in Numbers to perform accurate lookups with mixed data types and multiple criteria.
- Author: Macuser
- Tags: Spreadsheets, Apple Numbers, Data Lookup, Formulas, Productivity

You've gathered a list of product IDs and prices in Apple Numbers, but quickly realize that finding the right price for a specific item isn't as straightforward as you expected. Perhaps you're used to Excel's XLOOKUP and wonder how to perform similar searches in Numbers without errors when your data mixes numbers and text.

This article guides you through how to use XLOOKUP in Numbers step by step. You'll learn how to handle lookups involving numbers and text together, apply XLOOKUP for multiple criteria, and avoid common pitfalls that cause unexpected results. Whether you are transitioning from Excel or want to enhance your spreadsheet skills, this guide breaks down each action clearly for effective data lookup.

## Key takeaways

- XLOOKUP in Numbers requires matching data types precisely for accurate results.
- You can combine multiple lookup values using concatenation to perform advanced searches.
- Using the IFERROR function alongside XLOOKUP helps manage missing or mismatched data gracefully.
- Always check the data format of your lookup values to prevent mismatches between numbers and text.

## Before you start: What you need to use XLOOKUP in Numbers

To use XLOOKUP in Apple Numbers, you first need to ensure you have the right software version. XLOOKUP is available starting from Numbers 13.0, which was released with macOS Ventura and iOS 16\. If your Numbers version is older, you will not see the XLOOKUP function available. Check your version by opening Numbers and selecting **Numbers > About Numbers** from the menu bar; the version number appears in the window.

Next, having a basic familiarity with Numbers’ interface and formula entry is essential. You should know how to select cells, enter formulas by typing an equals sign (*\=*), and understand how rows and columns form your data structure. This foundation helps when setting up lookup arrays (the range you search) and return arrays (the range from which you want to retrieve data).

Understanding your data layout is crucial before using XLOOKUP. You need to clearly define which column or row contains the lookup values and which contains the return values. For example, if you are searching for a product code to find its price, the product codes form your lookup array, and the prices form your return array.

While Apple's XLOOKUP shares conceptual similarities with Excel’s, it is not identical. Some arguments and behaviors differ slightly, so avoid copying Excel formulas directly without adjustment. Familiarize yourself with Apple’s formula syntax and function documentation to ensure accuracy.

Below is a quick reference table of Numbers versions supporting XLOOKUP:

| Numbers Version   | Minimum Required OS          | XLOOKUP Available |
| ----------------- | ---------------------------- | ----------------- |
| 13.0 and later    | macOS Ventura / iOS 16       | Yes               |
| Earlier than 13.0 | Earlier macOS / iOS versions | No                |

Make sure your Numbers app is updated to version 13.0 or newer to access XLOOKUP.

## How do you do an XLOOKUP in Numbers?

The XLOOKUP function in Numbers helps you find a value in one column and return a matching value from another column. Its syntax looks like this: *XLOOKUP(lookup\_value, lookup\_array, return\_array, \[if\_not\_found\], \[match\_mode\], \[search\_mode\])*. The first three arguments are essential: **lookup\_value** is the item you want to find, **lookup\_array** is the range where you search for that item, and **return\_array** contains the values you want to retrieve. The last three are optional settings that control what happens if no match is found, how the match is made, and the direction of the search.

Let's walk through an example step-by-step. Suppose you have a table with product IDs in column A and prices in column B. You want to find the price of product ID "P102".

1. Click the cell where you want the price to appear.
2. Type **\=XLOOKUP(** to start the formula.
3. Enter the lookup value: **"P102"**, followed by a comma.
4. Select the lookup array: click and drag to highlight cells **A2:A5**, then enter a comma.
5. Select the return array: highlight cells **B2:B5**, then type a closing parenthesis **)**.
6. Press **Return**. The cell will display the price corresponding to "P102".

If the product ID you enter isn’t found, the formula returns an error by default. You can customize this by adding the **if\_not\_found** argument, for example, "Not found". By default, XLOOKUP uses an exact match and searches from the start to the end of the lookup array.

## How to use XLOOKUP with numbers and text together

Numbers treats data types strictly when performing lookups with XLOOKUP. This means that numeric and text values are not interchangeable by default. If your lookup value is a number, but the lookup array contains text that looks like numbers, the function may not find a match, and vice versa. Understanding this behavior helps prevent unexpected errors.

![How to use XLOOKUP with numbers and text together – how to use XLOOKUP in Numbers](https://macfixly.com/content/images/2026/10/use-xlookup-numbers-2.webp)

For example, when you use XLOOKUP to find a purely numeric value in a numeric array, the function returns the expected result. The same applies when both the lookup value and the array contain only text. However, when you mix these types, such as looking up the text "123" in a numeric array or the number 123 in a text array, XLOOKUP will not match those entries.

| Lookup Value | Lookup Array                           | Result      | Explanation                              |
| ------------ | -------------------------------------- | ----------- | ---------------------------------------- |
| 123 (number) | Numeric array (e.g., 100, 123, 150)    | Match found | Both are numbers, so match succeeds      |
| "123" (text) | Text array (e.g., "100", "123", "150") | Match found | Both are text strings, so match succeeds |
| 123 (number) | Text array (e.g., "100", "123", "150") | No match    | Number does not equal text, so no match  |
| "123" (text) | Numeric array (e.g., 100, 123, 150)    | No match    | Text does not equal number, so no match  |

To handle mixed data types effectively, you can convert numbers to text or vice versa to ensure consistency. For instance, use the TEXT function to convert numbers to text before lookup, or use VALUE to convert text to numbers. This step ensures that lookup values and arrays are in the same format.

1. Identify the data type of your lookup value and the lookup array.
2. If they differ, decide which format to standardize on (text or number).
3. Use the TEXT() function to convert numbers to text if needed: =TEXT(A2, "0") converts a number in A2 to text.
4. Alternatively, use VALUE() to convert text to a number: =VALUE(B2) converts text in B2 to a number.
5. Apply the conversion consistently across the lookup value and lookup array columns.
6. Write your XLOOKUP formula using the standardized data to ensure accurate matching.

Ensuring type consistency avoids common pitfalls in XLOOKUP usage with mixed data. It also helps maintain spreadsheet reliability when working with imported or user-entered data that may vary in format.

## How to put XLOOKUP in your spreadsheet: practical examples

Integrating XLOOKUP into your Numbers spreadsheet streamlines data retrieval tasks. Here are three practical examples to help you apply it effectively.

### Example 1: Lookup a price based on product code

1. Enter your product codes in column A and prices in column B.
2. In a separate cell where you want the price to appear, type **\=XLOOKUP(**.
3. Specify the lookup value (e.g., the product code in another cell), the lookup array (column A), and the return array (column B). For example, *\=XLOOKUP(D2, A2:A20, B2:B20)*.
4. Press Enter. You should see the matching price for the product code you entered.

### Example 2: Lookup an employee name based on ID number

1. List employee IDs in column C and names in column D.
2. In the cell where you want the employee name, enter **\=XLOOKUP(**.
3. Use the ID you want to find, the ID column C as the lookup array, and the names column D as the return array, e.g., *\=XLOOKUP(F2, C2:C50, D2:D50)*.
4. Press Enter. The corresponding employee name should display.

### Example 3: Using XLOOKUP with approximate match for ranges

1. For tasks like grading or pricing tiers, list the lower bounds of ranges in ascending order in one column.
2. In the formula, add a fourth argument *, 1* to specify an approximate match.
3. Example: *\=XLOOKUP(G2, A2:A10, B2:B10,, 1)* will find the closest match less than or equal to the lookup value.
4. Press Enter to see the correct tier or grade for the value.

To copy XLOOKUP formulas across rows or columns, select the cell with the formula and drag the fill handle. Numbers will adjust relative cell references automatically. Use absolute references (with $ signs) if you want to keep lookup arrays fixed when copying.

These examples demonstrate how XLOOKUP can fit into common workflows, reducing manual searching and improving accuracy.

## How to use XLOOKUP based on two values

When you need to find a match based on two criteria in Numbers, XLOOKUP alone does not directly support multiple lookup arrays. However, you can work around this by concatenating the two lookup values and the corresponding lookup array or by using a helper column that combines both criteria into one string. This method lets you perform a single lookup based on the combined key.

1. Create a helper column in your data table that joins the two criteria columns using the **&** operator. For example, if you have columns *City* (A) and *Product* (B), in the helper column enter `=A2&B2`. Fill this down for all rows. You should see combined values like "New YorkWidget".
2. In your lookup formula, also combine the two lookup values exactly as in the helper column. For example, if you want to find data for City in D2 and Product in E2, use `=D2&E2` as the lookup value.
3. Write the XLOOKUP formula using the combined lookup value and the helper column as the lookup array. For example: `=XLOOKUP(D2&E2, HelperColumn, ReturnColumn)`. When entered correctly, it returns the matching value for both criteria.
4. Test the formula with different pairs of lookup values to ensure it finds the correct row. If no match exists, XLOOKUP will return an error unless you specify an alternative result.

Keep in mind this approach requires managing the helper column, which increases your sheet's complexity. Also, ensure that concatenated values are unique and consistent; otherwise, incorrect matches may occur. Numbers does not currently support array operations inside XLOOKUP for multiple criteria without helper columns, so this is a reliable and straightforward method.

In summary, combining two values into a unique key in a helper column allows XLOOKUP to perform advanced lookups based on two criteria, extending its usefulness in Numbers beyond simple single-value lookups.

## How to properly use XLOOKUP: tips for accuracy and efficiency

Using XLOOKUP effectively in Numbers means understanding its match modes and handling errors gracefully. The function supports exact and approximate matches, which you specify with the *matchMode* argument. Use exact match (enter 0 or omit the argument) when you need precise results, such as looking up product IDs. Approximate match (enter -1 or 1) works well for sorted numeric data like price brackets. Picking the wrong mode leads to unexpected results or errors.

![How to properly use XLOOKUP: tips for accuracy and efficiency – how to use XLOOKUP in Numbers](https://macfixly.com/content/images/2026/10/use-xlookup-numbers-3.webp)

XLOOKUP can return **#N/A** when no match is found. To prevent this from disrupting your spreadsheet, wrap XLOOKUP inside the *IFERROR* function. For example, `IFERROR(XLOOKUP(...), "Not found")` returns a friendly message instead of an error. This keeps your data clean and easier to interpret.

Beware of mixing data types in lookup values and arrays, as Numbers treats text and numbers differently. Blank cells in lookup or return arrays may also cause issues, so verify your data is complete and consistently formatted before applying XLOOKUP.

When working with large spreadsheets, XLOOKUP formulas can slow down calculation speed. To improve performance, limit the lookup ranges to only the necessary rows instead of entire columns. Also, avoid volatile functions nested inside XLOOKUP, as they trigger frequent recalculations.

| Common Error             | Typical Reason                                         | Corrected Formula Example                           |
| ------------------------ | ------------------------------------------------------ | --------------------------------------------------- |
| #N/A error               | No matching value found                                | IFERROR(XLOOKUP(A2, B2:B100, C2:C100), "Not found") |
| Incorrect value returned | Using approximate match instead of exact match         | XLOOKUP(A2, B2:B100, C2:C100,, 0)                   |
| #VALUE! error            | Lookup value and array types mismatch (text vs number) | XLOOKUP(VALUE(A2), B2:B100, C2:C100)                |
| Slow performance         | Lookup range set to entire columns                     | XLOOKUP(A2, B2:B1000, C2:C1000)                     |

## Troubleshooting XLOOKUP not working with numbers or text

If your XLOOKUP formula returns **#N/A** or unexpected results, the issue often relates to data type mismatches or reference errors. A common problem arises when numbers are stored as text or vice versa, causing the lookup to fail. For example, if your lookup value is a number but the lookup array contains the same numbers formatted as text, XLOOKUP won’t find a match.

Another frequent cause is extra spaces or hidden formatting in your data. These invisible characters can prevent exact matches even when values appear identical. Additionally, incorrect cell references in the lookup array or return array can lead to errors or irrelevant results.

Follow these steps to troubleshoot and fix XLOOKUP issues:

1. **Check data types:** Select the lookup value cell and the lookup array cells. Use the Format panel to confirm they are both set to Number or Text consistently. If numbers are stored as text, convert them by multiplying by 1 or using the VALUE() function. When done correctly, XLOOKUP should find the matching value.
2. **Remove extra spaces:** Use the TRIM() function on your lookup array and lookup value cells to eliminate leading or trailing spaces. For example, create a helper column with =TRIM(A2). Replace the original references in your XLOOKUP with this cleaned column. You should then see a successful match instead of *#N/A*.
3. **Verify cell references:** Double-check that your lookup\_array and return\_array ranges cover the correct cells and do not include headers or blank rows. Adjust the ranges if needed to ensure XLOOKUP searches the intended data.
4. **Test with a simple example:** Try a basic XLOOKUP with a known exact match in a small data set. If this works, gradually reintroduce your actual data and formula complexity to isolate the problem area.

By carefully checking these aspects—data types, spaces, and references—you can resolve most issues causing XLOOKUP not to work properly with numbers or text in Numbers.

## Frequently asked questions

### How to use or in xlookup?

Apple Numbers' XLOOKUP does not directly support logical operators like OR within its lookup function. To perform an OR-type lookup, you can create a helper column combining the conditions you want to check, then use XLOOKUP on that combined column. Alternatively, use multiple XLOOKUP functions nested inside an IF statement to cover different criteria.

### Does xlookup only work with numbers?

No, XLOOKUP in Numbers works with both numbers and text. It can handle mixed data types in lookup and return arrays. However, you need to ensure the lookup value and the search column data types match to avoid errors or incorrect matches.

### Can you xlookup a number?

Yes, you can use XLOOKUP to find and retrieve values based on a number. Just enter the number as the lookup value, and make sure the search column contains numbers formatted consistently. Pay attention to number formatting, as differences like text-formatted numbers can cause mismatches.

### How can i use xlookup?

To use XLOOKUP, select the cell where you want the result, then enter the formula with syntax: XLOOKUP(lookup\_value, lookup\_array, return\_array). For example, XLOOKUP(42, A1:A10, B1:B10) searches for 42 in A1:A10 and returns the corresponding value from B1:B10\. Adjust ranges to your data and ensure data types are consistent.

### How to find xlookup?

In Numbers, XLOOKUP is entered directly as a formula in a cell, just like other functions. Start by typing =XLOOKUP( and Numbers will show the function help. There is no dedicated menu for it; you find it by typing its name in the formula bar or using the Function Browser and searching for "XLOOKUP."

## What This Guide Does Not Cover

This guide focuses specifically on using XLOOKUP within Apple Numbers. It does not cover using XLOOKUP in Excel or other spreadsheet applications, as the function’s syntax and behavior differ slightly in Numbers. Additionally, advanced lookup scenarios that involve dynamic arrays or multiple criteria beyond two values may require more complex workarounds or AppleScript automation, which are outside the scope of this article.

If your needs include highly complex lookups or automation beyond what Numbers natively supports, exploring dedicated Excel tutorials or AppleScript resources may be more helpful.

To continue improving your XLOOKUP skills in Numbers, the most useful next step is to practice by building your own spreadsheet with sample data that includes both numbers and text. Experiment with combining lookup values and test edge cases to get comfortable with the function’s behavior and limitations in real-world scenarios.

**See also:** [How to Use VLOOKUP in Numbers: A Step-by-Step Guide for Numbers Users](https://macfixly.com/p/082f14bd-23ff-490e-b7b6-126298aa6516/) · [How to Add a Dropdown Menu in Numbers: A Clear Step-by-Step Guide](https://macfixly.com/p/7aff49e9-5290-49ac-9980-601a942a1628/) · [How to Sort Data Alphabetically in Numbers: A Step-by-Step Guide](https://macfixly.com/p/abc4a573-ad6d-47b5-8e9a-c9ed2eae90b6/) · [How to Use Conditional Highlighting in Numbers: A Step-by-Step Guide](https://macfixly.com/p/8815c5e5-380f-4ed9-b88e-cc381c8d2f2b/)