Match a specific cell by an identifying number across multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2598163%22%20slang%3D%22en-US%22%3EMatch%20a%20specific%20cell%20by%20an%20identifying%20number%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2598163%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I'm%20looking%20to%20create%20a%20master%20spreadsheet%20from%20multiple%20tabs%20in%20excel.%20Specifically%2C%20I%20need%20to%20pull%20the%20total%20cost%20from%20each%20tab%20and%20match%20to%20the%20serial%20number%20on%20the%20master%20tab.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Each%20tab%20has%20a%20serial%20number%20in%20the%20same%20cell%20(H5)%20that%20can%20be%20matched%20to%20the%20master%20sheet%3C%2FP%3E%3CP%3E-%20Each%20tab%20has%20the%20cost%20I%20want%20to%20pull%20onto%20the%20master%20sheet%20in%20the%20same%20cell%20(I30)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20the%20index%20and%20match%20functions%20but%20I'm%20not%20super%20familiar%20with%20these.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Microsoft%20Office%20365%20ProPlus.%20I'm%20happy%20to%20provide%20additional%20info%20needed!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2598163%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2600669%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20specific%20cell%20by%20an%20identifying%20number%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2600669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1115127%22%20target%3D%22_blank%22%3E%40lizgallagher%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20is%20often%20the%20case%2C%20seeing%20what%20you're%20attempting%20to%20describe%20would%20be%20very%20helpful.%20That%20is%20to%20say%2C%20if%20you%20could%20attach%20a%20copy%20of%20the%20actual%20workbook%20(or%20a%20mockup%2C%20if%20the%20actual%20contains%20confidential%20or%20proprietary%20data)%2C%20that%20would%20help%20us%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20I%20don't%20think%20%3CSTRONG%3EINDEX%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EMATCH%3C%2FSTRONG%3E%20are%20what%20you%20want%20at%20all%3B%20they're%20really%20good%20for%20when%20the%20data%20all%20resides%20in%20a%20table.%20It's%20possible%20that%20you%20could%20use%20%3CSTRONG%3EINDIRECT%3C%2FSTRONG%3E.%20Here's%20a%20resource%20that%20explains%20the%20INDIRECT%20function%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-indirect-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20is%20also%20possible%20that%20we%20might%20recommend%20a%20different%20way%20to%20organize%20your%20data.%20It's%20a%20common%20mistake%20(among%20those%20relatively%20new%20to%20Excel)%20to%20create%20separate%20spreadsheets%20for%20different%20items%20being%20tracked%20(as%20it%20appears%20you've%20done%20here).%20Excel%20actually%20works%20very%20well%20when%20a%20single%20database%20is%20created%20incorporating%20data%20on%20all%20those%20different%20items...it's%20easier%20to%20extract%20data%20on%20a%20%22per-item%22%20basis%20from%20a%20single%20comprehensive%20database%20than%20it%20is%20to%20consolidate%20the%20data%20from%20disparate%20spreadsheets%20(which%20is%20what%20it%20appears%20you%20might%20be%20doing).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%2C%20yes%2C%20there%20are%20times%20when%20what%20works%20best%20is%20a%20hybrid%20of%20those%20two%20extremes.%20The%20basic%20point%20remains%3A%20it%20would%20be%20easier%20to%20give%20you%20specific%20suggestions%20if%20we%20were%20able%20to%20see%20a%20sample%20of%20the%20actual%20workbook%20you've%20created.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2604672%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20a%20specific%20cell%20by%20an%20identifying%20number%20across%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2604672%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20response!%20I%20have%20attached%20a%20mock%20report.%20I%20am%20trying%20to%20match%20the%20serial%20number%20in%20column%20A%20on%20the%20master%20tab%20to%20the%20serial%20numbers%20in%20H5%20on%20the%20individual%20site%20tabs%2C%20to%20then%20take%20the%20cost%20in%20cell%20I30%20and%20add%20it%20to%20column%20D%20on%20the%20master%20tab.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20resource%20for%20the%20INDIRECT%20function%20-%20I%20will%20give%20that%20a%20try%20now!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello! I'm looking to create a master spreadsheet from multiple tabs in excel. Specifically, I need to pull the total cost from each tab and match to the serial number on the master tab. 

 

- Each tab has a serial number in the same cell (H5) that can be matched to the master sheet

- Each tab has the cost I want to pull onto the master sheet in the same cell (I30)

 

I'm trying to use the index and match functions but I'm not super familiar with these. 

 

I am using Microsoft Office 365 ProPlus. I'm happy to provide additional info needed! 

 

5 Replies

@lizgallagher 

 

As is often the case, seeing what you're attempting to describe would be very helpful. That is to say, if you could attach a copy of the actual workbook (or a mockup, if the actual contains confidential or proprietary data), that would help us help you.

 

That said, I don't think INDEX and MATCH are what you want at all; they're really good for when the data all resides in a table. It's possible that you could use INDIRECT. Here's a resource that explains the INDIRECT function: https://exceljet.net/excel-functions/excel-indirect-function

 

But it is also possible that we might recommend a different way to organize your data. It's a common mistake (among those relatively new to Excel) to create separate spreadsheets for different items being tracked (as it appears you've done here). Excel actually works very well when a single database is created incorporating data on all those different items...it's easier to extract data on a "per-item" basis from a single comprehensive database than it is to consolidate the data from disparate spreadsheets (which is what it appears you might be doing).

 

And, yes, there are times when what works best is a hybrid of those two extremes. The basic point remains: it would be easier to give you specific suggestions if we were able to see a sample of the actual workbook you've created.

Please provide a sample worksheet stating your issue. It would be better to see and solve it

@mathetes 

 

Thanks for your response! I have attached a mock report. I am trying to match the serial number in column A on the master tab to the serial numbers in H5 on the individual site tabs, to then take the cost in cell I30 and add it to column D on the master tab. 

 

Thank you for the resource for the INDIRECT function - I will give that a try now! 

@nidhiachhaa9 

 

Thanks for your response! I have attached a mock report. I am trying to match the serial number in column A on the master tab to the serial numbers in H5 on the individual site tabs, to then take the cost in cell I30 and add it to column D on the master tab. 

@lizgallagher

You already have the location and city in columns B and C. Can we use that, or do you want formulas in those columns too?

 

If we can use column B, it's simply

 

=INDIRECT("'"&B2&"'!I30")

 

in D2, then fill down.