View Single Post

03-29-2020, 07:36 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 428

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, 3 views)

Last edited by p45cal; 03-29-2020 at 01:16 PM.