X Lookup Function

 

XLOOKUP Function in Excel

Introduction
The XLOOKUP function is one of Excel's most versatile lookup tools, introduced to simplify and enhance the search experience. It can look up values vertically or horizontally, replace older functions like VLOOKUP and HLOOKUP, and allows you to return results without the limitations of fixed data ranges.

Syntax

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

• lookup_value: The value you want to search for.
• lookup_array: The range to search for the lookup value.
• return_array: The range where you want to return the result.
• [if_not_found]: Optional. Value to return if the lookup value isn’t found.
• [match_mode]: Optional. 0 for an exact match (default), -1 for exact match or next smallest, 1 for exact match or next largest, and 2 for a wildcard match.
• [search_mode]: Optional. 1 for a first-to-last search (default), -1 for a last-to-first search.

Example Scenarios

1. Basic XLOOKUP
   Imagine you have a list of employees with their IDs and want to find an employee’s name using their ID.

   =XLOOKUP("A102", A2:A100, B2:B100)
   

   This formula will search for the ID "A102" in the range A2:A100 and return the corresponding name from B2:B100.

2. Handling Errors with [if_not_found]
   Use the [if_not_found] argument to avoid errors when a value isn’t found.

   =XLOOKUP("A200", A2:A100, B2:B100, "Not Found")
   

3. Dynamic Range Lookups
   Unlike VLOOKUP, XLOOKUP doesn't require data to be sorted and can handle lookups from both left to right and right to left.

4. Wildcard Matching
   Find items based on partial matches.

   =XLOOKUP("*Sales*", A2:A100, B2:B100, "No Match", 2)
   

Why Use XLOOKUP?
With XLOOKUP, you get greater flexibility and control over your data searches, making it a go-to function for modern data analysis in Excel.