Microsoft Office Forums INDEX/MATCH that work on Master Sheet to collect data from 12 sheets
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-26-2020, 02:19 AM
 greatinfoteam Windows 8 Office 2016 Novice Join Date: Mar 2020 Posts: 4
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
 example.xlsx (295.6 KB, 3 views)
#2
03-27-2020, 06:52 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,041

Credit goes to Jeff Lenning

Take a look at:
Attached Files
 3d_VLOOKUP.xlsx (327.3 KB, 4 views)
#3
03-28-2020, 08:00 AM
 greatinfoteam Windows 8 Office 2016 Novice Join Date: Mar 2020 Posts: 4

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
03-28-2020, 11:51 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,041

I have no idea how to use INDEX/MATCH for that.
#5
03-29-2020, 07:36 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 392

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
 msofficeforums44643_3d_VLOOKUP.xlsx (322.2 KB, 2 views)

Last edited by p45cal; 03-29-2020 at 01:16 PM.
#6
03-29-2020, 07:56 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 392

Grrrr.
#7
03-30-2020, 12:37 AM
 greatinfoteam Windows 8 Office 2016 Novice Join Date: Mar 2020 Posts: 4

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post greatinfoteam Excel 1 03-26-2020 12:54 AM robertjohn81 Excel 1 01-19-2017 11:20 AM inreality01 Excel 49 01-06-2016 03:09 PM anvqui Excel Programming 9 06-16-2015 01:35 PM alliage Excel 1 09-01-2011 05:23 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:57 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top