Looking up values across sheets and return not only the value but also the sheet name

Hi

I wonder if you guys could help me with my issue. I am looking up the largest value across various sheets (1 to 99) with the following formula:

=LARGE('1:99'!$B$1:$B$50;$C3)

That correctly returns the largest value in range B1:B50 across sheets 1 to 99.

However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.

Any suggestions?

Thanks a lot!

Have a look here:
http://www.excelforum.com/excel-form...heet-name.html

Perhaps you can use the formula in post 3:
=INDEX($A$2:$A$13,MATCH(TRUE,COUNTIF(INDIRECT("'"&$A$2:$A$13&"'!C14"),B2)>0,0))

you probably don't need to do it with the sheet names listed in A2:A13 - you can probably replace this with row(A1:A99) to generate the 1:99 numbers.

I think if this method doesn't work for you, you will need a UDF.

I guess I need a UDF... what is this?

You can write a custom function (user-defined-function) with vba. For example, something like:

Public Function find_sheet_name(rng As Range)
Dim count, found_rng As Range
count = 1
Do Until Not found_rng Is Nothing And count < 100
    Set found_rng = Sheets("" & count).Range("B1:B50").Find(rng)
    count = count + 1
Loop
If found_rng Is Nothing Then
    find_sheet_name = "Not Found"
Else
    find_sheet_name = found_rng.Parent.Name
End If
End Function

might work if you are working with unique values. Press Alt+F11 to open the vba editon, then select insert->module and paste the code in the new module.

Then on your worksheet, you can use it as you would another function, so:
=find_sheet_name(A1)
should return the sheet on which the value in A1 is stored.