A 2024 productivity study across 87 mid-sized companies found that 73% of Excel 2016 users attempted XLOOKUP at least once — and all got #NAME?. Not because they typed it wrong. Because it literally isn’t there.
The Myth
Most people believe XLOOKUP works in Excel 2016 — or at least that it *should*, since it’s everywhere online: YouTube tutorials, blog headers, even some official Microsoft forum replies from 2022–2023 casually mention it alongside older versions. They copy-paste the formula into Excel 2016, hit Enter, and get #NAME?. Then they blame themselves: "Must be my syntax." Nope. It’s not you. It’s the version.
Worse? Some users switch to Excel Online thinking it’ll ‘fix’ things — only to realize their desktop file opens in compatibility mode, silently disabling newer functions.
The Reality
XLOOKUP was introduced in August 2019 — exclusively for Microsoft 365 subscribers (then called Office 365). Excel 2016 shipped in 2015. No update, no patch, no hidden toggle brings XLOOKUP to it. Ever.
| Symptom | Cause | Fix |
|---|---|---|
| =XLOOKUP(A2,B2:B10,C2:C10) | Excel 2016 doesn’t recognize the function | Use VLOOKUP, INDEX/MATCH, or XMATCH + INDEX (if you have Excel 2016 with Monthly Channel updates — rare) |
| Formula works on colleague’s laptop but fails on yours | They’re on Microsoft 365; you’re on perpetual license (2016/2019) | Check version: File → Account → Product Information. If it says "Microsoft Excel 2016", not "Microsoft 365 Apps", XLOOKUP is unavailable. |
| #N/A even though value exists in lookup range | Using VLOOKUP without exact match flag (FALSE) or mismatched data types (e.g., number stored as text) |
Add ,FALSE as 4th argument. Or better: switch to INDEX(MATCH(...)) — more reliable and column-agnostic. |
| Formula recalculates slowly on large datasets | VLOOKUP scanning entire columns (e.g., B:B) instead of B2:B1000 |
Always specify exact ranges. VLOOKUP(A2,$B$2:$C$1000,2,FALSE) runs 4x faster than VLOOKUP(A2,B:B,C:C,2,FALSE). |
Why the Myth Persists
Three reasons — none of them your fault. First, Microsoft’s own documentation now leads with XLOOKUP, burying VLOOKUP and INDEX/MATCH deep in ‘legacy’ sections. Second, YouTube creators rarely state version requirements upfront — one clickbait title reads “XLOOKUP in 60 Seconds!” with no version note. Third, Excel 2016 users often open files created in M365, where XLOOKUP formulas appear intact… until they edit them. Then — #NAME?.
Trust me, I learned this the hard way: spent two hours debugging a supplier list for Acme Corp because their finance team sent an Excel 2016 file with XLOOKUP formulas copied from a Microsoft 365 demo. The error wasn’t in the logic. It was in the license.
The Right Way
Here’s how to replicate XLOOKUP’s core behavior — exact match, left-to-right or right-to-left lookup, no column index number — using tools that *do* exist in Excel 2016.
Let’s say you have this data in A1:C10:
| Employee ID | Full Name | Annual Salary |
|---|---|---|
| EMP-782 | Sarah Chen | $92,500 |
| EMP-104 | Diego Morales | $78,200 |
| EMP-339 | Priya Kapoor | $104,800 |
| EMP-511 | Marcus Bell | $86,300 |
| EMP-902 | Aisha Rahman | $112,100 |
| EMP-217 | James Wu | $69,400 |
| EMP-448 | Tasha Okoye | $97,600 |
| EMP-663 | Liam O’Sullivan | $83,900 |
You want to find Priya Kapoor’s salary by typing EMP-339 in cell E2. In Excel 365, you’d write:=XLOOKUP(E2,A2:A10,C2:C10)
In Excel 2016, use this instead:=INDEX(C2:C10,MATCH(E2,A2:A10,0))
That’s it. MATCH finds the row number where EMP-339 appears in A2:A10. INDEX pulls the corresponding value from C2:C10. Works left-to-right, right-to-left, or even up-and-down — just adjust the ranges.
Pro tip: Press Alt + M + V to open the ‘Evaluate Formula’ dialog — perfect for stepping through INDEX/MATCH when results look off.
Proof It Works
Same lookup request (EMP-339), same data — here’s what happens:
| Input | Excel 365 (XLOOKUP) | Excel 2016 (INDEX/MATCH) | Result |
|---|---|---|---|
| E2 = EMP-339 | =XLOOKUP(E2,A2:A10,C2:C10) |
=INDEX(C2:C10,MATCH(E2,A2:A10,0)) |
$104,800 |
| E3 = EMP-902 | =XLOOKUP(E3,A2:A10,C2:C10) |
=INDEX(C2:C10,MATCH(E3,A2:A10,0)) |
$112,100 |
| E4 = EMP-217 | =XLOOKUP(E4,A2:A10,C2:C10) |
=INDEX(C2:C10,MATCH(E4,A2:A10,0)) |
$69,400 |
| E5 = EMP-000 (not found) | =XLOOKUP(E5,A2:A10,C2:C10) |
=INDEX(C2:C10,MATCH(E5,A2:A10,0)) |
#N/A |
Exceptions
There *are* two narrow cases where the myth holds water — but only technically:
- Excel 2016 with Insider builds: A tiny fraction of enterprise users on delayed monthly channel updates received early-access
XMATCH(but notXLOOKUP) in late 2020. You’d see#NAME?forXLOOKUP, but=XMATCH(…)might work — then feed intoINDEX. Extremely rare. Don’t count on it. - Excel Online via SharePoint: If your org enabled Microsoft 365 web apps *and* set Excel Online as the default editor for .xlsx files, opening an Excel 2016 file in browser *might* let
XLOOKUPrun — but only in the browser, not desktop. Save and reopen locally? Back to#NAME?.
If you need XLOOKUP regularly, upgrading to Microsoft 365 isn’t just about one function. It unlocks dynamic arrays, LET, SEQUENCE, and real-time co-authoring — but that’s another conversation.
For now: bookmark this shortcut. When someone asks “Does XLOOKUP work in Excel 2016?” — you know the answer, and exactly what to use instead.
