INDEX＋MATCH関数で複数該当の２番目以降を抽出する方法

ここでは、INDEX＋MATCH関数で条件に複数該当する中から２番目以降を抽出する方法を解説します。

なお、このページで紹介するのは「〇番目を抽出する方法」ですが、以下のページでは「複数すべて」抽出する方法を解説しています。

⇒ 指定条件に合うデータ全てを一覧表からエクセル関数で順に抽出する方法

知っている方は多いと思いますが、一応確認しておきます。

INDEX＋MATCH関数の組み合わせで抽出できるのは、基本的に「上から検索して１番最初に一致した行のみ」です。

上の表は、社員の基本情報が載った一覧です。

例えば、この表からINDEX関数とMATCH関数の組み合わせで、「正社員の人（E列が〇の人）」を抽出してみます。

ですが、INDEX＋MATCHでは「上から検索して一番最初の行」しか抽出できません。

ですから、普通の使い方だとどんなに頑張っても、№1の中橋さんしか抜き出せないのです。

なお、範囲を2番目以降の人からになるよう手で直す方法もありますが、面倒で現実的ではありません。

INDEX＋MATCH関数で「複数該当する２番目以降を抽出できる数式」の、形と作り方を見ていきます。

２－１．数式の形は

数式の形は、次の通りです。

※ 条件が一つの場合

「INDEX＋MATCH」と言いながら、MATCH関数がないのはスルーしてください。

引数は、「抽出列」、「条件式」、「条件範囲」、「番目」の４つです。

２－２．引数の指定の仕方

各引数の指定の仕方を見ていきます。

２－２－１．抽出列には「抽出したい列」を指定する

「抽出列」には、「元データのうちで抽出したい列全体」を指定します。

例えば、元データからA列を抽出したい場合には「A:A」、D列を抽出したい場合には「D:D」となります。

２－２－２．条件式には「条件範囲や条件値」を指定する

「条件式」は、

条件範囲 → 比較演算子 → 条件値

の順で指定します。

「条件範囲」には、「条件値との一致等を判定する範囲」を「絶対参照で」指定します。

特に「絶対参照」とは、「数式を他にコピーしても参照先を変えない状態」のことで、ここでは「セル番号1つに『$』を2つ」付けます。

例えば、A1からA11までを判定したい場合には、「$A$1:$A$11」となります。

「比較演算子」は「=」や「>」、「<」、「>=」、「<=」、「<>」のどれかを入れます。

「条件値」には、そのままですが「条件値」を入れます。

条件値の指定の仕方は、後で詳しく説明します。

例えば、条件範囲を「A5からA10」、条件を「〇か？」としたい場合、それぞれ次のようになります。

　・条件範囲：$A$5:$A$10（『$』を4つ付けて絶対参照に）

　・比較演算子：=

　・条件値："〇"

つなげると条件式は、

$A$5:$A$10="〇"

となります。

２－２－３．条件範囲は条件式と同じく入れる

条件式で指定した「条件範囲」と全く同じに入れます。「絶対参照にする」のも同じです。

厳密に言うと、列番号は条件範囲と違っても問題ありません。ただ、全く同じものを入れると覚えたほうが分かりやすいと思います。

２－２－４．番目には「抽出したい番目」を入れる

「番目」とは、1番目、2番目などの順番のことです。抽出したい「番目」を、「数値」または「数値の入ったセル番号」で指定します。

数値の場合には、「1」、「10」などそのまま入れます。

セル番号を入れる場合には、やはり「$」を2つ付けて絶対参照にします。

条件値は「文字列」、「数値」、「セル番号」などの「値の型」によって指定の仕方が違います。

３－１．値を入力する場合

条件値に文字列などの「値」を入力する場合、型によって次のように指定します。

（１）文字列

条件値を「"」で囲みます。

　例）条件値が「〇」→”〇"

（２）数値

そのまま入力します。

　例）条件値が「100」→100

（３）日付

DATEVALUE関数を使い「"」で囲みます。

　例）条件値が「2022年1月1日」→DATEVALUE("2022/1/1”)

（４）条件値が時間

TIMEVALUE関数を使い「"」で囲みます。

　例）条件値が「18:00」→TIMEVALUE("18:00")

３－２．セル番号を入力する場合

セルに入力された値を条件値にする場合には、条件値の型に関わらず、セル番号をそのまま入力します。

ただし、「$」を2つ付けて絶対参照にします。

セル番号入力後にF4キーを１回押しましょう。

　例）セルA1に条件値が入っている→$A$1

例を用いて数式を作ってみます。

先ほども見た社員一覧表です。この表から、「A列が〇の2番目の人」の氏名や年齢、性別を抽出してみます。

（１）以下の数式をコピーし、結果を表示したい範囲の左上のセルに貼り付ける

【コピーする数式】

※ 複数条件の場合には、この後紹介する数式をコピーし貼り付けます

（２）「抽出列」の文字を消し、元データのうち抽出したい列をクリックする

※ 列番号（赤矢印部分）を押すと簡単です

（３）「条件式」の文字を消し、まず「条件範囲」を指定する

※マウスの左ボタンを押しながら選択すると簡単です

（４）キーボードのF4キーを１回押し、「$」を4つ付ける

※ F4キーはキーの最上段にあります

（５）比較演算子を入力する

※ 条件に応じて「=」か「<>」、「>」などの記号を入れます

（６）条件値を入力する

※ 条件値の入れ方は、この後詳しく解説します

（７）「条件範囲」の文字を消し、「条件範囲」を指定する

（８）キーボードのF4キーを１回押し、「$」を4つ付ける

（９）「番目」の文字を消し、「番目」を指定する

（１０）セル番号の場合はF4キーを1回押し、$を2つ付ける

（１１）Enterキーを押す

（１２）セルの右下にカーソルを当てる

（１３）「+」マークになったら右方向に左ドラッグすれば完了

※ 左ドラッグとは、マウスで左クリックを押しながらカーソルを移動させることです

結果、セルH5に入れた数式は次のようになります。

=IFERROR(INDEX(B:B,1/LARGE(INDEX(($A$5:$A$14="〇")/ROW($A$5:$A$14),0),$G$5)),"")

慣れるととても簡単です。

この数式は、「『番目』を変えれば条件に該当するデータを複数抽出することが可能」です。

数式の他の部分は変えず、「番目」を2や3…などとすることで、2番目に該当するデータも、3番目に該当するデータも抽出できるのです。

つまり、該当するデータをいくつでも抜き出すことができるということです。

以上、参考になれば幸いです。

次のページでは、「複数条件で任意の『番目』を抽出する方法」を解説します。

⇒ 次のページへ行く

INDEX＋MATCH関数の、他の記事へのリンクはこちらです。

• INDEX＋MATCH関数その他の記事へのリンクはこちら

• ◎基本的な使い方
• ◎空白以外を詰めて全部抽出する方法
• ◎条件値指定のポイント
• ◎含む条件の指定の仕方
• ◎AND（どれにも当てはまる）で指定する方法
• ◎OR（どれかに当てはまる）で指定する方法
• ◎２番目、３番目以降の値を抽出する方法
• ◎横方向を検索し値を抽出する方法
• ◎別シートからマッチングしたデータを抽出する方法
• ◎別ファイルからデータを抽出する方法
• ◎範囲条件に合うデータを全部抽出する方法
• ◎含む条件に合うデータを全部抽出する方法
• ◎OR条件に合うデータを全部抽出する方法
• ◎#N/Aエラーが起こる原因と回避法
• ◎うまくいかない、おかしい時の対処法

関数の解説記事一覧へはこちら