INDEX/MATCH that work on Master Sheet to collect data from 12 sheets

p45cal · #1 03-29-2020, 07:36 AM

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)

greatinfoteam · #2 03-30-2020, 12:37 AM

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.

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
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
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