View Single Post
 
Old 03-29-2020, 07:36 AM
p45cal p45cal is offline Windows 10 Office 2019
Expert
 
Join Date: Apr 2014
Posts: 428
p45cal is just really nicep45cal is just really nicep45cal is just really nicep45cal is just really nice
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, 3 views)

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