lookup_value - Required. The value to search for (e.g., product ID, employee name, customer code).
lookup_array - Required. The array or range to search in (e.g., A:A, Product_IDs).
return_array - Required. The array or range to return values from (can be single or multiple columns).
if_not_found - Optional. Optional. Value to return when no match found (default: #N/A error).
match_mode - Optional. Optional. 0=exact match (default), -1=exact or next smaller, 1=exact or next larger, 2=wildcard.
search_mode - Optional. Optional. 1=first-to-last (default), -1=last-to-first, 2=binary ascending, -2=binary descending.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee ID | Name | Department | Lookup ID: | Result |
| 2 | E001 | Alice Johnson | Sales | E002 | |
| 3 | E002 | Bob Smith | IT | ||
| 4 | E003 | Carol Davis | HR | ||
| 2 | E001 | Alice Johnson | Sales | E002 | =XLOOKUP(D1, A:A, B:B) Bob Smith |
Search for E2 in column A, return corresponding value from column B
Simple vertical lookup without column counting
Returns "Not Found" instead of #N/A error when value doesn't exist
Built-in error handling without IFERROR wrapper
Return 3 columns (B, C, D) in one formula with dynamic array
Spills results across columns automatically
Dynamic lookup by both row header and column header
Replaces complex <Link href="/formulas/index">INDEX</Link> <Link href="/formulas/match">MATCH</Link> combinations
This modern lookup function revolutionizes product catalog lookups by eliminating VLOOKUP's left-to-right limitation. Search by product code and return any column - name, price, category, supplier - regardless of position. No need to count columns or rearrange data. It handles catalog updates gracefully: add new columns without breaking formulas, reorganize fields freely, and return multiple product attributes simultaneously. This lookup solution is essential for e-commerce managers, inventory specialists, procurement teams, and sales operations who manage extensive product databases and need flexible, maintainable lookup solutions.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Name | Category | Product ID | Price |
| 2 | Laptop Pro | Electronics | P001 | $1,200 |
| 3 | Wireless Mouse | Accessories | P002 | $25 |
| 4 | Keyboard RGB | Accessories | P003 | $75 |
| 5 | Lookup: P002 | =XLOOKUP("P002", C:C, A:A) Wireless Mouse |
Use this modern lookup function for employee records with built-in error handling - no need for IFERROR wrappers. Look up employee details by ID, name, or email and display "Employee Not Found" for invalid entries. It returns multiple columns simultaneously: name, department, salary, manager, and start date in one formula that spills across cells. This lookup solution is perfect for HR managers, payroll specialists, department heads, and people operations teams who need reliable employee data retrieval with graceful error handling for dashboards, reports, and automated workflows.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | Name | Department | Salary |
| 2 | E001 | Alice Johnson | Sales | $85,000 |
| 3 | E002 | Bob Smith | IT | $95,000 |
| 4 | E003 | Carol Davis | HR | $78,000 |
| 5 | Lookup: E999 | Not Found | =XLOOKUP("E999", A:A, B:D, "Not Found") |
❌ The Problem:
✅ Solution:
=XLOOKUP(E2, B:B, A:A)This modern lookup function overcomes all VLOOKUP limitations: look in any direction, reference columns directly instead of counting, handle errors with if_not_found parameter, and return multiple columns natively. It is cleaner, more maintainable, and more powerful for modern Excel versions. Use this solution for all new projects instead of legacy VLOOKUP patterns.
❌ The Problem:
✅ Solution:
=XLOOKUP(E2, A:A, B:B)Use entire column references (A:A, B:B) instead of fixed ranges. It handles full columns efficiently and automatically includes new data. This makes formulas dynamic and self-maintaining as your dataset expands - essential for scalable spreadsheet solutions.
❌ The Problem:
✅ Solution:
=XLOOKUP(E2, A:A, B:B, "Not Found")This solution has built-in error handling with the if_not_found parameter (4th argument). Use it instead of IFERROR wrapper for cleaner code. Only use IFERROR when you need to catch other types of errors beyond "not found" scenarios.
Master these XLOOKUP variants:
From basics to advanced - AI generates perfect formulas instantly.