Table of Contents
To use XLOOKUP from another sheet in Google Sheets, you first need to select the cell where you want the result to appear. Then, type in the formula =XLOOKUP( and click on the cell or range of cells that contain the value you want to search for. Next, add a comma and click on the sheet name where the data is located, followed by an exclamation mark (!). Then, select the range of cells where the data is located, add a comma, and select the column number or range where you want the search to be performed. Finally, close the formula with a closing bracket and press enter. The XLOOKUP formula will then search for the specified value in the selected sheet and return the corresponding result.
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: