Lookup nth largest value
To lookup the nth largest value in a range we need to apply the Excel LARGE function
Example: Lookup nth largest value
|
=LARGE(C5:C9,E5)
|
The formula uses the Excel LARGE function to return the second largest number from the selected range. You can adjust the nth largest number to any you want as long as the range that you are selecting from has at least that amount of values.
|
Dim ws As Worksheet
ws.Range("F5") = Application.WorksheetFunction.Large(ws.Range("C5:C9"), ws.Range("E5"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Values Range: In this example the range of values that the VBA code looks up from is ("C5:C9"), therefore, if using the exact same VBA code ensure that the values are captured in the same range.
nth Largest Value: The formula sources the number of the nth largest value from cell ("E5"), therefore, if using the exact same VBA code ensure that the number of the nth largest value that you want to lookup is captured in cell ("E5").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Values Range: Select the range from which you want to lookup the nth largest value by changing range ("C5:C9") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
nth Largest Value: Select the cell that captures the number of nth largest value that you want to return by changing the reference to cell ("E5") in the VBA code to any cell in the worksheet that doesn't conflict with the formula. In this example the number of the largest value that is to be returned is sourced from a cell that captures the number, however, we can also directly capture the number of the nth largest value to be returned by directly entering the number in the VBA code. To do this using this example we would replace ws.Range("E5") with 2.
To lookup the nth largest value in a range we need to apply the Excel LARGE function.
=LARGE(array, nth_largest_value)
ARGUMENTS
array: The range from which you want to return the nth largest value.
nth_largest_value: A number value that specifies the largest value to return from the array.