こんにちは、HARUです！

今回は、実務で出くわす機会の多い「検索関数」の使い方を演習していきます。

一言で「検索関数」といっても、内容が盛り沢山かつ順をおってご説明した方が理解が深まりやすいので、以下のスリーステップに分けて段階的に投稿していきます。
①VLOOKUP関数
②INDEX関数・MATCH関数
③XLOOKUP関数

前回のVLOOKUP関数に続けて、この記事では「INDEX関数・MATCH関数」を解説します。

VLOOKUP関数の諸課題を解決してくれる便利な関数の組み合わせなので、ぜひ最後までご一読ください！

↓VLOOKUP関数の徹底解説記事はこちら↓

INDEX関数・MATCH関数の構成

はじめに、INDEX関数とMATCH関数それぞれの役割と基本構成を見ていきます。

INDEX関数

indexには、索引、指数、指標といったいくつかの意味がありますが、Excelにおいては「索引」のニュアンスで使われます。

INDEX関数を挿入してみます。
通常のワークシート関数は引数のガイダンスが１つだけ表示されますが、INDEX関数には２つの検索方法があります。

第１引数が「配列」となっている配列形式と、「参照」となっているセル範囲形式です。
ビジネスの現場でよく使われるのは、配列形式です。 

第１引数「配列」は、情報を見つけ出す検索対象範囲です。
VLOOKUP関数における第２引数「範囲」と同じ役割です。

第２引数「行番号」には、取り出したい情報が上から数えて何行目にあるか、その値を指示します。
ワークシートの最端からではなく、あくまで第１引数の「配列」内で何番目に位置しているか、がポイントです。

"HR005"は配列内で上から5番目にあるため、「5」と入力します。

第３引数「列番号」には、取り出したい情報が左からか数えて何列目にあるか、その値を指示します。
こちらもワークシートの最端からではなく、第１引数「配列」内で何番目に位置しているか、がポイントです。

今数式を入力しているセルに取り出したい"登録時期"は配列内で左から3番目にあるため、「3」と入力します。

結果、"HR005"の"登録時期"である「2015年1月」が返ります。

ただし実務では、「何行目の何列目のデータ」なんて探し方はほとんどしませんよね。
サンプル数が少なく、かつ取り出したい情報の位置がわかっているのであれば、検索関数も使わずにお目当てのデータを目視でピックアップすれば良いだけです。

どちらかというと、縦にも横にも膨大なデータ量の範囲から、どこにあるかわからない情報を取り出したいシーンで、今回直接入力した行番号や列番号のような固定値ではなく、"HR005"の"登録時期"といった検索値で指示できることが理想です。

とはいえ、INDEX関数単独では、こうしたサーチができません。

MATCH関数

ここで効力を発揮するのがMACTH関数です。
MATCHは、調和する、似合う、競合との試合、のように２つ以上のものが交わる関係を指すことが多いですが、Excelでは派生して「一致する」のニュアンスでおさえておきましょう。

MATCH関数は、前回の記事でVLOOKUP関数を右へコピーする際、第３引数「列番号」を自動更新させるために使いました。
検索範囲の中で検索値が上または左から数えて何番目にあたるか、その位置を取り出せる関数でしたね。

MATCH関数を挿入します。
第１引数「検査値」には、位置情報を調べたいデータを指示します。
今回は"HR005"の場所を取得したいのでI3セルを参照します。

第２引数「検査範囲」は、第１引数「検索値」が含まれる範囲を指示します。
今回はマスターデータにおいて"HR005"が含まれる管理番号の範囲を参照します。

第３引数「照合の種類」は、VLOOKUP関数の第４引数「検索方法」と同じです。完全一致の「0」を入力します。

結果を見てみると、管理番号の範囲で"HR005"が位置する「5」番目が返されます。

検索範囲を横方向に参照し、左から何番目にあるかも同じように取り出せます。
第１引数「検査値」にJ2セルを参照し、"登録時期"の位置情報を検索します。

第２引数「検査範囲」には、マスターデータにおいて"登録時期"が含まれる見出しの範囲を参照します。
第３引数「照合の種類」は、完全一致の「0」を入力します。

結果として、見出しの中で"登録時期"が位置する「３」番目が返されます。

それぞれ「5」行目の「3」列目ということで、先ほどINDEX関数に入力した固定値と同じ値です。

このMATCH関数をINDEX関数にネストすれば、固定の値ではなく関節的に検索値を指示してサーチできる、ということです。

INDEX＆MATCH

欄外にテスト入力したMATCH関数を、前述のINDEX関数に入れ込んでいきます。

"HR005"が管理番号の範囲で何行目にあるかを求めたMATCH関数は、INDEX関数における行番号の役割を担ってもらうので、INDEX関数の第２引数にネストします。

"登録時期"が見出しの範囲で何列目にあるかを求めたMATCH関数は、INDEX関数における列番号の役割を担ってもらうので、INDEX関数の第３引数にネストします。

HR005の登録時期情報が取得できました。

参照形式の変更

この数式を他の範囲にもコピーしていきます。
前回のVLOOKUP関数でも演習した通り、このまま複製すると検索値や検索範囲がズレてしまいます。

相対参照となっている部分をしかるべき参照形式に変換しておきましょう。この切り替えは[F4]で行うんでしたね。

参照演算子の":"(コロン)を挟んでセル範囲を参照している引数は、縦横いずれにコピーする場合でも一切ズレて欲しくないので、絶対参照にします。

管理番号"HR005"を検索値としているI3セルの参照は、下へコピーする分にはスライドして良いですが、右へコピーするときはズレて欲しくありません。

そのため、列を固定する複合参照にします。
[F4]を3回押すと列番号の前だけに$マークがつきます。

対して、見出し"登録時期"を検索値としているJ2セルの参照は、右へコピーする分にはスライドして良いですが、下へコピーするときはズレて欲しくありません。

そのため、行を固定する複合参照にします。
[F4]を2回押すと行番号の前だけに$マークがつきます。

最後に、検索値が見当たらなかった場合のエラー対策として、IFERROR関数を追加しておきます。

見つからない場合は、ダブルコーテーションマーク２つ””で何も囲まず空白を返すか、「該当なし」といった任意の文字列を設定します。

この内容で、他の範囲にコピーします。
それぞれ検索した情報を正しく取り出せました。

INDEX＆MATCHの価値

今回のサンプルにおいて管理番号を検索値に「登録時期」情報や「単価」情報を取り出すのであれば、VLOOKUP関数でもできます。

さらに、ここまで2つの関数を用いて、指定の行と列がいわゆる交差する場所のデータを抽出してきましたが、INDEX関数とMATCH関数を組み合わせる用途として、実はこのような使い方はあまりしません。

これは、INDEX関数の第３引数「列番号」が任意設定となっていることがなによりの証拠です。

INDEX関数の第１引数「配列」に、複数列にまたがった範囲ではなく単一列のみ参照しているのであれば、あとはその範囲から何行目のデータを取り出すか、残る第２引数「行番号」だけ指示すれば必要な情報が取得できるのです。

検索値の左側のデータを取得する

ここからは商品名を検索値として、管理番号を取得する様式で演習していきます。

INDEX関数を挿入します。
第１引数「配列」には管理番号の列を絶対参照で参照します。

第２引数「行番号」にMATCH関数をネストします。
MATCH関数の第１引数「検査値」には、商品名の位置を調べるため、I3セルを列固定の複合参照で指示します。

MATCH関数の第２引数「検査範囲」には、商品名の範囲を絶対参照で参照します。

MATCH関数の第３引数「照合の種類」は完全一致の「0」とします。
エラー対策として、IFERROR関数でネストしておきます。

この数式を下へコピーすると、各商品名に対応する管理番号が取得できます。

VLOOKUP関数の場合、検索値が検索範囲の中で一番左になければならず、検索値よりも右側の情報しか取り出せませんでした。
対して、INDEX関数とMATCH関数を組み合わせれば、検索値の左側のデータも取得できるのです。

行列の挿入・削除といった表の整形作業が発生しても、データ抽出に支障はありません。

これが、INDEX＆MATCHというセット名称で呼ばれるほど、INDEX関数とMATCH関数がペアで活用される背景です。

セル範囲形式の検索

前述の通り、INDEX関数には配列形式とセル範囲形式の２つの検索方法があります。

使用頻度は極稀ですが、参考までにセル範囲形式のユースケースについても触れておきます。

改めて引数を確認すると、配列形式との大きな違いは、第4引数の「領域番号」です。

セル範囲形式の場合、INDEX関数の第１引数「参照」の部分には複数の範囲を指定できます。
そのうち、どの範囲からデータを抽出するか、検索対象範囲の切り替えを、第４引数「領域番号」で指示するのです。

A支店とB支店という2つの検索対象範囲が存在する表をサンプルに見ていきます。

参照の部分に複数の範囲を入力するには、もう一つカッコをつけます。
A支店の範囲を参照し、カンマで区切ったら続けてB支店の範囲を参照します。

この要領で検索対象となり得る範囲をすべて選択したら、INDEX関数の第１引数「参照」のカッコを閉じます。
この数式を他の範囲にコピーする必要があれば、しかるべき参照形式に変換しておきましょう。（今回は省略します）

続く第２引数「行番号」、第３引数「列番号」ともに、実際はMATCH関数を入れ込みますが、今回は固定値で簡略化します。
※今回はあくまでセル範囲形式の動作をご理解いただければOKです。

ここでは"6"行目（6月）の、"3"列目（商品3）の数量を取り出してみます。

一旦、第４引数「領域番号」に何も入力せずに結果を見てみます。
すると、左側の範囲において6行目・3列目にあるデータ「53」が取り出されました。

続いて、領域番号に「1」を入れてみます。

また、同じデータが返されます。

次に、領域番号を「2」に設定します。

すると今度は、右側の範囲における6行目・3列目にあるデータ「651」が抽出されます。

セル範囲形式はこのように、領域番号に何も入力しない、もしくは「1」とすることで、一番左または一番上に位置する範囲を検索対象範囲にし、「2」以降の数値を指示すると、その値に応じて対象範囲をスライドしてくれます。

ユースケースとしては、
＊天候や広告効果の大小に左右される季節商品の販売量を、パターンA・パターンBのように予測する。
＊営業ブロックごとの拠点別成約件数実績をＡブロック単位・Ｂブロック単位のように分ける。
＊部品の調達量に応じた生産可能数の幅をMAXケース、MINケースのように見積もる。
といったようにいくつかの選択肢を設定し、状況に応じてそれらを切り替えながらデータを取得したいシーンで、このセル範囲形式の概念が役立ちます。

この機会におさえておきましょう。

まとめ

今回は、INDEX関数・MATCH関数、それぞれの役割と、これらを組み合わせたときに実現できる検索方法をご紹介しました。

前回のVLOOKUP関数講座で触れた、空白セル対策や、検索範囲の自動拡張、COUNTIF関数による重複データのナンバリングなど、検索関数に欠かせない関連知識はこのINDEX＆MATCHにも応用できますので、復習がてら見直していただき、様々なシーンでご活用ください。

また本日の講座で、左右いかなる位置関係でもデータ抽出が可能であることをご理解いただけましたが、VLOOKUP関数と同じく、
・エラー対策にIFERROR関数へのネストが求められる。
・複数の条件を検索値に指定できない。
といったように、INDEX&MATCHのペアでも解決できない課題があります。

次回はこうした弱点を解消してくれるXLOOKUP関数を演習しますので、ぜひあわせてご一読くださいね！

↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。

↓↓Excel操作をとにかく高速化したい方へ↓↓