First create a list of all your sheets somewhere in the Master List (you can hide the column afterwards)....
So, assuming you created this list in Master List!P2:P6, enter formula in Master List, C2:
=IF(F3="","",VLOOKUP(F3, INDIRECT("'"&INDEX($P$2:$P$6, MATCH(TRUE, COUNTIF(INDIRECT("'"&$P$2:$P$6&"'!$B$3:$B$17"),F3) >0,0))&"'!$B$3:$C$17"),2,0))
confirm with CTRL+SHIFT+ENTER not just ENTER.
|