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.
