How to Use XLOOKUP in Excel – Step-by-Step Tutorial for Beginners

Introduction

This tutorial will guide you through using the XLOOKUP function in Excel, a modern alternative to VLOOKUP. XLOOKUP simplifies data retrieval, making it a valuable tool for anyone looking to enhance their Excel skills. By the end of this tutorial, you'll be able to use XLOOKUP confidently in various scenarios, including filling in order amounts and handling horizontal lookups.

Step 1: Entering the XLOOKUP Formula

To start using XLOOKUP, you need to enter the formula correctly. Here’s how:

1. Click on the cell where you want the result to appear.
2. Type the formula in the following format:

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

3. Replace:
   • lookup_value with the value you want to find.
   • lookup_array with the range of cells to search through.
   • return_array with the range of cells from which to return a value.
   • Use optional parameters for additional functionality.

Example

Assuming you want to find the order amount for "Product A":

=XLOOKUP("Product A", A2:A10, B2:B10)

Step 2: Understanding lookup_array and return_array

Get familiar with the key components of the XLOOKUP function:

• lookup_array: This is the range of cells where Excel will search for the lookup_value.
• return_array: This is the range of cells that contains the data you want to retrieve based on the found lookup_value.

Practical Tip

Ensure both arrays (lookup and return) are the same size; otherwise, Excel will return an error.

Step 3: Handling if_not_found, match_mode, and search_mode

Learn how to utilize optional parameters to enhance your lookup:

1. if_not_found: Specify what to return if the lookup value is not found. For example:

   =XLOOKUP("Product X", A2:A10, B2:B10, "Not Found")
   

2. match_mode: Control how the function matches the lookup_value. Options include:
   • 0: Exact match (default)
   • -1: Exact match or next smaller
   • 1: Exact match or next larger
3. search_mode: Determine the search direction:
   • 1: Search from first to last (default)
   • -1: Search from last to first

Step 4: Using Formula Help for Parameters

Excel offers a helpful tool to guide you through the formula:

1. Click on the cell where you want to enter the formula.
2. Press the Fx icon next to the formula bar.
3. A dialog box will appear, allowing you to fill in the parameters step-by-step with explanations for each.

Step 5: Copying XLOOKUP Across Rows

To apply the XLOOKUP function across multiple rows:

1. After entering the formula in one cell, click on the fill handle (small square at the bottom-right corner of the cell).
2. Drag down to fill the formula in adjacent cells.
3. Use absolute references (e.g., $A$2:$A$10) to lock specific ranges if needed.

Common Pitfall

Avoid dragging without absolute references when your lookup table or return data remains constant across rows. This can lead to incorrect results.

Step 6: Performing Horizontal Lookups with XLOOKUP

XLOOKUP can also be used for horizontal data:

1. Ensure your data is laid out in rows instead of columns.
2. Use XLOOKUP in the same way as vertical lookups, adjusting the arrays accordingly.

Example

To look up a value in a horizontal array:

=XLOOKUP("January", A1:F1, A2:F2)

Conclusion

You have now learned how to utilize the XLOOKUP function in Excel, enhancing your data retrieval capabilities. Remember to practice entering formulas, using optional parameters, and applying the function across multiple rows and in horizontal scenarios. For further practice, download the workbook provided in the video description and explore more complex data sets. Happy Excel-ing!