Excel Find a Match in All Sheets

Using Excel formula search across sheets for a particular value in a workbook.  While this is not traditional this is useful if you want to find a value across all of the sheets in your Excel workbook or wanted a listing of all of the sheets where that value appeared.  Excel will search each sheet for a given phrase or value.  For example, if searching Range A1:A5 on all of the sheets for the text string Car.  

Find Match on Sheets in Excel

All of the sheet names which we might want to use are in the area on the left.  This is the formula to achieve the Result.  

=IFERROR(INDEX(List,SMALL(IF(COUNTIF(INDIRECT("'"&List&"'!A1:A5"),$C$11)>0,ROW(List)-MIN(ROW(List))+1),ROWS($A$1:$A1))),"")

In this example, "Car" is contained in a list on Sheets (2,3,4).  

The above is an Excel array entered formula so after it is entered hit Ctrl Shift Enter.