# How to use XLOOKUP in Excel on Mac: step by step for Microsoft 365

> Learn how to use XLOOKUP in Excel on Mac, step by step, with the formula, if_not_found, match modes, shortcuts and fixes for #N/A and #NAME? errors.

URL: https://www.lazur.app/how-to/use-xlookup-in-excel
Updated: 2026-11-09

To use XLOOKUP in Excel on Mac, click an empty cell and type =XLOOKUP, then give it the value to find, the range to search and the range to return. This works in Microsoft 365, Excel 2024 and Excel 2021 for Mac, so check your version first if the formula shows #NAME?.

## Quick answer

- Type =XLOOKUP(lookup_value, lookup_array, return_array) in a cell, then press Return.
- Add a fourth argument such as "Not in list" so misses show a message instead of #N/A.
- Use Microsoft 365, Excel 2024 or Excel 2021 for Mac, on macOS 12 or later, with app version 16.83 or later.
- If you see #NAME?, save the file as .xlsx and check your Excel version before anything else.
- Test one row by hand before you copy the formula down the column.

Applies to: Excel for Mac in Microsoft 365, Excel 2024 and Excel 2021, macOS 12 or later. Time: 10 min. Difficulty: Easy.

## What you need

- Excel for Mac in Microsoft 365, Excel 2024 or Excel 2021
- macOS 12 (Monterey) or later
- Excel for Mac app version 16.83 or later
- A .xlsx, .xlsm or .xlsb file, not .xls
- A column of lookup values and a matching column of results

## How to Write an XLOOKUP formula in Excel on Mac in Excel on a Mac

1. **Check your Excel version first.** (Help > Check for Updates) Open Excel and select Help > Check for Updates. If Microsoft AutoUpdate opens, select Update or Update All, then restart Excel when the update finishes. You need Microsoft 365, Excel 2024 or Excel 2021 for Mac for XLOOKUP to work. Tip: Excel 2019 and 2016 do not support XLOOKUP, even though they can open the file.
2. **Lay out your data.** Put the codes you search for in one column, such as B, and the answers in another column, such as C. Start both ranges on the same row, and make them the same height. Mismatched ranges break the formula. Tip: Keep headers in row 1 and start both ranges at row 2, so headers are never searched.
3. **Click the result cell.** Click the empty cell where the answer should appear. In the worked example, that cell is D2, on the first row of your results column.
4. **Open the function.** Shortcut: ⇧F3. Click the fx button beside the formula bar to open Insert Function. Search for XLOOKUP, select it, then click OK. You can also type the formula straight into the cell. Tip: Typing is faster once you know the syntax. Insert Function helps when you forget an argument.
5. **Type the lookup value.** Type =XLOOKUP( and then the cell that holds the value to find, such as A2, followed by a comma. Keep the comma. Excel shows the name of the next argument as you type. Tip: Click the cell with the mouse to fill in its reference, instead of typing it.
6. **Give the search and return ranges.** Type the range to search, such as $B$2:$B$100, then a comma. Type the range that holds the answer, such as $C$2:$C$100. The dollar signs lock both ranges, so they stay put when you copy the formula down. Tip: Drag across a range with the mouse and Excel fills in the reference for you.
7. **Add the not-found message.** Type a comma, then a message in quotation marks, such as "Not in list". That text appears when no match exists. If you leave it out, the cell shows #N/A instead. Tip: A clear message such as "Not in list" makes misses easy to spot in a long sheet.
8. **Close the formula and check the answer.** Shortcut: Return. Type a closing parenthesis and press Return. Compare the result with the source row for one code you know. If the result looks wrong, press F2 to edit the cell and check each range. Tip: Copy the formula down the column with the fill handle at the bottom right of the cell once one row is right.

XLOOKUP searches one column for a value and returns the matching entry from another column. You give it three things: what to find, where to search and what to return. Two optional arguments control what appears when nothing matches and how the search runs.

![Person using a laptop at a desk to work on an XLOOKUP formula in Excel on Mac](https://images.lazur.app/content-images/howto/use-xlookup-in-excel/how-to-use-xlookup-in-excel-on-1-7487f0.jpg)

## Check that your Mac version supports XLOOKUP

Start here, because a formula that works on one Mac can fail on another. Microsoft's XLOOKUP page lists Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac in its Applies-To list. The same list includes iPad, iPhone and Android versions. It does not list Excel for the web.

The page contains a conflict. Its text says XLOOKUP is not available in Excel 2016 and Excel 2019, yet its Applies-To list includes both. Treat 2019 and 2016 as unsupported. If a workbook with XLOOKUP opens in one of them, the formula will not work there.

The Mac minimum is macOS 12 Monterey or later, with Excel for Mac app version 16.83 or later. Microsoft's update page lists both. Check your build first, since an older version is one of the common causes of #NAME? errors.

1. Open any Office app, such as Excel.
2. Select Help > Check for Updates in the menu bar.
3. If Microsoft AutoUpdate opens, select Update or Update All.
4. When the update finishes, restart the app.

Two support dates matter for older installs. Office 2019 for Mac reached end of support on October 10, 2023, and updating or reinstalling does not fix the problem it has. Office 2021 reached end of support on October 13, 2026, so it no longer gets security updates or technical support.

Microsoft's update page also says that starting July 13, 2026, affected non-subscription Office apps on Mac can open, view and print files but cannot edit or save them. If you bought a one-time copy, you may be unable to save the workbook you are building. Confirm which license you have before you spend time on the formula.

## The syntax, argument by argument

The full syntax is shown below. Read it left to right when you open someone else's workbook. Ask what the formula looks for, where it looks, and what it returns.

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

- lookup_value: the value you want to find, such as the code in A2.
- lookup_array: the range to search, such as $B$2:$B$100.
- return_array: the range that holds the answer, such as $C$2:$C$100.
- if_not_found: optional text or value shown when no valid match exists.
- match_mode: optional. 0 is exact match and is the default.
- search_mode: optional. 1 searches first to last and is the default.

Only the first three arguments are required. If you leave out if_not_found, XLOOKUP returns #N/A when no valid match exists. That is the most common surprise for new users. Set the fourth argument on any sheet that other people read.

## A worked example: pulling prices from a product list

Say you keep a product list. Column B holds product codes, column C holds the price for each code, and cell A2 holds the code you need. In cell D2, type =XLOOKUP(A2,$B$2:$B$100,$C$2:$C$100,"Not in list") and press Return.

![Rows of spreadsheet data on a laptop screen, the kind of product list an XLOOKUP formula searches](https://images.lazur.app/content-images/howto/use-xlookup-in-excel/how-to-use-xlookup-in-excel-on-2-7366ae.jpg)

When A2 contains a code that appears in column B, D2 shows the price from the same row in column C. When the code is missing, D2 shows Not in list. Copy D2 down to the last row you need. The dollar signs keep columns B and C fixed while A2 moves with each row.

| Cell or range | Contents | Role in the formula |
| --- | --- | --- |
| A2 | Code you want to price | lookup_value, changes as you copy down |
| $B$2:$B$100 | Product codes | lookup_array, locked with dollar signs |
| $C$2:$C$100 | Prices | return_array, same height as the search range |
| D2 | =XLOOKUP(A2,$B$2:$B$100,$C$2:$C$100,"Not in list") | The formula, copied down the column |

_Layout for the product price example_

Test the formula on a code you know is missing. If you see the message, the search range covers the right rows. If you see the wrong price, check that columns B and C start on the same row. A one-row offset produces wrong answers that look plausible, which makes it easy to miss.

## Match and search modes you will actually use

XLOOKUP uses an exact match and a first-to-last search by default. You change both with the fifth and sixth arguments. Most everyday lookups need neither change, so leave them out.

| Argument | Value | What it does |
| --- | --- | --- |
| match_mode | 0 | Exact match, the default |
| match_mode | -1 | Exact match, or the next smaller value |
| match_mode | 1 | Exact match, or the next larger value |
| match_mode | 2 | Wildcard match, where *, ? and ~ have special meaning |
| search_mode | -1 | Searches from last to first |
| search_mode | 2 | Binary search, needs ascending order |

_Match and search mode values from Microsoft's XLOOKUP page_

Approximate matching is the useful case. Suppose a table lists minimum values in ascending order, and you want the band a value falls into. Use match_mode -1, which returns the exact match or the next smaller value.

Wildcards work too. With match_mode 2, an asterisk stands for any run of characters. That is the idea behind the partial-match fix later on this page.

> **Sort before binary search:** Binary search modes return invalid results if the array is not sorted. Sort the data first, or keep the default search mode.

## Fix #N/A, #NAME? and partial-match errors

#N/A means XLOOKUP found no valid match, and you did not give it an if_not_found value. Check the lookup value for extra spaces first. Then add the fourth argument so the cell shows a clear message.

### #NAME? on a Mac

Microsoft's Q&A answers list three causes. The file may be an .xls file. The Excel build may be a non-365 version, such as Excel 2019, 2016 or an LTSC build after a license change. A defined name or VBA function called XLOOKUP may also shadow the built-in function.

For the first cause, save a copy as .xlsx, .xlsm or .xlsb. For the second, check your version using the update steps above. For the third, press ⌘F3 to open the Define Name dialog and check for a defined name called XLOOKUP. The Microsoft thread describes this check with Windows keys, and I could not confirm the Mac menu name on a Microsoft page, so check the labels on your build.

### The #VALUE error when codes are partial matches

A July 2021 Microsoft Tech Community thread reports #VALUE when a lookup cell holds part of a code. The accepted fix nests a second XLOOKUP that falls back to a wildcard search. The formula is =XLOOKUP(A2,$B$2:$B$100,$C$2:$C$100,XLOOKUP("*"&A2&"*",$B$2:$B$100,$C$2:$C$100,"No match",2),0).

Read it this way. The outer XLOOKUP tries an exact match first. If that finds nothing, the inner XLOOKUP searches for your text anywhere inside each code. Both ranges must be the same size, or the formula cannot pair rows.

| Error | Usual cause | What to try |
| --- | --- | --- |
| #N/A | No valid match, and if_not_found was left out | Check for extra spaces, then add an if_not_found message |
| #NAME? | An .xls file, an older build, or a name called XLOOKUP | Save as .xlsx, update Excel, check for the conflicting name |
| #VALUE | Partial codes with an exact match | Use the nested wildcard formula above |

_Quick fixes for the three errors people see most_

## Keep the formula working as your data grows

A fixed range such as $B$2:$B$100 stops working when a new product lands in row 101. Give the range some room, such as $B$2:$B$1000, so new rows fall inside it. Empty rows in the range are harmless for an exact match.

If you expect the list to keep growing, convert the data into an Excel table. Table references grow with the data, so your formula does not need edits each time a row is added. Check the formula after each change, since a converted table can rename the references.

## Keyboard shortcuts for working with formulas on a Mac

Microsoft's keyboard shortcut page for Excel lists the keys below. ⇧F3 is the closest match for Insert Function, which opens the Formula Builder. F2 edits the selected cell, which is the fastest way to fix one argument. The page did not list Name Manager for Mac, so ⌘F3 opens the Define Name dialog instead.

Check the other combinations on your own Mac before you rely on them. The page I read was a summary, and it did not confirm every Mac key combination. A shortcut that does nothing is usually a sign that your keyboard layout or a Mac setting is changing the key.

## Common mistakes and how to avoid them

- Using a column number. XLOOKUP takes a return array, so point it at the column itself, such as $C$2:$C$100.
- Mismatched ranges. The lookup and return arrays must be the same size, or the formula will not work.
- Missing dollar signs. Relative ranges shift when you copy the formula down, and the lookup then reads the wrong cells.
- Leaving out if_not_found. The cell shows #N/A, which looks like a bug to everyone who reads the sheet.
- Binary search on unsorted data. The results are invalid, so sort first or keep the default mode.
- Saving as .xls. That file type can trigger #NAME? with this function.
- Sending the file to someone on Excel 2019. The file opens, but the formula does not work there.

## Why spreadsheet formulas go wrong, and how to check yours

- **16.83** Minimum Excel for Mac app version listed for the update requirements, on macOS 12 or later ([source](https://support.microsoft.com/en-us/office/lifecycle/officeinstall/update-m365-office-macos-ios))
- **88%** of 113 operational spreadsheets in Panko's field audits contained errors, per a secondary summary ([source](https://keyholesoftware.com/spreadsheet-modernization/))
- **0.4% to 6.9%** range of formula cell error rates across the same field audits, per the same secondary summary ([source](https://keyholesoftware.com/spreadsheet-modernization/))
- **16.114.3** Latest Excel for Mac build on Microsoft's release notes, dated October 27, 2026 ([source](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac))

Errors in spreadsheets are common, which is why a quick check matters. In Panko's field audits from 1995 to 2007, 88% of 113 operational spreadsheets contained errors, according to a secondary summary by Keyhole Software. I could not read Panko's original paper, so treat that figure as a summary.

The same summary reports that the share of formula cells with an error ranged from 0.4% to 6.9%. A small share is still enough to put a wrong number in a report. Do not trust a lookup until you test it.

Pick three rows, one near the top, one in the middle and one near the bottom. Find each answer by hand. If the formula disagrees on any of them, check the ranges before you change the logic.

## Update Excel for Mac when XLOOKUP still fails

Updates change the build number, which is how you confirm a fix. Microsoft's release notes list 16.114.3, Build 26102720, dated October 27, 2026. That entry describes only quality and performance improvements. None of the recent entries I read mention XLOOKUP or function changes.

One release note matters for older installs. It says a previous update inadvertently disabled Office 2021 for existing users. If Office 2021 behaves oddly after an update, read the release notes before you assume the formula is wrong.

## What to expect in Excel for the web

If your team uses Excel for the web, test the formula there too. Microsoft's Applies-To list for XLOOKUP does not include Excel for the web. I found no official list of differences between Excel for Mac and Excel for the web. Some third-party sites claim support, but I could not verify those claims from a source.

## Where to go next

Once XLOOKUP works, the next step is often a summary. Read [how to make a pivot table in Excel on Mac](https://www.lazur.app/how-to/make-a-pivot-table-in-excel) to group your lookup results by category. Keep the lookup formulas on one sheet and the pivot table on another, so each stays easy to find.

Save a copy of the finished workbook before you share it. That copy is the one you can test if someone reports a wrong number later.

## Shortcuts

- Insert Function, closest match, opens Formula Builder: ⇧F3
- Edit the selected cell: F2
- Edit the active cell: ⌃U
- Open the Define Name dialog: ⌘F3
- Enter an array formula (also ⌃⇧Return): ⇧⌘Return

## If it does not work

- **The result cell shows #N/A** No valid match exists for the lookup value. Check for extra spaces or a number stored as text. Add an if_not_found message as the fourth argument so the cell explains the miss.
- **The result cell shows #NAME?** Save the file as .xlsx, .xlsm or .xlsb, confirm you run Microsoft 365, Excel 2024 or Excel 2021, and check that no defined name or VBA function is called XLOOKUP.
- **The #VALUE error appears when codes are partial matches** Use the nested XLOOKUP that falls back to a wildcard search, shown in the errors section. Keep the lookup and return ranges the same size.
- **A colleague's Excel 2019 opens the file, but the formula does not work** Their version does not support XLOOKUP. Move them to Microsoft 365, Excel 2024 or Excel 2021 for Mac, or send a copy of the results as values.
- **Office 2021 opens the file but will not save it** Microsoft says affected non-subscription Office apps on Mac cannot edit or save from July 13, 2026. Use a Microsoft 365 subscription to edit and save the file.
- **The wrong row comes back** Check that both ranges start on the same row, that match_mode and search_mode are the values you meant, and that the data is sorted if you used a binary search mode.

## With Lazur

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥ ⌘ and ask; it sees your screen, answers out loud, points at buttons and walks you through apps. Hold fn to dictate. Free for 3 days, then $18/month: https://www.lazur.app/download

- “Where is the Insert Function button?”: Lazur circles the fx button beside the formula bar and labels it, so you can see where to click. (Points at it)

## FAQ

### Does XLOOKUP work in Excel 2019 for Mac?

Not reliably. Microsoft's XLOOKUP page says the function is not available in Excel 2016 and Excel 2019, even though its Applies-To list includes them. A workbook with XLOOKUP opens in those versions, but the formula will not work there. Move to Microsoft 365, Excel 2024 or Excel 2021 for Mac.

### Why does XLOOKUP show #NAME? on my Mac?

Microsoft's Q&A answers list three causes. The file may be an .xls file, the Excel build may be a non-365 version, or a defined name called XLOOKUP may shadow the built-in function. Save the file as .xlsx and check your version first.

### What does #N/A mean in XLOOKUP?

It means XLOOKUP found no valid match, and you did not give it an if_not_found value. Check the lookup value for extra spaces. Then add a message as the fourth argument, such as "Not in list", so the cell explains itself.

### Is XLOOKUP the same in Excel for the web?

I cannot confirm that. Microsoft's Applies-To list does not include Excel for the web, and I found no official list of differences from Excel for Mac. Test the formula in the app your team actually uses.

### Can XLOOKUP look to the left?

Yes. The lookup array and the return array are separate arguments, so the return column can sit to the left of the search column. Keep both ranges the same size and the same height.

### Why do I get the wrong row back from XLOOKUP?

Check that both ranges start on the same row. Then check match_mode and search_mode. If you used a binary search mode on unsorted data, the results are invalid, so sort the data or return to the default mode.

### Why does my Office 2021 open the file but not save it?

Microsoft's update page says that starting July 13, 2026, affected non-subscription Office apps on Mac can open, view and print files but cannot edit or save them. Office 2021 also reached end of support on October 13, 2026. The limit applies to non-subscription apps, not to Microsoft 365.

## Sources

1. [XLOOKUP function: Microsoft Support](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), Microsoft, checked 2026-11-09
2. [Update Microsoft 365 or Office on your macOS or iOS device: Microsoft Support](https://support.microsoft.com/en-us/office/lifecycle/officeinstall/update-m365-office-macos-ios), Microsoft, checked 2026-11-09
3. [Keyboard shortcuts in Excel: Microsoft Support](https://support.microsoft.com/en-us/accessibility/excel/keyboard-shortcuts-in-excel), Microsoft, checked 2026-11-09
4. [Release notes for Office for Mac: Microsoft Learn](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac), Microsoft Learn, checked 2026-11-09
5. [error #NAME? using the XLOOKUP formula: Microsoft Q&A](https://learn.microsoft.com/en-us/answers/questions/5727915/error-name-using-the-xlookup-formula), Microsoft Q&A, checked 2026-11-09
6. [Excel for Mac Xlookup: Microsoft Tech Community](https://techcommunity.microsoft.com/discussions/excelgeneral/excel-for-mac-xlookup/2514447), Microsoft Tech Community, checked 2026-11-09
7. [When the Spreadsheet Is the System (Keyhole Software)](https://keyholesoftware.com/spreadsheet-modernization/), Keyhole Software, checked 2026-11-09