Return to Excel Formulas List

XLOOKUP Between Worksheets or Spreadsheets – Excel & Google Sheets

This tutorial will demonstrate how to lookup from another worksheet or spreadsheet using XLOOKUP in Excel. If your version of Excel does not support XLOOKUP, read how to use the VLOOKUP instead.

xlookup between sheets Main

XLOOKUP from Another Worksheet

To reference a range from a different sheet, click the tab name at the bottom of Excel and select your range. Excel will automatically add the appropriate cell reference:

xlookup between sheets 01

If you’d like, you can manually type the cell reference. Notice how it’s displayed above.

However, this reference format will not work if there are spaces or special characters. If there are spaces or special characters, the cell reference will look like this instead (notice the apostrophes):

xlookup between sheets 02

XLOOKUP from Another Workbook

If the data set is stored in a sheet from another workbook, we must also add the workbook reference.

xlookup between sheets 03

 

If the workbook is open, only the workbook name will be displayed. When the workbook is closed, the full path will be shown:

xlookup between sheets 04