Learning XLOOKUP in Excel

Introduction

• Focus on learning the XLOOKUP function in Excel, specifically in Excel as part of Microsoft 365.
• XLOOKUP does not work in Excel 2016 or 2019, but is available in Excel 2021.

Data Setup

• Left-hand side: List of client names and order amounts.
• Right-hand side: Selection of client names for which order amounts need to be filled.

Using XLOOKUP

1. Start the Formula:

   • Type =XLOOKUP() and respect the case (uppercase/lowercase both work).

2. Parameters of XLOOKUP:

   • Lookup Value: Value to match, e.g., client name.
   • Lookup Array: List of possible values to search within, e.g., column A.
   • Return Array: Value to return from another column once a match is found, e.g., column B.
   • Optional Parameters:
     • If not found: Default value if the lookup value isn't found.
     • Match Mode: Defines matching type (exact match, next smaller/larger).
     • Search Mode: Specifies search order (first to last, last to first).

3. Applying the Formula:

   • Input the necessary parameters and press Enter to commit.

4. Troubleshooting:

   • Click on the FX icon next to the formula for parameter descriptions.
   • Use the "Insert Function" helper for additional guidance.

5. Copying the Formula:

   • Use the drag handle in the cell’s bottom-right corner to copy.
   • Fix potential issues with cell references by using F4 to lock them.

Advanced Usage

• Horizontal Lookup:
  • Perform horizontal lookups by modifying the parameters appropriately.
  • Lock your lookup and return arrays using the same method (F4).

Final Tip

• XLOOKUP can be an evolution from VLOOKUP because it allows more flexibility, including looking both horizontally and vertically.