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

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.

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.

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.

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.

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.

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.

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

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 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.