How can I use XLOOKUP from another sheet in Excel?

By /

XLOOKUP is a powerful function in Excel that allows users to search for and retrieve specific data from a table or range of cells. It can be used to find and retrieve information from another sheet in Excel by referencing the sheet name in the formula. This allows for quick and efficient access to data from multiple sheets within a single workbook. By using XLOOKUP, users can easily perform cross-sheet data analysis and streamline their workflow. This function is especially useful for large datasets and complex spreadsheets, making it a valuable tool for data manipulation and organization.

Excel: Use XLOOKUP From Another Sheet

You can use the XLOOKUP function in Excel to look up a specific value in a range and return the corresponding value in another range.

You can use the following syntax with XLOOKUP to look up a value from one sheet and return the corresponding value from another sheet:

=XLOOKUP(A2, Sheet2!$A$2:$A$11, Sheet2!$B$2:$B$11)

This particular formula looks up the value in cell A2 of the currently active sheet in the range A2:A11 of Sheet2 and returns the corresponding value in the range B2:B11 of Sheet2.

The following example shows how to use this syntax in practice.

Example: How to Use XLOOKUP From Another Sheet

Suppose we have one sheet called Sheet1 that contains information about the points scored by basketball players on various teams:

Suppose we have another sheet called Sheet2 that contains information about the assists for basketball players on various teams:

Suppose we would like to look up the team names in Sheet1 within Sheet2 and return the value from the Assists column.

We can type the following formula into cell C2 of Sheet1 to do so:

=XLOOKUP(A2, Sheet2!$A$2:$A$11, Sheet2!$B$2:$B$11)

We can then click and drag this formula down to each remaining cell in column C:

Excel XLOOKUP from another sheet

The XLOOKUP function returns the value in the Assists column from Sheet2 that corresponds to the value in the Team column from Sheet1.

For example, if we check Sheet2 we will see that the Mavs do indeed have a value of 5 in the Assists column.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. How can I use XLOOKUP from another sheet in Google Sheets?
  2. How do I use XLOOKUP from another sheet in Google Sheets?
  3. How can I utilize the XLOOKUP function with a wildcard in Excel?
  4. How can I use XLOOKUP to sum all matches in Excel?
  5. How to use XLOOKUP to Sum All Matches in Excel?
  6. How to use XLOOKUP with Wildcard in Excel?
  7. How do I use XLOOKUP in VBA?
  8. How to Use XLOOKUP in Google Sheets (With Example)
  9. How do I mirror a table from one sheet to another in Excel?
  10. How can I create a table of contents in an Excel sheet?