Excel XLOOKUP Function Overview

Introduction to XLOOKUP

• XLOOKUP is a new and improved lookup function in Excel.
• It can replace VLOOKUP, HLOOKUP, and INDEX MATCH.
• This lecture will cover five examples to showcase how to leverage XLOOKUP effectively.

Examples Overview

1. Handling items not found (error handling)
2. Using wildcards in lookup
3. Performing a horizontal lookup
4. Conducting a two-way lookup
5. Looking for an approximate match

Example 1: Handling Errors in XLOOKUP

• Scenario: Calculating maximum bonus for employees based on their names.
• Steps:
  • Use XLOOKUP to bring over bonus percentages from the "B_Master" tab.
  • Arguments for XLOOKUP:
    • lookup_value: Employee name
    • lookup_array: Range of names in "B_Master"
    • return_array: Bonus percentages range
    • Error Handling: Use if_not_found argument to return 0 for names that are not found.
    • Final formula multiplies percentage with yearly salary to calculate maximum bonus.

Example 2: Horizontal Lookup with XLOOKUP

• Scenario: Adjusting for horizontal data arrangement.
• Steps:
  • Similar to the first example but selecting ranges for a horizontal lookup.
  • Set lookup_array to horizontal range and return_array accordingly.
  • Handle errors using if_not_found for missing names.

Example 3: Using Wildcards in Lookup

• Scenario: Finding first names using a partial last name.
• Steps:
  • Insert wildcard character in lookup_value (e.g., "Willard").
  • Formula: Use & to concatenate wildcard with the name.
  • Arguments: Adjust match_mode to accept wildcard characters instead of looking for an exact match.

Example 4: Two-Way Lookup with XLOOKUP

• Scenario: Retrieve maximum bonus based on employee selection.
• Steps:
  • Utilize data validation for employee selection and field selection.
  • Use nested XLOOKUP to determine which array to return based on selected field.
  • Calculate the maximum bonus for the selected employee using the selected data field.

Example 5: Approximate Match with XLOOKUP

• Scenario: New bonus scheme based on salary ranges.
• Steps:
  • Set lookup_value to the employee's yearly salary.
  • Arguments: Use match_mode set to -1 for approximate match (next smaller item).
  • Calculate new maximum bonus based on the determined percentages.

Conclusion

• XLOOKUP offers a versatile way to handle various lookup scenarios in Excel.
• It simplifies error handling, supports wildcards, and allows for two-way lookups and approximate matches.
• Encouragement to try out XLOOKUP in future Excel tasks.
• Call to action: Like and subscribe for more Excel tips.