📊 Microsoft Excel — Lookup & Reference Excel 365 / 2019+
Excel XLOOKUP Function
The modern replacement for VLOOKUP — searches in any direction, handles missing values natively, returns arrays, and requires no column numbers. The most powerful lookup function Excel has ever had.
📖 What is the XLOOKUP Function?
XLOOKUP is a modern Lookup & Reference function introduced in
Excel 365 and Excel 2019 that searches a lookup array for a
specified value and returns a corresponding result from a return array.
It is the official successor to both VLOOKUP and HLOOKUP,
eliminating their most critical limitations.
XLOOKUP can search vertically or horizontally, look left or right
(unlike VLOOKUP which only looks right), handle not-found errors natively without
IFERROR wrappers, return multiple columns at once, and even perform
reverse/last-match searches — making it the single most versatile lookup tool
available in modern Excel.
❌ VLOOKUP Limitations
- Looks right only — can’t search left
- Breaks if columns are inserted
- Requires a column number (col_index)
- Returns only one column
- No native “not found” handling
- Slower on large datasets
✅ XLOOKUP Advantages
- Searches left, right, up, or down
- Column-insertion proof by design
- No column number needed
- Returns multiple columns as an array
- Built-in if_not_found argument
- Faster binary search mode available
↕
Any Direction
Vertical, horizontal, left, right
🔄
Reverse Search
Find last match in a list
📦
Array Return
Return multiple columns at once
⚙️ Syntax & Arguments
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
| Argument | Required? | Description |
|---|---|---|
| lookup_value | Required | The value to search for — a cell reference, text, number, or formula result. |
| lookup_array | Required | The single row or column range to search in (e.g., A2:A100 or A1:Z1). |
| return_array | Required | The range to return a value from — can be multiple columns wide for array returns. |
| [if_not_found] | Optional | Value to return if no match is found. Replaces IFERROR wrapper. E.g., “Not Found”. |
| [match_mode] | Optional | 0 = Exact match (default) · -1 = Exact or next smaller · 1 = Exact or next larger · 2 = Wildcard |
| [search_mode] | Optional | 1 = First to last (default) · -1 = Last to first · 2 = Binary asc · -2 = Binary desc |
| match_mode | Behaviour | Best For |
|---|---|---|
| 0 | Exact match only — returns error or if_not_found if no match | Invoice no., PAN, Employee ID |
| -1 | Exact match; if not found, returns next smaller value | Tax slab, salary grade lookup |
| 1 | Exact match; if not found, returns next larger value | Delivery date, pricing tiers |
| 2 | Wildcard match (* ? ~) | Partial company name search |
ℹ️ Formula patterns:
Basic: =XLOOKUP(E2, A2:A100, C2:C100, "Not Found")
Left lookup: =XLOOKUP(E2, C2:C100, A2:A100) — returns column to the LEFT of search column
Multi-col: =XLOOKUP(E2, A:A, B:D) — returns 3 columns simultaneously
Last match: =XLOOKUP(E2, A:A, B:B, , 0, -1) — finds the LAST occurrence
📊 Example 1 — Employee Details Lookup (Basic with if_not_found)
Basic Use Case
An HR team wants to fetch an employee’s Department and CTC by entering their
Employee ID in a search cell. The formula should return a clean “Not Found”
message if the ID doesn’t exist — no IFERROR wrapper needed.
| A — Emp ID | B — Name | C — Department | D — CTC (₹) |
|---|---|---|---|
| EMP-101 | Aarav Sharma | Finance | 12,00,000 |
| EMP-102 | Priya Mehta | HR | 8,50,000 |
| EMP-103 | Rohan Gupta | Operations | 9,20,000 |
| EMP-104 | Sunita Nair | Marketing | 11,00,000 |
| EMP-105 | Vikram Das | Sales | 7,80,000 |
Search cell F1 = “EMP-103”
Fetch Department → =XLOOKUP(F1, A2:A6, C2:C6, “Not Found”) → Operations
Fetch CTC → =XLOOKUP(F1, A2:A6, D2:D6, “Not Found”) → ₹ 9,20,000
Both at once → =XLOOKUP(F1, A2:A6, C2:D6, “Not Found”) → Operations | ₹ 9,20,000
Missing ID → =XLOOKUP(“EMP-999”, A2:A6, C2:C6, “❌ Not Found”) → ❌ Not Found
✅ Department: Operations
💰 CTC: ₹ 9,20,000
Key advantage over VLOOKUP: The 4th argument "Not Found" handles missing IDs
cleanly — no need to wrap in =IFERROR(VLOOKUP(...),"Not Found").
Also, the multi-column return C2:D6 fetches Department AND CTC in a single formula —
impossible with standard VLOOKUP.
📊 Example 2 — Left Lookup + Last Match (Advanced Scenarios)
Advanced / Practical Use Case
Two advanced scenarios that VLOOKUP simply cannot handle:
(A) Left lookup — find an Employee ID by searching the Name column (which is to the RIGHT of the ID column),
and (B) Last match — in a sales log with repeated salesperson entries, retrieve the most recent transaction amount.
Part A — Left Lookup (find ID by Name)
| A — Emp ID | B — Name | C — Dept |
|---|---|---|
| EMP-101 | Aarav Sharma | Finance |
| EMP-102 | Priya Mehta | HR |
| EMP-103 | Rohan Gupta | Operations |
Search by Name → =XLOOKUP(“Priya Mehta”, B2:B4, A2:A4, “Not Found”) → EMP-102
// lookup_array (B) is to the RIGHT of return_array (A) — impossible in VLOOKUP
Part B — Last Match (most recent transaction)
| A — Date | B — Salesperson | C — Amount (₹) |
|---|---|---|
| 01-Apr | Amit Sharma | 1,20,000 |
| 05-Apr | Priya Nair | 95,000 |
| 10-Apr | Amit Sharma | 2,10,000 |
| 15-Apr | Priya Nair | 1,75,000 |
| 20-Apr | Amit Sharma | 88,000 |
Last sale by Priya → =XLOOKUP(“Priya Nair”, B2:B6, C2:C6, “Not Found”, 0, -1) → ₹ 1,75,000
// search_mode = -1 scans from LAST row upward — returns most recent match
✅ Left Lookup: EMP-102
🔄 Last Match: ₹ 1,75,000
Why this matters: Left lookup eliminates the need to rearrange columns before using a lookup formula —
critical when working with locked Tally exports or ERP data dumps where column order cannot be changed.
Last match is essential for audit trails, revision histories, and most-recent-price lookups.
💡 Key Applications
-
Finance
Fetching ledger balances, account names, or GL codes from a chart of accounts by account number — in any column direction, without restructuring the master file. -
HR
Pulling multiple employee attributes (Grade, Department, Designation, CTC) in a single XLOOKUP formula using a multi-column return array — replaces 4 separate VLOOKUPs. -
Tax
GST rate lookup by HSN code using approximate match (match_mode = 1) — returns the applicable tax slab for any product code from a rate master table. -
Audit
Last-transaction audit — using search_mode = -1 to retrieve the most recent voucher, payment, or journal entry for a vendor or account from a chronological register. -
Sales
Price list lookup with fallback — fetch product price from a master list; if SKU is discontinued, return “Price on Request” via if_not_found without any additional formula layer. -
MIS
Building dynamic MIS dashboards where a single dropdown cell drives multiple XLOOKUP formulas returning entire rows of data — name, region, target, actual, variance — simultaneously. -
Retail
Barcode / SKU lookup — scan a product code and instantly retrieve product name, category, MRP, and current stock from an inventory master in one array-returning formula.
⚠️ Common Mistakes to Avoid
1. Using XLOOKUP in Excel 2016 / 2013 — #NAME? Error
XLOOKUP is only available in Excel 365, Excel 2019, and Excel 2021.
If the file is opened in an older Excel version, every XLOOKUP formula returns a #NAME? error —
breaking the entire workbook silently for colleagues on older software.
✔ Fix: Check team’s Excel versions before deploying XLOOKUP in shared files. Provide a VLOOKUP / INDEX-MATCH fallback version, or note the version requirement clearly in the file.
2. Lookup Array and Return Array Different Sizes → #VALUE!
Writing =XLOOKUP(E2, A2:A10, B2:B8) where lookup_array has 9 rows but return_array
has only 7 rows causes a #VALUE! error. Both arrays must span the same number of rows (or columns
for horizontal lookups).
✔ Fix: Always ensure lookup_array and return_array are the same length: =XLOOKUP(E2, A2:A10, B2:B10). Use full-column references like A:A, B:B for safety.
3. Forgetting if_not_found — Returns Ugly #N/A Error
Writing =XLOOKUP(E2, A:A, B:B) without the 4th argument means any unmatched value
displays a raw #N/A error — unprofessional in client-facing reports and dashboards.
✔ Fix: Always include a clean fallback: =XLOOKUP(E2, A:A, B:B, "Not Found") or use "" for blank, or 0 for numeric fields.
4. Using match_mode = 1 or -1 on Unsorted Data
Approximate match modes (-1 and 1) require the lookup array to be sorted in ascending order.
Using them on unsorted data (e.g., a random product list) returns incorrect results silently —
no error is shown, just a wrong value.
✔ Fix: Only use match_mode ≠ 0 on sorted ranges. For tax slabs or salary grades, sort the lookup table ascending first. For all other cases, use the default exact match (match_mode = 0).
5. Expecting XLOOKUP to Work Like VLOOKUP’s col_index_num
New users try writing =XLOOKUP(E2, A:A, 3) expecting to return the 3rd column —
like VLOOKUP’s column index. XLOOKUP doesn’t accept a column number; it requires an actual
range reference as return_array.
✔ Fix: Always provide the actual return range: =XLOOKUP(E2, A:A, C:C). To return column 3 dynamically, use =XLOOKUP(E2, A:A, INDEX(A:Z,,3)).
🔁 Related Topics & Functions
🔍 VLOOKUP (predecessor)
🔍 HLOOKUP
📋 INDEX + MATCH
🔄 XMATCH
🔢 MATCH
📐 INDEX Function
⚠️ IFERROR
🏷 Wildcard Criteria (*, ?)
📊 Dynamic Arrays
🔁 FILTER Function
💱 Absolute References ($)
📋 Named Ranges
tubeshaala · Excel Function Guide · XLOOKUP · Excel 365 / 2019+ · For educational use only
