Excel XLOOKUP Function

Excel XLOOKUP Function – Complete Guide

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