Google Sheets 'Arrayformula' with nested 'Importrange' formula Excel Equivalent?

I'm trying to consolidate individual spreadsheet tabs across multiple spreadsheets into one master sheet that collects all the data for analysis.

Google Sheets has this formula that will display the selected criteria: =ARRAYFORMULA(IMPORTRANGE("Spreadsheet URL","Tab Name!A1:J29")).

That formula is entered into 1 cell (the green cells (one formula per tab) in my consolidation example), and it displays everything on the tab in the selected data range (noted in the formula) across the equivelent number of cells in the new spreadsheet.

1 formula entered into 1 cell, and a whole range of data is entered automatically. It live-updates and requires no maintenance (ie - no copy/paste, no re-consolidating it, no need to enter a vlookup or index formula into every single cell to get the current data).

Original Data Link: https://drive.google.com/open?id=1yy...J4b2LxZio7XqI4
Consolidated Data Link:https://drive.google.com/open?id=1wX...21WHq7ZMpwZH_0

Does Excel have an equivalent option? All original files would be saved in the same server folder.


I tried asking this question in the Excel Formulas & Functions forum, but all I got for replies was "please put this into Excel so we know what you're talking about". If I could do that, I wouldn't be asking for help with this issue.

No, we asked for you to show us some data and what you expected, so we could see what we could come up with in excel for you
(and if you took a gander at the forum rules, you would see that posting duplicate threads is not permitted, but I will let that slide - this time )

https://www.excelforum.com/excel-for...-workbook.html