Excel tutorials

Search all worksheets and when you find a value from the conditions, display the Sheet Name Tab of the worksheet

In this Excel tutorial I will show how we can search for multiple worksheets, find the value as a condition, and return the Sheet Name Tab of the worksheet where the value is located.

On the 'Master' worksheet in 'A' column there is a value as a condition. In this case in the picture below we want to display, on which all worksheets there is a value of 100. So, we want to return the Sheet Name Tab. Note that there are multiple worksheet in the active workbook.
In the column 'H' there is a named cell range. This range contains a list of all worksheets in an active workbook. Notice the picture below the 'H2:H4' range, this range I named as "AllSheets". We will use this name in a formula that will return the SheetName Tab of the worksheet containing the particular condition.

Searching for values across multiple worksheets

Worksheet 'JAN'

On this worksheet are the source data for the month of 'January'. Note in the figure below that the values can be arranged in the 'A2:E7' cell range. Remember this range because it will appear in the formula.

The first worksheet in an active workbook

Worksheet 'FEB'

On this worksheet are the source data for the month of 'February'. Note in the figure below that the values can be arranged in the 'A2:E7' cell range. Remember this range because it will appear in the formula.

The second worksheet in an active workbook

Worksheet 'MAR'

On this worksheet are the source data for the month of 'March'. Note in the figure below that the values can be arranged in the 'A2:E7' cell range. Remember this range because it will appear in the formula.

Third worksheet in an active workbook

Lookup Value Across Multiple Worksheets and Display the Sheet Name Tab

An example of the Excel formula in the 'B2' cell in the first image of this tutorial.

In the 'B2' cell, set the ARRAY formula below. This formula searches all worksheets in an active workbook and as a result displays the name of the worksheet where the corresponding value is located. Note! that some arguments contain the named cell range 'AllSheets' instead of the address range. We look for the results in the 'A1:E7' range on each worksheet. The condition is in the '$A2' cell. If you are wondering why exactly the '$' character in front of 'A2' then look at the tutorial on absolute and relative addresses. This is important for copying the formula.

=IFERROR(INDEX(AllSheets;1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&AllSheets&"'!A1:E7");$A2));ROW(INDIRECT("1:"&COUNTIFS(AllSheets;"?*"))));COLUMN(A1))));"")

Copy this formula to the right, if you want to display the Sheet Name Tab of other Worksheets containing the requested value as a condition.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.