Lookup nth smallest value
To lookup the nth smallest value in a range we need to apply the Excel SMALL function
Example: Lookup nth smallest value
|
=SMALL(C5:C9,E5)
|
The formula uses the Excel SMALL function to return the second smallest number from the selected range. You can adjust the nth smallest 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.Small(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 Smallest Value: The formula sources the number of the nth smallest value from cell ("E5"), therefore, if using the exact same VBA code ensure that the number of the nth smallest 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 smallest value by changing range ("C5:C9") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
nth Smallest Value: Select the cell that captures the number of nth smallest 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 smallest 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 smallest 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 smallest value in a range we need to apply the Excel SMALL function.
=SMALL(array, nth_smallest_value)
ARGUMENTS
array: The range from which you want to return the nth smallest value.
nth_smallest_value: A number value that specifies the smallest value to return from the array.