Lookup & ReferenceLevel 4: Green (Lookups & Data Models)Difficulty: Intermediate

How to Use XLOOKUP in Excel

The modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.

Formula Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)

Argument	Requirement	Purpose & Details
lookup_value	Required	The value you want to search for.
lookup_array	Required	The single row or column where the value is searched.
return_array	Required	The range of cells that holds the value you want to retrieve.
if_not_found	Optional	Text or value to return if no match is found (avoids #N/A).
match_mode	Optional	0 for exact match (default), -1 for next smaller, 1 for next larger, 2 for wildcard.
search_mode	Optional	1 to search first-to-last, -1 to search last-to-first, 2 for binary search.

Suppose you have the following dataset in Excel cells A1:D4:

EmpID	Employee Name	Department	Salary
E101	Rahul Sharma	Finance	₹85,000
E102	Pooja Verma	Marketing	₹65,000
E103	Aman Patel	Engineering	₹1,20,000

Applied Formula:Result: ₹1,20,000

=XLOOKUP("E103", A2:A4, D2:D4, "Not Found")

How it works: Searches for 'E103' in Column A and immediately retrieves the matching salary from Column D.

#N/A

Why it happens: The lookup value was not found in the lookup range.

Fix: Provide a friendly fallback using the 4th argument: =XLOOKUP(A2, B:B, C:C, 'Not Found').

#VALUE!

Why it happens: The lookup_array and return_array have different dimensions (e.g. 5 rows vs 6 rows).

Fix: Ensure both ranges span the exact same number of rows or columns.

Ready to Master Excel?

Learn XLOOKUP alongside 500+ formulas, hands-on practice workbooks, and verifiable certificates.