Find lowest n values

To find the n lowest values in a set of data, you can use the SMALL function. This can be combined with INDEX as shown below to retrieve associated values. In the example shown, the formula in F7 is: =SMALL(bid,E7) Note: this worksheet has two named ranges: bid (C5:C12), and company (B5:B12), used for convenience and readability only.

April 14, 2025 · 1 min · 77 words · Anthony Ward

Table of Contents

Explanation

TheSMALL functionretrieves the smallest values from data based on a given rank.

MATCH returns the location of the value to INDEX as the row number.

INDEX then retrieves the corresponding value from the named rangecompany(B5:B12).

Excel formula: nth smallest value

You canuse a formula like thisto break times.

from a set of numeric data.

you might use INDEX to retrieve individual values, or entire rows and columns.

Excel formula: nth largest value

MATCH supports approximate and exact matching, andwildcards(* ?)

Often, MATCH is combined with the…

Excel formula: Sum bottom n values

Excel formula: Break ties with helper column and COUNTIF

Excel SMALL function

Excel INDEX function

Excel MATCH function