Forum Discussion
Formula to use Index and Match across Tabs in Excel
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.
3 Replies
- 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.
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
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
