The first thing most people do when they type XLOOKUP into Excel 2016 is hit Enter — then stare at #NAME?. That’s the wrong move. XLOOKUP didn’t exist until Excel 365 and Excel 2021. If you’re on 2016, typing it won’t just fail — it’ll mislead you into thinking your formula logic is broken, when really, the function doesn’t exist at all.
Quick Answer
No. Excel 2016 does not include XLOOKUP. It was introduced in August 2019 for Microsoft 365 subscribers and shipped with Excel 2021. Excel 2016 users must use alternatives: INDEX/MATCH, VLOOKUP with helper columns, or array formulas with IF+INDEX+MATCH.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| INDEX + MATCH (single column) | =INDEX(C2:C11,MATCH(F2,A2:A11,0)) | Exact matches, leftward lookups, clean syntax | Fails on duplicates unless combined with AGGREGATE or SMALL |
| VLOOKUP + helper column (for right-to-left) | Insert column A, paste =B2&"|"&C2, then VLOOKUP(F2&"|"&G2,A2:D11,4,FALSE) | Legacy workflows where VLOOKUP is already embedded | Clutters sheet, breaks if source data shifts, slow on >10k rows |
| Array formula: IF + INDEX + MATCH | {=INDEX(D2:D11,MATCH(1,(A2:A11=F2)*(B2:B11=G2),0))} — press Ctrl+Shift+Enter | Multi-criteria lookups (e.g., name + region) | Not dynamic — requires manual re-entry after edits; fails silently if criteria don’t match |
| SUMIFS as pseudo-lookup | =SUMIFS(D2:D11,A2:A11,F2,B2:B11,G2) — only works for numeric returns | Returning sums, counts, or averages — no text or dates | Returns 0 if no match (not #N/A), can’t distinguish between zero value and no match |
Method 1 Deep Dive
Use INDEX/MATCH for exact-match lookups — especially when you need to pull data from left of the lookup column. It’s faster and more reliable than VLOOKUP in Excel 2016.
Here’s real data in A1:D11:
| Employee ID | Full Name | Department | Salary |
|---|---|---|---|
| EMP-782 | Sarah Chen | Finance | $82,500 |
| EMP-319 | Diego Mora | Engineering | $112,300 |
| EMP-544 | Priya Kapoor | Marketing | $74,100 |
| EMP-107 | Jamal Wright | HR | $69,800 |
| EMP-923 | Anya Petrova | Engineering | $108,600 |
You want to find Sarah Chen’s salary. Put EMP-782 in F2. In G2, enter:
=INDEX(D2:D11,MATCH(F2,A2:A11,0))
This pulls the value from column D where column A matches F2. No dragging required. No column index numbers to count. And it works even if you insert a column between A and D — unlike VLOOKUP.
Surprising tip: If you need case-sensitive matching, wrap MATCH in EXACT inside an array formula. But don’t bother — Excel 2016’s MATCH is case-insensitive by design. Trying to force case sensitivity here will break more than it fixes.
Method 2 Deep Dive
For multi-criteria lookups — say, “What’s Priya Kapoor’s salary *in Marketing*?” — use an array formula combining IF, INDEX, and MATCH.
Assume your criteria are in F2 (name) and G2 (department). Your data range is A2:D11 as before. In H2, enter:
{=INDEX(D2:D11,MATCH(1,(A2:A11=F2)*(B2:B11=G2),0))}
Then press Ctrl+Shift+Enter — not Enter alone. Excel will add curly braces automatically. If you see #N/A, double-check spelling and whitespace. If you see 0, one criterion matched but not both.
Why not use SUMIFS? Because SUMIFS returns 0 for non-numeric results — like department names or hire dates. You’ll get false confidence. INDEX/MATCH returns #N/A when nothing matches. That’s safer.
And yes — this formula works in Excel 2016. It’s been stable since Excel 2007. No updates needed. Just remember: never edit the formula and hit Enter. Always use Ctrl+Shift+Enter after changes.
Cheat Sheet
| Task | Formula | Shortcut / Notes |
|---|---|---|
| Exact match, single criterion | =INDEX(return_range,MATCH(lookup_value,lookup_range,0)) | Alt+= inserts SUM — but Alt+M+V opens Formula Auditing → Evaluate Formula (useful for debugging) |
| Two-criteria lookup | {=INDEX(return_range,MATCH(1,(range1=crit1)*(range2=crit2),0))} | Ctrl+Shift+Enter — not Enter. If you forget, Excel treats it as a regular formula and returns #N/A. |
| Return first match only (no duplicates) | =INDEX(return_range,AGGREGATE(15,6,ROW(lookup_range)/(lookup_range=lookup_value),1)) | AGGREGATE(15,6,...) ignores errors — works without Ctrl+Shift+Enter. Use ROW()-ROW() offset if data starts below row 1. |
| Find next occurrence after first | =INDEX(return_range,SMALL(IF(lookup_range=lookup_value,ROW(lookup_range)-ROW($A$2)+1),2)) | Array formula — Ctrl+Shift+Enter. Change “2” to “3” for third match. Only works if lookup_range is contiguous and starts at known row. |
