Formula to use Index and Match across Tabs in Excel

[calof1]

Hi,

i have a workbook that contains the same sheet but for different products. So each tab is set out the same.

I wish to have a summary tab which reads from each tab.

I have attached a spreadsheet with an example.

Can someone please assist.

[#2]

Have you used Power Query before? It's great at this sort of thing? Also, let us know what version of Excel you are using.

[#3]

Hi@Wyn Hopkins 

I have looked at power query briefly before, but yet to implement much practice with it, but keen to learn.

I also currently use version 1902 of excel.

Thanks

[#4]

Easiest way to start off is to turn your tables of data into proper Excel Tables (using Ctrl + t)  however if that isn't an option then use the name box to name the data first (See Range1) and then go to Data > From Table/Range

e.g. 

You can then duplicate this query for each sheet

and simply edit the range name in the source step

Final step is to Append as New

then 

And finally click Close and Load to - connection Only

Final Step

Right click on the Append query and Load to Table

Whenever you want the Append table to update just right click on it and choose Refresh