Index-Match or Vlookup pulling from multiple sheets

Papers

New Member
Joined
Dec 1, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Hoping you can point me in the right direction. I've recently inherited a workbook that I need to keep up to date. One of the tasks at the moment is to download several reports and combine them into a master sheet. To date, it's been possible to do straightforward cross-sheet lookups (ie, ='Sheet2'!F55) after spending a lot of time sorting the inputs so that everything is in the same order across all the different reports - sheet1 B2 will look at sheet2 b2, sheet 3 b9 and sheet 4 d6, and then sheet1 B3 will look at sheet2 b3, sheet 3 b10 and sheet 4 d7, and so on. (apologies, there has to be a better way to explain this, but I'm not familiar with the board controls)

However, we're now getting to a stage with the reports we're ingesting aren't going to line up neatly any more - data's not going to be as homogenous or reliably sortable into precisely the same order, as the original reports will have missing lines. At the moment, my solution is to manually move the data in the feeder sheets to where the master sheet is expecting to pull from, leaving several blank rows where data hasn't yet been received. It's doable, but it's very clunky and potentially error prone, and if there's a more effective way of running this for the next few years, I'd like to try it.

Every project has a project ID, and all the data in all the reports is organised vertically. It feels like it wouldn't actually be that much hassle (compared to manual faffing) to restructure my master sheet as a vlookup - functionally, what I want is a code that looks for a project number on a specified sheet, and then brings back the data in a specified range of cells to a specified range of cells in the master sheet, rather than the current fixed location links. (preferably, I'd put all this into an actual database, but that's not currently an option)

Questions, then:
a) would you consider it a waste of time to restructure the master sheet? I'm currently pulling data from 3 sheets into 60 columns, and I'm expecting to add another 40-odd columns from another 2 sheets.
b) vlookup or index-match?
c) where to start?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Papers

If you restructure the master sheet to match the new report format then later on they might change the report format again and your master sheet will be out of sync again.

If the data in the reports is connected to reliable headings - you have already mentioned Project ID - then vlookup and index-match should work.

Can you post examples of the master sheet and the report sheets?
 

aghaffar82

Board Regular
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Such a huge file with so many columns, expect it to be darn slow with so many VLOOKUP/INDEX & MATCH, better, dynamic and perfect solution will be to use PowerQuery.
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,122,236
Messages
5,594,984
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top