# Thread: How do I find the cell address of the 2nd largest

1. ## How do I find the cell address of the 2nd largest

Hello.

I have a table of integers ranging from cells B3 to Z51, and I want to find the cell address of the second-largest and third-largest value.

=LARGE(B3:Z51, 2) will give me the second-largest value, but how do I find the cell address where that value is located?

Also, when there is the exact same value in two different cells, I want to be able to find both cell addresses. For example, LARGE(B3:Z51, 3) will find the third-largest value, and LARGE(B3:Z51, 4) will find the fourth-largest value. If those two values are the same, how do I find their corresponding addresses?

See, what I'm doing with this data is the ten highest values will be put in another worksheet as a sort of "top ten list".

The format looks a little something like this:

Date.......Alex.........Becky......Carl........Don
1/1/05.....23...........47..........-29.........21
1/8/05.....-2...........16...........30..........-20 (etc) ...
2/2/05.....30..........-50..........40...........65
2/14/05...14..........-20..........15...........34
...

It goes on like that for a long time. With my Top Ten List, I want to have the value along with the date and the person's name, and the only way I can find the corresponding date or person's name is if I know the cell address.

2. ## Re: How do I find the cell address of the 2nd largest

Welcome to the Lounge.
I do not think that it can be done directly with a formula without some type of intermediate calcs (and even that may be cumbersome). I think your best bet is a "user defiend function", you can use this function, for example:

<pre>Option Explicit
Function LookupAddress(rng As Range, lValue As Long, _
Optional bLarge As Boolean = True)

Dim lIndex As Long
Dim lRank As Long
Dim dValue As Double
Dim AWF As WorksheetFunction
Dim lCount As Long
Dim vArray() As Variant
Dim rCell As Range

On Error GoTo ErrHandler
Set AWF = Application.WorksheetFunction
If bLarge Then
dValue = AWF.Large(rng, lValue)
lRank = AWF.Rank(dValue, rng, 0)
Else
dValue = AWF.Small(rng, lValue)
lRank = AWF.Rank(dValue, rng, 1)
End If
lIndex = lValue - lRank + 1

ReDim vArray(1 To rng.Cells.Count)
lCount = 0
For Each rCell In rng
If rCell.Value = dValue Then
lCount = lCount + 1
End If
Next

ReDim Preserve vArray(1 To lCount)
If lCount = 0 Then
ElseIf lIndex > lCount Then
Else
End If
ErrHandler:
If Err.Number <> 0 Then LookupAddress = CVErr(xlErrValue)
Set AWF = Nothing
Set rCell = Nothing
End Function</pre>

Add the function to a module and then use something like:

or explicitly for large (the third parameter is optional):

to get the address of the 2nd largest in the range B3:Z51 or something like: