To use XLOOKUP with multiple sheets, you can specify the sheet name followed by an exclamation point (!) in front of the cell range. For example, if you have a table of data in Sheet1, in range A1:C5 and you want to look up a value in column A and return the corresponding value in column C, the XLOOKUP formula would be:
=XLOOKUP(A7, Sheet1!A1:A5, Sheet1!C1:C5)
Similarly, if you have another table of data in Sheet2, in range A1:C5 and you want to look up a value in column A and return the corresponding value in column C, the XLOOKUP formula would be:
=XLOOKUP(A7, Sheet2!A1:A5, Sheet2!C1:C5)
In both formulas, A7
is the lookup value, Sheet1!A1:A5
or Sheet2!A1:A5
is the lookup array, and Sheet1!C1:C5
or Sheet2!C1:C5
is the return array.