Excelで複数の条件を指定したルックアップ式を作成する方法｜INDEX・MATCH関数の組み合わせ

ポイント

• まずINDEX関数を作成し、続いてネスト（入れ子）するMATCH関数のLookup_value引数の入力から始めます。
• 次にLookup_array引数とMatch_type引数を追加し、列範囲を指定します。
• その後、Ctrl＋Shift＋Enterキーを同時に押して、ネストした関数を配列数式へ変換します。最後にワークシートへ検索語を入力すれば完成です。

この記事では、Excelで配列数式を活用し、データベースやデータテーブルから複数の条件で情報を検索するルックアップ式の作り方を解説します。具体的には、MATCH関数をINDEX関数の中にネストさせる配列数式を使用します。対応バージョンは、Microsoft 365版Excel、Excel 2019／2016／2013／2010、およびMac版Excelです。

チュートリアルを進めるための準備

このチュートリアルの手順に沿って進めるには、下の画像を参考にサンプルデータを各セルに入力してください。3行目と4行目は、このチュートリアルで作成する配列数式のために空白のまま残しておきます（画像に見られる書式設定までは再現しません）。

• 上側のデータ範囲をセルD1～F2に入力します。
• 2番目のデータ範囲をセルD5～F11に入力します。

ExcelでINDEX関数を作成する

INDEX関数は、複数の形式を持つ数少ないExcel関数の一つです。「配列形式」と「参照形式」の2種類があり、配列形式はデータベースやテーブルからデータそのものを返し、参照形式はデータが存在するセル参照（位置）を返します。

このチュートリアルでは、データベース内のセル参照ではなく、「チタン製ウィジェット」のサプライヤー名を取得するために配列形式を使用します。

以下の手順でINDEX関数を作成しましょう。

1. セルF3を選択してアクティブセルにします。ここにネストした関数を入力します。

2. リボンの「数式」タブをクリックします。

   

3. 「検索/行列」を選択し、関数のドロップダウンリストを開きます。

4. 「INDEX」を選択して、「引数の選択」ダイアログボックスを開きます。

5. array, row_num, column_num を選択します。

6. 「OK」をクリックすると「関数の引数」ダイアログボックスが開きます（Mac版Excelでは「数式ビルダー」が開きます）。

7. カーソルをArrayテキストボックスに置きます。

8. ワークシート上でセルD6～F11をドラッグして選択し、範囲をダイアログボックスに入力します。
   ※「関数の引数」ダイアログボックスは開いたままにしておいてください。数式はまだ途中であり、以降の手順で完成させます。

   

ネストしたMATCH関数の入力を開始する

関数を別の関数の中にネストする場合、内側（ネスト先）の関数用のダイアログボックスを開いて引数を入力することはできません。ネストした関数は、外側の関数の引数として直接入力する必要があります。

※関数を手入力する場合、引数と引数の間はカンマ（,）で区切ります。

MATCH関数入力の最初のステップは、Lookup_value引数の入力です。Lookup_valueとは、データベース内で照合したい検索語が入力されている場所（セル参照）のことです。

Lookup_valueは通常1つの検索条件しか受け付けません。そこで複数の条件を扱うには、アンパサンド記号（&）を使って2つ以上のセル参照を連結（結合）します。

1. 「関数の引数」ダイアログボックスで、カーソルをRow_numテキストボックスに置きます。

2. MATCH( と入力します。

3. セルD3をクリックして、そのセル参照をダイアログボックスに入力します。

4. セル参照D3の後に &（アンパサンド）を入力し、2つ目のセル参照を連結できるようにします。

5. セルE3をクリックして、2つ目のセル参照を入力します。

6. セル参照E3の後に ,（カンマ）を入力して、MATCH関数のLookup_value引数を確定します。

   

   ※チュートリアルの最終ステップで、実際の検索語をワークシートのセルD3とE3に入力します。

ネストしたMATCH関数を完成させる

このステップでは、ネストしたMATCH関数のLookup_array引数を追加します。Lookup_arrayとは、前のステップで指定したLookup_valueを探すためにMATCH関数が検索するセル範囲のことです。

Lookup_value側で2つの検索フィールドを指定したので、Lookup_array側も同じ構成にする必要があります。MATCH関数は1つの項目につき1つの配列しか検索できないため、複数の配列を扱うにはアンパサンド（&）で配列どうしを連結します。

1. カーソルをRow_numテキストボックス内のデータ末尾（現在の入力の最後のカンマの直後）に置きます。

2. ワークシート上でセルD6～D11を選択して範囲を入力します。これが関数が検索する1つ目の配列です。

3. セル参照D6:D11の後に &（アンパサンド）を入力します。この記号によって、関数は2つの配列を検索できるようになります。

4. ワークシート上でセルE6～E11を選択して範囲を入力します。これが関数が検索する2つ目の配列です。

5. セル参照E6:E11の後に ,（カンマ）を入力して、MATCH関数のLookup_array引数を確定します。

   

6. ダイアログボックスは、次のステップのために開いたままにしておきます。

Match_type引数を追加する

MATCH関数の3つ目（最後）の引数がMatch_typeです。この引数は、Lookup_valueとLookup_array内の値をどのような方法で照合するかをExcelに指示します。指定できる値は「1」「0」「-1」の3種類です。

※この引数は省略可能です。省略した場合は既定値の「1」が適用されます。

• Match_type = 1（または省略）：Lookup_value以下の最大値を検索します。Lookup_arrayのデータは昇順に並べ替えておく必要があります。
• Match_type = 0：Lookup_valueと完全に一致する最初の値を検索します。Lookup_arrayの並び順は問われません。
• Match_type = -1：Lookup_value以上の最小値を検索します。Lookup_arrayのデータは降順に並べ替えておく必要があります。

INDEX関数のRow_num欄で前のステップで入力したカンマの続きとして、以下の操作を行います。

1. Row_numテキストボックスのカンマの後に 0（ゼロ）を入力します。これにより、ネストした関数はセルD3とE3に入力された語句と完全一致する結果を返すようになります。

2. )（閉じ括弧）を入力して、MATCH関数を閉じます。

   

3. ダイアログボックスは、次のステップのために開いたままにしておきます。

INDEX関数を仕上げる

これでMATCH関数は完成です。続いて、ダイアログボックスのColumn_numテキストボックスに移動し、INDEX関数の最後の引数を入力します。この引数は、範囲D6～F11の中の何列目のデータを返すかをExcelに指示するもので、関数の戻り値はこの範囲から取得されます。今回の場合は「チタン製ウィジェットのサプライヤー名」です。

1. カーソルをColumn_numテキストボックスに置きます。

2. 3（数字の3）を入力します。これにより、数式は範囲D6～F11の3列目にあるデータを返します。

   

3. ダイアログボックスは、次のステップのために開いたままにしておきます。

配列数式を作成する

ダイアログボックスを閉じる前に、ネストした関数を配列数式へ変換します。配列数式にすることで、関数はデータテーブル内の複数の項目を同時に照合できるようになります。このチュートリアルでは、「1列目のWidgets（ウィジェット）」と「2列目のTitanium（チタン）」という2つの条件を照合します。

Excelで配列数式を作成するには、Ctrl＋Shift＋Enterキーを同時に押します。押すと数式が波括弧 { } で囲まれ、配列数式になったことが示されます。

1. 「OK」をクリックしてダイアログボックスを閉じます（Mac版Excelでは「完了」をクリック）。

2. セルF3を選択して数式を確認し、数式バー内の数式の末尾にカーソルを置きます。

3. Ctrl＋Shift＋Enterを押して、数式を配列数式に変換します。

4. セルF3に #N/A エラーが表示されます。これは関数を入力したセルです。

5. セルF3に#N/Aエラーが表示されるのは、Lookup_valueを参照するセルD3とE3がまだ空だからです。この2つのセルにデータを入力すると、エラーはデータベースから取得した情報に置き換わります。

   

検索条件を入力する

最後のステップは、ワークシートに検索語を入力することです。これにより、「1列目のWidgets」と「2列目のTitanium」が照合されます。

数式がデータベースの該当する列で両方の条件に一致する行を見つけると、3列目の値を返します。

1. セルD3を選択します。

2. Widgets と入力します。

3. セルE3を選択します。

4. Titanium と入力し、Enterを押します。

5. サプライヤー名「Widgets Inc.」がセルF3に表示されます。これは、チタン製ウィジェットを販売しているリスト中唯一のサプライヤーです。

6. セルF3を選択すると、ワークシート上部の数式バーに関数が表示されます。

   {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}

   ※この例ではチタン製ウィジェットのサプライヤーは1社だけですが、複数のサプライヤーが存在する場合は、データベースで先頭に登録されているサプライヤーが返されます。