Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-26-2020, 02:19 AM
greatinfoteam greatinfoteam is offline INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 8 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2016
Novice
INDEX/MATCH that work on Master Sheet to collect data from 12 sheets
 
Join Date: Mar 2020
Posts: 4
greatinfoteam is on a distinguished road
Exclamation INDEX/MATCH that work on Master Sheet to collect data from 12 sheets

Hey all Excel experts


Someone told me to post my question as a New Thread so i am posting my problem here again with all details

I am working with 12 sheets and then have Master sheet. By defining these 12 sheets to a name range, I am able to do it with a formula that only look in 1st sheet and not working for the remaining sheets.
to understand my problem please see the attached file.
Attached Files
File Type: xlsx example.xlsx (295.6 KB, 7 views)
Reply With Quote
  #2  
Old 03-27-2020, 06:52 PM
xor xor is offline INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 10 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Credit goes to Jeff Lenning

Take a look at:
Attached Files
File Type: xlsx 3d_VLOOKUP.xlsx (327.3 KB, 9 views)
Reply With Quote
  #3  
Old 03-28-2020, 08:00 AM
greatinfoteam greatinfoteam is offline INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 8 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2016
Novice
INDEX/MATCH that work on Master Sheet to collect data from 12 sheets
 
Join Date: Mar 2020
Posts: 4
greatinfoteam is on a distinguished road
Default

Thanks dear
but i dont want to use vlookup bcoz it cannot look to the right side of a column and the file is only as a reference and original files have many columns some are to left and some are to right of the base column. So need to use INDEX/MATCH

thanks
Reply With Quote
  #4  
Old 03-28-2020, 11:51 AM
xor xor is offline INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 10 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I have no idea how to use INDEX/MATCH for that.
Reply With Quote
  #5  
Old 03-29-2020, 07:36 AM
p45cal's Avatar
p45cal p45cal is online now INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 10 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Building on xor's work, column E of the Style Detail sheet in the attached eliminates VLookup.


Note a variation of xor's formula has been used (makes no difference to the results in this case):
=VLOOKUP($B6,INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$A$6:$B$31"),$B6)>0),0))&"'!$A$6:$B$31"),2,FALSE)
to:
=VLOOKUP($B6,INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$A$6:$A$31"),$B6)>0),0))&"'!$A$6:$B$31"),2,FALSE)


edit:

after discovering hidden sheets with a different arrangement of columns the new formula would be, in C6:
Code:
=INDEX(INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$C$6:$C$40"),$B6)>0),0))&"'!$E$6:$E$40"),
MATCH($B6,INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$C$6:$C$40"),$B6)>0),0))&"'!$C$6:$C$40"),0))
(array-entered, copied down)
Attached Files
File Type: xlsx msofficeforums44643_3d_VLOOKUP.xlsx (322.2 KB, 7 views)

Last edited by p45cal; 03-29-2020 at 01:16 PM.
Reply With Quote
  #6  
Old 03-29-2020, 07:56 AM
p45cal's Avatar
p45cal p45cal is online now INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 10 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Grrrr.
Crossposting without linking (and multiposting).
Reply With Quote
  #7  
Old 03-30-2020, 12:37 AM
greatinfoteam greatinfoteam is offline INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Windows 8 INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Office 2016
Novice
INDEX/MATCH that work on Master Sheet to collect data from 12 sheets
 
Join Date: Mar 2020
Posts: 4
greatinfoteam is on a distinguished road
Default

OMG
You are great man dear. I am very much thankful to you and adding you as my friend, trainer, teacher or guider. Thanks again a lot dear.
Reply With Quote
Reply

Tags
index, indirect, match

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX/MATCH that work on Master Sheet to collect data from 12 sheets greatinfoteam Excel 1 03-26-2020 12:54 AM
INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Help Needed With Index/Match To Compare And Pull Data From One Sheet To Another robertjohn81 Excel 1 01-19-2017 11:20 AM
Complex calculation from multiple sheets to master sheet inreality01 Excel 49 01-06-2016 03:09 PM
INDEX/MATCH that work on Master Sheet to collect data from 12 sheets Excel, transfer data from Master Sheet to sub sheets, using key word from column anvqui Excel Programming 9 06-16-2015 01:35 PM
If two geographical data match in two sheets, copy unique id/code found in one sheet alliage Excel 1 09-01-2011 05:23 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft