XLOOKUP

Lookup & Reference

(5/5)

XLOOKUP is Excel's modern, flexible lookup function that searches a range for a value and returns a corresponding value from another range. It replaces VLOOKUP, HLOOKUP, and INDEX/MATCH combinations with a simpler, more powerful syntax. XLOOKUP works in any direction and includes built-in error handling.

Syntax & Parameters

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

Required

lookup_value:

The value to search for. Can be a number, text, logical value, or cell reference.

Required

lookup_array:

The array or range to search. This is the range containing the values to find.

Required

return_array:

The array or range to return values from. Can be same or different range than lookup_array.

Optional

if_not_found:

Value to return if lookup_value is not found. If omitted, returns #N/A error.

Optional

match_mode:

Match type: 0=exact (default), -1=exact or next smaller, 1=exact or next larger, 2=wildcard match.

Optional

search_mode:

Search direction: 1=first to last (default), -1=last to first, 2=binary ascending, -2=binary descending.

Returns

Return Value:

Value from return_array corresponding to lookup_value position in lookup_array

Description: Searches for a value and returns a corresponding value from another range with flexible matching and search options

Interactive Examples

Basic XLOOKUP

Simple lookup with default exact match

Input Text:

"A1:A5 names, B1:B5 values"

Formula:

=XLOOKUP("John", A1:A5, B1:B5)

Result:

Value from B where A is "John"

Simplest XLOOKUP. Searches A1:A5 for "John" and returns corresponding value from B1:B5. Much simpler than VLOOKUP!

Interactive Formula Tester

Employee Name:

Formula:

=XLOOKUP("John")

Complete Theory & Understanding

Master the fundamentals of Excel XLOOKUP function

Core Concept

XLOOKUP is Excel's modern replacement for VLOOKUP, HLOOKUP, and INDEX/MATCH combinations. Introduced in Excel 365, XLOOKUP provides a simpler syntax, works in any direction, includes built-in error handling, and offers advanced matching options. It's the recommended lookup function for new projects.

Why Use XLOOKUP?

Replace VLOOKUP/HLOOKUP with simpler syntax
Look up values to the left of lookup column
Exact, approximate, or wildcard matching
Built-in default values for missing data

Key Characteristics

Any Direction

Works left, right, up, or down

XLOOKUP works unlike VLOOKUP which only goes right

Simpler Syntax

Easier than VLOOKUP/HLOOKUP

XLOOKUP(value, lookup, return) vs VLOOKUP(value, table, col, range)

Built-in Defaults

Optional default value parameter

XLOOKUP(..., "Not Found") handles errors

Advanced Options

Match modes and search directions

Match mode -1,1 for ranges, search mode -1 for reverse

Function Anatomy

=XLOOKUP(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Modern Lookups

Replace VLOOKUP/HLOOKUP with simpler syntax

Left Lookups

Look up values to the left of lookup column

Flexible Matching

Exact, approximate, or wildcard matching

Error Handling

Built-in default values for missing data

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

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

Required

lookup_value:

Value to search for

Required

lookup_array:

Range to search in

Required

return_array:

Range to return value from

Optional

if_not_found:

Default value if not found

Optional

match_mode:

0=exact, -1/1=approximate, 2=wildcard

Optional

search_mode:

1=first-last, -1=last-first, 2/-2=binary

Returns

Return Value:

Value from return_array or if_not_found if not found

Description: Modern lookup function that works in any direction with flexible matching

Interactive Examples

Basic XLOOKUP

Simple lookup

Input Text:

"A1:A5 lookup, B1:B5 return"

Formula:

=XLOOKUP("John", A1:A5, B1:B5)

Result:

Value from B

Simplest XLOOKUP syntax

VBA Implementation & Automation

XLOOKUP in VBA

Using XLOOKUP in VBA (Excel 365+)

Sub XLOOKUPExample()
    ' Note: XLOOKUP requires Excel 365 or later
    ' Method 1: Basic XLOOKUP
    Dim result As Variant
    result = Application.WorksheetFunction.XLookup("John", Range("A1:A5"), Range("B1:B5"))
    Range("C1").Value = result
    
    ' Method 2: With default value
    result = Application.WorksheetFunction.XLookup("NotFound", _
        Range("A1:A5"), Range("B1:B5"), "Not Found")
    Range("C2").Value = result
    
    ' Method 3: Left lookup
    result = Application.WorksheetFunction.XLookup("John", _
        Range("B1:B5"), Range("A1:A5"))
    Range("C3").Value = result
    
    ' Method 4: Reverse search (last to first)
    result = Application.WorksheetFunction.XLookup("John", _
        Range("A1:A5"), Range("B1:B5"), , , -1)
    Range("C4").Value = result
    
    ' Method 5: Approximate match
    result = Application.WorksheetFunction.XLookup(85, _
        Range("A1:A5"), Range("B1:B5"), , -1)
    Range("C5").Value = result
    
    ' Method 6: Error handling
    On Error Resume Next
    result = Application.WorksheetFunction.XLookup("X", _
        Range("A1:A5"), Range("B1:B5"))
    If Err.Number <> 0 Then
        Range("C6").Value = "Error: " & Err.Description
        Err.Clear
    Else
        Range("C6").Value = result
    End If
    On Error GoTo 0
End Sub

Business Applications

Replace VLOOKUP

Modern replacement with simpler syntax

=XLOOKUP(Value, LookupRange, ReturnRange)

Left Lookups

Look up values to the left

=XLOOKUP(Value, RightRange, LeftRange)

Error Handling

Built-in default values

=XLOOKUP(Value, Lookup, Return, "Default")

Reverse Search

Find last occurrence

=XLOOKUP(Value, Lookup, Return, , , -1)

Common Issues & Solutions

XLOOKUP not available

Function not recognized or #NAME? error

Check Excel version - XLOOKUP requires 365/2021+

Solution: XLOOKUP requires Excel 365 or Excel 2021 or later. For earlier versions, use VLOOKUP, HLOOKUP, or INDEX/MATCH instead.

XLOOKUP returns #N/A

Lookup value not found

=XLOOKUP(Value, Lookup, Return, "Not Found")

Solution: Verify lookup value exists in lookup_array. Use if_not_found parameter to provide default value: XLOOKUP(..., "Not Found"). Check for typos, case sensitivity, and data types.

Wrong value returned

XLOOKUP returns unexpected value

=XLOOKUP(Value, Lookup, Return, , 0)

Solution: Verify lookup_array and return_array ranges are correct and aligned. Check match_mode - use 0 for exact matches unless you need approximate.

Performance with large ranges

Slow performance with large datasets

=XLOOKUP(Value, Lookup, Return, , 0, 2)

Solution: Limit lookup_array and return_array to necessary data only. For sorted data, consider using binary search mode (2 or -2) for better performance.