Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

next highest value



I have a known value in column C 

I would like to find the next highest value compared to (C value ) in column B



Dave, Please see REVISION 20 November 2023 to my Answer below- it now has a revision to the example file you added to your question
John_Ru (rep: 5722) Nov 20, '23 at 7:27 am
Just to clarify my solution.
When testing it, all you need to do is (on "Sheet1") select the cell in column "C" with the value you want to compare and the macro does the rest. The 'table' I added then lets you know which value you chose to compare and which value in column "B" is the next highest to it.
WillieD24 (rep: 372) Nov 20, '23 at 5:59 pm

You chose the Forum user name Makingadifference and got two valid Answers, neither of which you marked as Selected (in accordance with the Forum rules.

Please note that you are "making a difference" here: I'm getting tired of unresponsive users so I'm thinking more seriously of retiring from the Forum.
John_Ru (rep: 5722) Nov 22, '23 at 1:25 pm

I was considering making a similar comment. If Dave wants to live up to his forum name he needs to comment on our answers and lets us know if one of them is suitable or if another approach is needed. As you have told several other posters, we do this voluntarily (without any compensation) and truly appreciate recognition for our efforts.
In the future, when a poster doesn't respond, I will give up on them and not help them should them post again.
WillieD24 (rep: 372) Nov 22, '23 at 6:03 pm
@Willie- fair point. I think I'll naturally tend give up hope on users who don't respond for three weekdays (excluding Saturday and Sunday)

@Don- you'll detect that Willie and me are tired of non-responsive users but I don't want to create a personal list of non-responsive users e.g. those who pose several question but select none.

Two things- 1) does that data exist on the site- a list of users and info like their reputations, questions posed, responses made? 2) Given Willie and I often spend time but get no response, is there a way to reward contributors for the effort of creating answers, e.g. 2 points if  an answer or competing answer isn't selected? 
John_Ru (rep: 5722) Nov 22, '23 at 6:28 pm
Add to Discussion



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


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.



Thank You so much for the reply I will work with this and see the results

Thank You
Makingadifference Nov 17, '23 at 8:22 pm
Okay but, as Willie said,  please remember to mark it as Selected  whichever Answer you prefer. 

Kindly note that in my second suggestion, D3 shows the known value which D2 compares to (in case you need D2,:D3 to be moved away from the known value cell).
John_Ru (rep: 5722) Nov 18, '23 at 2:19 am
Add to Discussion

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   :-)



Thank You so much for the reply I will work with this and see the results

Thank You
Makingadifference Nov 17, '23 at 8:23 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login