next highest value

Hi Dave and welcome to the Forum

Your question isn't quite clear (and your Forum profile doesn't show which version of Excel you use) but the first part of my answer will suit all versions from 2007.

In the first attached file, the range B2:B17 (shaded orange) contains whole number values between 1 and 39. If all you want to do is find the 2nd highest value, set yellow cell C19 to 2. This is used a the second argument in Excel's LARGE function so blue cell D19 has the formula

=LARGE(B2:B17,C19)

and shows the 2nd highest number in the orange cells, 38. Change C19 to 6 and it will show the 6th highest (27). You get the idea (and can replace C19 above by 2 if you only ever want to find the second highest value).

If however you want to find the second highest after a given number, that's trickier and this second part will suit only users with Microsoft 365, Excel 365, web or phone versions. It uses the SORT function (rather than a helper column) combined with the approximate match version of VLOOKUP.

Yellow cell C1 contains the "known" number, say 38 (as the file is delivered in this Answer). Green cell D2 shows the next highest after C1 in the range. It does so by wrapping a VLOOKUP around a SORT of the range. The formula in D2 is:

=VLOOKUP(C1-0.01,SORT(B2:B17,1,1), 1, TRUE)

The SORT bit in bold creates an array with the values in B2:B17 sorted in ascending order, from smallest to largest.

The part C1-0.01 is the value searched for (so I've taken a small amount off C1) and the TRUE part makes VLOOKUP look for the closest match rather than an exact match. VLOOKUP gives the closest value before it passes the (exact) match with C1 i.e. the next highest value.

Change C1 to 28 say and D2 will show 27 (the one after 28).

The approach will work with non-integers too but you'll need to change the range B2:B17 to suit your purposes (and possibly the subracted amount 0.01 if you have more decimal places). 

REVISION 20 November 2023:

I noticed that you added a file to your question, in which cell C10 is shaded red.

Assuming that (1965.9) is the "known" value you're looking for, the SECOND attached file below, a revised version of your file has this formula in (yellow) cell E2:

=VLOOKUP(C10-0.01,SORT(C:C,1,1), 1, TRUE)

and returns the next highest value (1935.6)  after than in the same column (rather than a defined range).

Change C10 in the formula to another cell in that column and the result will change.

The text in F2 currently shows  "Next highest after 1965.9" (the "known"  value) and I've modified the formula shown in my original response so you don't have to adjust the formula in cell F2 when you change the vcell reference in E2- the replacement bold part below extracts the address (like C10) used in E2 and that "known" value using this formula:

="Next highest after " & INDIRECT(MID(FORMULATEXT(E2), 10,FIND("-",FORMULATEXT(E2),10)-10))

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

Hello Dave and welcome,

I took a different approach from John. I added a sheet (High-Low) to your file to show how you can achieve this using in-cell formulas. I also created a Worksheet_SelectionChange macro to run on your "Sheet1". I also added a small "table" which displays the number being compared (the selected cell in column "C") and its address, and the next highest value (in column "B") and its address.

Here is the macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim LR_high As Long
Dim LR_low As Long
Dim NextHigh As Double
Dim CompValue As Double
Dim CompAddress As Variant
Dim HighCell As Range
Dim SearchRange As Range
Dim HighWhere As Variant

LR_high = Range("B" & Rows.Count).End(xlUp).Row
LR_low = Range("C" & Rows.Count).End(xlUp).Row

' check if the selected cell is in column "C"
If Intersect(Target, Range("C1:C" & LR_low)) Is Nothing Then Exit Sub

CompValue = ActiveCell.Value
'MsgBox CompValue
CompAddress = ActiveCell.Address(False, False)
'MsgBox CompAddress
Range("E3").Value = CompValue
Range("G3") = CompAddress
If Not Intersect(Target, Range("C1:C" & LR_low)) Is Nothing Then

    NextHigh = Application.WorksheetFunction.Large(Range("$B$2:$B$" & LR_high), Application.WorksheetFunction.CountIf(Range("$B$2:$B$" & LR_high), ">" & CompValue))
    'MsgBox NextHigh
    Range("E5") = NextHigh
End If

Set SearchRange = Range("$B$2:$B$" & LR_high).Find(NextHigh, LookIn:=xlValues, lookat:=xlWhole)
If Not SearchRange Is Nothing Then SearchRange.Select

HighWhere = ActiveCell.Address(False, False)
'MsgBox HighWhere
Range("G5") = HighWhere

End Sub

Whichever answer you prefer, John's or mine, please remember to mark it as Selected

Cheers   :-)