Thread: Lookup function in more than one sheet

I need to use the lookup function over a number of sheets in a workbook. For example, if I know what sheet the lookup entry is in, I use =LOOKUP(E3,'62_04'!B2:B26,'62_04'!C2:C26), where the sheet is 62_04. However, I am working with hundreds of sheets and I don't know which one the entry is in. So I would like to use a range of sheets instead of just 62_04. Is there any way to do this? I would think the easiest way would be to use a function that I could enter the lookup entry in, say the number 10010. The function would then search the entire worksheet for 10010, and return the sheet and cell where 10010 is found. Then I can use this as the input to the lookup function. Does any such search function exist?

thanks

If you download and install the free-addin, Morefunc.xll, you'll be able to use the THREED function. If the add-in is not an option, a complex formula without the use of the add-in is possible. If this is the case, how are your sheets named?

An Add-In doesn't work for me, because I have to share this with many other people that will be updating the spreadsheet and I don't want to have to distribute the plugin.
An example of the sheet naming would be 62_00, 62_01,62_03, etc. They always have a major release (62) and a minor release (00).
I am going to play with writing a script that loops through each sheet. In that case, is there a function that will return the sheet name to a cell?

Actually, there's no need for others to install the add-in. It allows you to embed it in your file.