Excel WhisperExcel Whisper

How to Use XLOOKUP Function in Excel

Complete guide to Excel's most versatile lookup function with practical examples

XLOOKUP is Excel's newest and most versatile lookup function, designed to overcome the limitations of VLOOKUP and HLOOKUP. Unlike its predecessors, XLOOKUP can search in any direction, handle errors gracefully, and perform exact or approximate matches with powerful wildcards. Whether you're looking up customer information, matching product data, or creating dynamic references, XLOOKUP provides a more flexible and efficient solution.

Try XLOOKUP Online

Test XLOOKUP function directly in your browser - faster and easier than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

XLOOKUP Function Guide

Comprehensive instructions for mastering XLOOKUP

Function Syntax

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

Steps to Use

1

Select the cell where you want the result

2

Enter =XLOOKUP( to start the function

3

Specify what you want to look up (lookup_value, can be a cell reference like A2)

4

Select the column or range to search in (lookup_array, e.g. B2:B20)

5

Choose the column or range containing the return values (return_array, e.g. C2:C20)

6

Optional: Add a custom value for when no match is found, like "Not found"

7

Optional: Specify match mode (0=exact, -1=less than, 1=greater than, 2=wildcard)

8

Optional: Set search order (1=first-to-last, -1=last-to-first)

9

Press Enter to complete the formula

Use Cases

Two-Way Lookups

Search data in any direction without column restrictions

Multiple Condition Matching

Look up values based on multiple criteria

Error Handling

Customize responses when no match is found

Tips & Notes

  • 1XLOOKUP can search in any direction - left, right, up, or down with no restrictions
  • 2Lookup and return arrays can be different sizes as long as they're aligned properly
  • 3When using wildcards (* and ?) for partial matches, set match_mode to 2
  • 4Avoid #N/A errors by setting an appropriate [if_not_found] parameter
  • 5Match mode 0 (exact match) is suitable for most cases and offers best performance
  • 6For date or numeric lookups, use -1 or 1 to find the nearest value
  • 7Set search_mode to -1 to find the last occurrence instead of the first
  • 8XLOOKUP can return multiple values as an array - ensure cells to the right or below are empty

Frequently Asked Questions about XLOOKUP

Common questions and solutions for Excel XLOOKUP function

The #NAME? error typically occurs because XLOOKUP is only available in Excel 365, Excel 2021, and newer versions. If you're using an older version of Excel, you'll need to use alternative functions like VLOOKUP, INDEX/MATCH, or upgrade to a newer Excel version.

XLOOKUP improves upon VLOOKUP in several ways: 1) It can search in any direction, not just left-to-right, 2) It doesn't require sorting for approximate matches, 3) It offers better error handling with custom messages, 4) It can return multiple values, 5) It can perform two-way lookups, and 6) It has more powerful matching options including wildcards and exact/approximate matches.

For a two-column lookup (matching based on two criteria), use: =XLOOKUP(1, (criteria1_range=lookup_value1)*(criteria2_range=lookup_value2), return_range). This formula uses array multiplication to find rows where both conditions are true, then returns the corresponding value from the return range.

XLOOKUP itself isn't case-sensitive, but you can make it case-sensitive by combining it with the EXACT function. Use: =XLOOKUP(TRUE, EXACT(lookup_array, lookup_value), return_array). This will only match cells that exactly match the case of your lookup value.

Set the search_mode parameter to -1 to search from the end of the range: =XLOOKUP(lookup_value, lookup_array, return_array, "Not found", 0, -1). This will find the last occurrence of your lookup value instead of the first one.

Use the [if_not_found] parameter to provide a custom message or value when no match is found: =XLOOKUP(lookup_value, lookup_array, return_array, "No match found"). This is more user-friendly than Excel's default #N/A error. You can also return a zero, blank, or any other value that makes sense for your data.

XLOOKUP supports wildcards with the * (any sequence of characters) and ? (any single character). For example: =XLOOKUP("Smith*", names_range, return_range, "Not found", 2) will find names starting with 'Smith'. Make sure to set match_mode to 2 to enable wildcard matching.

Yes, when your return_array spans multiple columns or rows, XLOOKUP can return multiple values as an array. To spill results across multiple cells, simply ensure the cells to the right or below are empty. For multiple columns: =XLOOKUP(lookup_value, lookup_array, return_array, "Not found") where return_array references multiple columns.