Excel関数逆引き集 | n番目に一致した値 → SMALL＋FILTER

Excel

2021.02.092025.12.19

概要

「一致する値が複数あるとき、n番目の一致だけ取りたい」
こういう場面は、名簿・売上履歴・ログデータなどでよくあります。

Microsoft 365 なら、FILTER で一致する行を全部取り出し、SMALL で n番目を指定するという方法が最もシンプルで強力です。

この組み合わせで「n番目の一致」を簡単に取得できます。

例：A2:A100 の中から「F2 と一致する値」だけを取り出す

=FILTER(A2:A100, A2:A100=F2)

これで「一致した値の一覧」が返ってきます。

FILTER の結果は配列なので、SMALL で n番目を取り出せます。

=SMALL(FILTER(A2:A100, A2:A100=F2), n)

n に 1 を入れれば「1番目の一致」、
n に 2 を入れれば「2番目の一致」になります。

=SMALL(FILTER($A$2:$A$100, $A$2:$A$100=$F$2), G2)

文字列は SMALL では扱えないため、行番号を使う方法が定番です。

=INDEX($A$2:$A$100,
       SMALL(FILTER(ROW($A$2:$A$100)-ROW($A$2)+1, $A$2:$A$100=F2), G2))

ポイントをかみ砕くと：

これで文字列でも「n番目の一致」が取れます。

=SMALL(FILTER(ROW(A2:A100), A2:A100=F2), G2)

A2:D100 の中で「商品名（C列）が F2 と一致する n番目の行」を返す：

=INDEX($A$2:$D$100,
       SMALL(FILTER(ROW($A$2:$A$100)-ROW($A$2)+1, $C$2:$C$100=F2), G2),
       SEQUENCE(1,4))

例：一致が2件しかないのに、n=3 を指定すると #NUM! になります。
ユーザーに優しくするなら IFERROR を使います。

=IFERROR(
   SMALL(FILTER(A2:A100, A2:A100=F2), G2),
   "該当なし"
)

例：A2:A100 を抽出するなら、条件も A2:A100 で揃えること。
ずれると配列エラーになります。

文字列の n番目一致は必ず「行番号を SMALL で取る → INDEX で値を返す」という流れになります。

=SMALL(FILTER(A2:A100, A2:A100=F2), 1)

=SMALL(FILTER(A2:A100, A2:A100=F2), G2)

=INDEX($A$2:$A$100,
       SMALL(FILTER(ROW($A$2:$A$100)-ROW($A$2)+1, $A$2:$A$100=F2), G2))

=SMALL(FILTER(ROW(C2:C100), C2:C100=F2), G2)

=INDEX($A$2:$D$100,
       SMALL(FILTER(ROW($A$2:$A$100)-ROW($A$2)+1, $C$2:$C$100=F2), G2),
       SEQUENCE(1,4))

n番目の一致を取るときの基本パターンは次の2つです。

=SMALL(FILTER(範囲, 条件), n)

=INDEX(範囲, SMALL(FILTER(行番号配列, 条件), n))

この2つを覚えておけば、
「n番目の売上」「n番目の一致する商品」「n番目の該当行」など、
実務でよくある“複数一致の中から順位を取る”処理がすべて書けるようになります。