Excel XLOOKUP function with formula examples

This tutorial introduces XLOOKUP, Excel's new function for vertical and horizontal lookups. Previously complex tasks, like left lookups, finding last matches, and VLOOKUPs with multiple criteria, are now simplified.

When performing Excel lookups, you might use VLOOKUP, HLOOKUP, INDEX MATCH, or Power Query. XLOOKUP offers a superior, versatile alternative.

XLOOKUP's advantages include vertical and horizontal searches, left and upward lookups, multiple criteria searches, and the ability to return entire rows or columns of data, not just single values. It addresses many limitations of VLOOKUP.

However, XLOOKUP is only available in Microsoft 365, Excel 2021, and Excel for the web.

Key Features and Uses:

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

• Arguments:

  • lookup_value: The value to search for.
  • lookup_array: The range to search within.
  • return_array: The range to return values from.
  • if_not_found (optional): Value returned if no match is found (defaults to #N/A).
  • match_mode (optional): Match type (0/omitted = exact, -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard).
  • search_mode (optional): Search direction (1/omitted = forward, -1 = reverse, 2/-2 = binary search on sorted data).

• Basic Usage: A simple exact match: =XLOOKUP(G4, B4:B8, D4:D8) searches for G4 in B4:B8 and returns the corresponding value from D4:D8.

XLOOKUP vs. VLOOKUP:

XLOOKUP significantly improves upon VLOOKUP in several key areas:

1. Bidirectional Lookup: Performs both vertical and horizontal lookups.
2. Flexible Search Direction: Looks up to the left, right, above, and below.
3. Default Exact Match: Provides exact matches by default, unlike VLOOKUP's default approximate match.
4. Wildcard Support: Allows partial matches using wildcard characters.
5. Reverse Search: Easily finds the last match using search_mode = -1.
6. Multiple Value Returns: Retrieves entire rows or columns.
7. Multiple Criteria: Handles multiple lookup conditions.
8. Error Handling: Includes built-in error handling via the if_not_found argument.
9. Column Insertion/Deletion: Robust to column additions/removals.
10. Improved Performance: More efficient than VLOOKUP.

Advanced XLOOKUP Examples:

• Vertical and Horizontal Lookups: Demonstrated with examples showing how to use the same syntax for both.

• Left Lookup: Easily performs left lookups, a task previously requiring INDEX MATCH.

• Exact and Approximate Matches: Shows how to use match_mode for both exact and approximate lookups.

• Wildcard Matches: Uses wildcard characters (*) and (?) for partial matches.

• Last Match (Reverse Order): Uses search_mode = -1 to find the last occurrence.

• Multiple Value Returns: Returns entire rows or columns of data.

• Multiple Criteria: Uses array operations to handle multiple conditions.

• Nested XLOOKUP (Two-Way Lookup): Performs a two-way lookup by nesting XLOOKUP functions.

• Error Handling (if_not_found): Customizes the error message.

• Case-Sensitive Lookup: Uses EXACT to perform a case-sensitive search.

Troubleshooting:

• XLOOKUP Unavailable: Only works in Microsoft 365, Excel 2021, and Excel for the web.
• Incorrect Results: Check for incorrect cell references (use absolute references like $A$1).
• #N/A Error: Lookup value not found. Consider approximate matching or custom error handling.
• #VALUE! Error: Incompatible array dimensions.
• #REF! Error: Closed workbook referenced.

XLOOKUP is a powerful and versatile function that simplifies many lookup tasks in Excel. This comprehensive guide provides a solid foundation for mastering its capabilities. A practice workbook is available for download.

The above is the detailed content of Excel XLOOKUP function with formula examples. For more information, please follow other related articles on the PHP Chinese website!