Table of Contents
XLOOKUP is a powerful function in Google Sheets that allows you to search and retrieve data from another sheet within the same spreadsheet. This function can be used by entering the sheet name and cell range of the data you want to retrieve, along with the search criteria. This allows for easy access to data from multiple sheets without having to manually switch between them. By using XLOOKUP, you can efficiently organize and analyze data from different sheets in your Google Sheets.
Google Sheets: Use XLOOKUP From Another Sheet
You can use the following syntax with XLOOKUP in Google Sheets 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 current 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 in Google Sheets
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:
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 6 in the Assists column:
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets: