はじめに：「2番目」が見つからない！そんな経験ありませんか？
Excelで検索といえば、VLOOKUP関数やMATCH関数がおなじみですよね。
でも、彼らには共通した「弱点」があることをご存知でしょうか？
それは、「一番最初に見つけたデータしか教えてくれない」ことです。
「最後に一致するデータ」なら、LOOKUP関数やXMATCHの逆順検索でなんとかなります。
しかし、「2番目に一致するデータ」となると、途端に難易度が跳ね上がります。
「なぜ難しいの？」
標準的な検索関数は、『見つかったらそこで終了！』という動きをするからです。
「1回見つけても無視して、次を探せ！」なんて命令、普通はできませんよね？
今回は、そんな厄介な「2番目の検索」を、Excelのバージョンに合わせて6つのアプローチで攻略します。
古き良き配列数式から、最新のLAMBDA関数まで。
さあ、あなたのExcelにはどの魔法が効くでしょうか？ワクワクしながら読み進めてください！
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
準備：今回のターゲット
まずはデータを用意しましょう。
A1セルを起点に、以下のシンプルなリストを作成してください。
={"名前";"c";"d";"a";"b";"c";"a";"b"}
【ミッション】
このリスト（A2:A8）の中から、2回目に出てくる “a” の位置を探し出してください。
3行目に1回目の”a“、6行目に2回目の”a“があるので、正解は「6」（上から6番目）となります。
6つのアプローチで、「2番目」を特定せよ！
1. 王道の古典テク（Excel 2007以前～）
まずは、配列数式（CSE数式）を使った、昔からある最もポピュラーな方法です。
多くの先輩Excelユーザーが愛用してきた鉄板テクニックです！
数式と解説
=SMALL(IF(A2:A8="a",ROW(A2:A8)-ROW(A1)),2)
※古いExcelでは、入力後に Ctrl + Shift + Enter を押して確定する必要があります。
【登場する関数の解説】
- IF(論理式, 真の場合, [偽の場合])：条件を満たすかどうかで値を返します。
- ROW([範囲])：セルの行番号を返します。
- SMALL(配列, 順位)：データの中で、小さい方から指定した順位（k番目）の値を返します。
【ロジックの分解】
1. IF(A2:A8="a",ROW(A2:A8)-ROW(A1))
「もし”a”なら行番号を、違うならFALSEを返せ！」と命令します。
結果：{FALSE; FALSE; 3; FALSE; FALSE; 6; FALSE}
“a“の場所だけ数字が入りましたね！
2. SMALL(..., 2)
この配列の中から、2番目に小さい数字を取り出します。
FALSEは無視されるため、数値の中で2番目に小さい「6」が返されます。
弱点： データ量が多いと計算が重くなりますが、ロジックは明快です！
2. INDEX & MATCHのコンビ（Excel 2007以前～）
これは関数パズル好きにはたまらない、トリッキーな方法です。
配列数式を使わずに実現できるのが強みです。
数式と解説
=MATCH("a",A2:A8,0)+MATCH("a",INDEX(A2:A8,MATCH("a",A2:A8,0)+1):A8,0)
【登場する関数の解説】
- MATCH(検索値, 検索範囲, [照合の種類])：範囲内で値を探し、その相対的な位置（何番目か）を返します。
- INDEX(配列, 行番号, [列番号])：指定された位置の値を返します。（が、今回は異なる使い方をします！）
【ロジックの分解】
1. 1つ目のMATCH: MATCH("a",A2:A8,0)
最初の”a”の位置「3」を見つけます。
2. INDEX(...):A8: ここが大注目ポイント！
INDEX(A2:A8, 3+1) は、A2:A8の4番目のセル（A5）を指します。
これをコロン:でA8と繋ぐことで、A5:A8 という「1つ目の”a”の直後から最後までの範囲」を動的に作り出しているのです！
INDEX関数が「値」ではなく「セル参照」を返す性質を利用した技です。
3. 2つ目のMATCH: その新しい範囲（A5:A8）の中で、再び”a”を探します。
新しい範囲の中で”a”は「3番目」に見つかります。
4. 足し算: 最初の位置「3」＋ 新しい位置「3」＝「6」
驚きポイント：
「INDEXから後ろ」という範囲指定ができることを知っていると、Excel上級者から一目置かれますよ！
3. MMULTで行列計算（Excel 2007以前～）
「数学で殴る」とはこのこと！
行列計算を使って、累積カウントを行います。
MMULT関数については、Excel MMULT関数で合計・順位・平均を出す7つの方法にて紹介しています！
数式と解説
=MATCH(2,MMULT((ROW(A2:A8)>=TRANSPOSE(ROW(A2:A8)))*1,(A2:A8="a")*1),0)
【登場する関数の解説】
- MMULT(配列1, 配列2)：2つの配列の行列積を返します。
- TRANSPOSE(配列)：配列の行と列を入れ替えます。
【ロジックの分解】
1. (A2:A8="a")*1: “a”がある場所を1、それ以外を0にした縦ベクトルを作ります。{0;0;1;0;0;1;0}
2. (ROW... >= TRANSPOSE(ROW...)): これで「下三角行列（左下半分が1の行列）」を作ります。これは「累積和」を計算するための数学的な定石です。
3. MMULT(…): 行列積を計算すると、「その行までに”a”が何回出てきたか」という累積カウントの配列が生まれます。
結果：{0;0;1;1;1;2;2}
4. MATCH(2, …, 0): 累積カウントが初めて「2」になった場所を探します。
それが2番目の”a”の位置「6」です。
4. AGGREGATEでエラー回避（Excel 2010以降）
SMALL関数を使いたいけど、（古いExcelで）Ctrl+Shift+Enterは面倒くさい…そんなあなたに！
数式と解説
=AGGREGATE(15,6,(ROW(A2:A8)-ROW(A1))/(A2:A8="a"),2)
【登場する関数の解説】
- AGGREGATE(集計方法, オプション, 範囲, [順位])：エラーを無視したり、非表示行を無視したりできる万能集計関数です。
AGGREGATE関数については、フィルターをかけても番号順！Excelでズレない連番を作る方法でも扱っています！
【ロジックの分解】
1. (ROW(A2:A8)-ROW(A1))/(A2:A8="a")
・”a”の行：行番号 ÷ TRUE(1) ＝ 行番号
・違う行：行番号 ÷ FALSE(0) ＝ #DIV/0! エラー
わざとエラーを出すのがポイントです！
2. AGGREGATE(15, 6, ..., 2)
・15: SMALL関数と同じ機能。
・6: エラー値を無視するオプション。
・最後の2: 2番目に小さい値を取得。
エラーを無視して数値だけを拾ってくれるので、非常にスマートです。
5. FILTER関数（Excel 2021以降）
新しいExcelを使っているなら、これが最強かつ最も簡単です。もう難しいことを考える必要はありません！
数式と解説
=INDEX(FILTER(SEQUENCE(ROWS(A2:A8)),A2:A8="a"),2)
【登場する関数の解説】
- FILTER(配列, 含む)：条件に合うデータだけを抽出します。
- SEQUENCE(行数, [列数], [開始], [目盛り])：連番の配列を作成します。
- ROWS(配列)：行数を返します。
【ロジックの分解】
1. SEQUENCE(ROWS(A2:A8)): 1から7までの連番を作ります。
2. FILTER(連番, A2:A8=”a”): 連番の中から、”a”の行番号だけを抽出します。
結果：{3; 6}
3. INDEX(…, 2): 抽出された結果の「2番目」を取り出します。
「抽出して、n番目を取る」。やりたいことがそのまま数式になっていますね！
6. LAMBDA + SCAN (Microsoft 365)
最後は、最新のプログラミング的アプローチです。
累積計算をスマートに記述します。
数式と解説
=XMATCH(2,SCAN(0,A2:A8="a",LAMBDA(acc,x,acc+x)))
【登場する関数の解説】
- XMATCH(検索値, 検索範囲, [一致モード], [検索モード])：MATCH関数の進化版。
- SCAN(初期値, 配列, LAMBDA(…))：配列を走査して累積値を計算します。
- LAMBDA(パラメータ, 計算式)：独自の関数処理を定義します。
【ロジックの分解】
1. SCAN(0,A2:A8="a",LAMBDA(acc,x,acc+x)): 配列を上から順にスキャンして、累積値を計算します。
・acc (Accumulator): 累積値。これまでの合計を溜めておく場所です。
・x: 現在の行の値（TRUEなら1、FALSEなら0）
・acc+x: 累積値に現在の値を足していきます。
結果：{0;0;1;1;1;2;2} （MMULTの時と同じ累積配列ができます！）
2. XMATCH(2, …): 累積値が「2」になる最初の場所を探します。
なぜMATCHではなくXMATCH？
ここでMATCH関数を使う場合、完全一致にするためにMATCH(2, ..., 0)と、第3引数に「0」を指定する必要があります。
しかし、XMATCH関数は「デフォルトで完全一致」という素晴らしい性質を持っています。
そのため、引数を省略してXMATCH(2, ...)とスッキリ書けるのです。
最新関数ならではのメリットですね！
まとめ：結局、どれが一番おすすめ？
6つの方法を紹介しましたが、実務での個人的なイチオシは…、
1番目の「SMALL & IF」です！
=SMALL(IF(A2:A8="a",ROW(A2:A8)-ROW(A1)),2)
「えっ、一番古いやつ？」と思われるかもしれません。
しかし、この数式は「条件に合う行番号を集めて、小さい順に並べる」というロジックが非常に明確で、何をしているかが一目で分かります。
また、古いExcelから最新のExcelまで、どの環境でも確実に動くという互換性の高さは、実務において最強の武器になります。
LAMBDAやSCANも未来的でカッコいいですが、この程度のミッションなら、わざわざ持ち出すまでもないかもしれませんね！（笑）
ぜひ、あなたの環境に合わせて「2番目」を見つけ出してみてくださいね！
