See if this custome lookup function will do as you need
If you are asking to pull a value from all the sheets, then you will need code to loop through all the worksheets using the FIND method
See this add in from Chip Pearson: Find All VBA Function
Bookmarks