Excel formula to search across multiple worksheets and return worksheet name

You can use a LOOKUP formula to search for a value [in this example A4] across multiple worksheets and if there is a hit in the specified column range [in this example A2:A100 - but it can cover multiple columns] the worksheet name will be returned.

=LOOKUP(9.99999999999999E+307,1/COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),$A4),SheetList)

Before entering the formula you need to name a range of cells with the names of the worksheets you are searching. So you can just select the cells and then enter a name in the box to the far left of the formula bar. If you need to adjust the range go to the Formulas tab and then click 'Name Manager'.

The formula is not case sensitive. If it finds the same value on more than one worksheet it will not list more than one worksheet name.

Thanks to Aladin Akyurek for posting this formula on the MrExcel.com forum.