|First, a posting tip:|
Before posting example data in the forum, please click on the blue line at the bottom of this post and read the instructions found via that link.
It's hard for us tell which columns your data is in.
As for your question, you can't do what you want with any Excel built in function. You will need to use VBA to create a User Defined Function (UDF) such as the one below.
After pasting this code into a Standard module in the VBA editor, you can enter =SheetMax(Cell Reference) in a cell and it should return the sheet names where the MAX values are found.
The Cell Reference should be the cell in which your MAX value is returned.
The code assumes that your "Risk" values are in Column B of each sheet. Modify the code as required.
Function SheetMax(ByVal Target As Range)
For shts = 1 To Sheets.Count
lastRw = Sheets(shts).Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Sheets(shts).Range("B1:B" & lastRw)
If cell = Target.Value Then
tmpSheetMax = tmpSheetMax & Sheets(shts).Name & ", "
SheetMax = Left(tmpSheetMax, Len(tmpSheetMax) - 2)
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.