#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Credit goes to Jeff Lenning
Take a look at: |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
I have no idea how to use INDEX/MATCH for that.
|
#5
|
||||
|
||||
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)) Last edited by p45cal; 03-29-2020 at 01:16 PM. |
#6
|
||||
|
||||
Grrrr.
Crossposting without linking (and multiposting). |
#7
|
|||
|
|||
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. |
Tags |
index, indirect, match |
|
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 |