How to Use XLOOKUP Across Two Worksheets | XLOOKUP Examples Across Two Sheets

Introduction

This tutorial will guide you through using the XLOOKUP function to perform lookups across two worksheets in Excel. Whether you're working with large datasets or need to reference information from different sheets, XLOOKUP can simplify your tasks and enhance your productivity. We will cover two methods to achieve this, ensuring you can choose the one that best suits your needs.

Step 1: Understanding XLOOKUP Basics

Before diving into the methods, it's important to understand the XLOOKUP function. XLOOKUP is a powerful Excel function that allows you to search a range or an array and return an item corresponding to the first match found.

Key Features of XLOOKUP

• Searches a range or an array.
• Returns corresponding values from another range or array.
• Can handle approximate and exact matches.

Syntax

The basic syntax for XLOOKUP is:

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 array or range to search.
• return_array: The array or range from which to return the result.
• if_not_found: Optional value to return if no match is found.
• match_mode: Optional argument to specify match type.
• search_mode: Optional argument to specify search order.

Step 2: Using XLOOKUP Between Two Worksheets

Now, let's look at how to use XLOOKUP across two different worksheets.

Method 1: Basic XLOOKUP Between Worksheets

1. Open Your Excel Workbook: Ensure you have two worksheets prepared with the necessary data.
2. Select the Cell for Your Formula: Go to the worksheet where you want to display the result.
3. Enter the XLOOKUP Function: In the selected cell, type the XLOOKUP function.
   • For example:

   =XLOOKUP(A2, 'Sheet1'!A:A, 'Sheet1'!B:B)
   

   • In this example, A2 is the lookup value, Sheet1!A:A is the range to search, and Sheet1!B:B is the return range.
4. Press Enter: This will return the corresponding value from the specified worksheet.

Method 2: Using Excel Tables for Dynamic Ranges

Using Excel tables can make your XLOOKUP formulas more dynamic.

1. Convert Your Data Range to a Table:
   • Select your data range in the first worksheet.
   • Go to the Insert tab and click on Table.
   • Ensure the checkbox for "My table has headers" is checked, then click OK.
2. Name Your Table: With the table selected, go to the Table Design tab and give your table a meaningful name (e.g., SalesData).
3. Use XLOOKUP with the Table Name:
   • In the second worksheet, enter the formula using the table name:

   =XLOOKUP(A2, SalesData[Product], SalesData[Price])
   

   • Here, SalesData[Product] is the column where you are looking up the value, and SalesData[Price] is the column from which to return the result.
4. Press Enter: The corresponding price will be displayed in the selected cell.

Conclusion

You have now learned how to use the XLOOKUP function across two worksheets in Excel. By mastering both the basic and dynamic methods using Excel tables, you can efficiently retrieve and analyze data from multiple sheets.

Next Steps

• Practice creating your own XLOOKUP formulas with different datasets.
• Explore additional features of XLOOKUP, such as handling errors or using approximate matches.
• Consider integrating XLOOKUP with other Excel functions to enhance your data analysis capabilities.