Lookup nth largest value with criteria
To lookup the nth largest value in a range with criteria we need to apply the Excel LARGE function
Example: Lookup nth largest value with criteria
|
The formula uses the Excel LARGE and IF functions to return the second largest number from the selected range with a criteria of "Maths". 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 against the specified criteria. You can also change the criteria that you want it to match by changing the value in cell F5 to any value in the selected criteria range.
Note: given this is an array formula, when the formula is written you need to press Control + Shift + Enter . This will convert the formula into an array formula and insert {}. |
Dim ws As Worksheet
ws.Range("H5").FormulaArray = "=LARGE(IF(C5:C12=F5,D5:D12),G5)"
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 ("D5:D12"), 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 ("G5"), 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 ("G5").
Criteria Range: In this example the criteria range that the VBA code looks up from is ("C5:C12"), therefore, if using the exact same VBA code ensure that the criteria values are captured in the same range.
Criteria Value: The formula sources the criteria from cell ("F5"), therefore, if using the exact same VBA code ensure that the criteria is captured in cell ("F5").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("H5") 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 ("D5:D12") 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 ("G5") 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 G5 with 2.
Criteria Range: Select the range from which you want to lookup the criteria by changing range ("C5:C12") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Criteria Value: Select the cell that captures the criteria that you want to search against by changing the reference to cell ("F5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula. In this example the criteria is sourced from a cell, however, we can also directly capture the criteria value by directly entering the criteria in the VBA code, with double quotation marks. To do this using this example we would replace ("F5") with "Maths".
To lookup the nth largest value in a range with criteria we need to apply the Excel LARGE function.
{=LARGE(IF(criteria_rng=criteria_value,values_rng),nth_largest_value)}
criteria_rng: The range which is populated with the criteria values.
criteria_value: The criteria value that you want to filter for.
values_rng: 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.
ADDITIONAL NOTES
Note 1: This is an array formula. To convert a formula into an array formula the formula must be written using Control + Shift + Enter .