複数の基準を持つExcelルックアップ式を作成する方法

知っておくべきこと

• 最初にINDEX関数を作成し、Lookup_value引数を入力してネストされたMATCH関数を開始します。
• 次に、Lookup_array引数に続けてMatch_type を追加します。 引数を指定してから、列の範囲を指定します。
• 次に、 Ctrl を押して、入れ子関数を配列数式に変換します +シフト +入力 。最後に、検索語をワークシートに追加します。

この記事では、Excelで複数の条件を使用して、配列数式を使用してデータベースまたはデータテーブル内の情報を検索するルックアップ数式を作成する方法について説明します。配列数式には、INDEX関数内にMATCH関数をネストすることが含まれます。情報には、Excel for Microsoft 365、Excel 2019、Excel 2016、Excel 2013、Excel 2010、およびExcelforMacが含まれます。

チュートリアルに従ってください

このチュートリアルの手順を実行するには、次の画像に示すように、次のセルにサンプルデータを入力します。行3と4は、このチュートリアルで作成された配列数式に対応するために空白のままになっています。 （このチュートリアルには、画像に表示されているフォーマットは含まれていません。）

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

ExcelでINDEX関数を作成する

INDEX関数は、複数の形式を持つExcelの数少ない関数の1つです。この関数には、配列フォームと参照フォームがあります。配列形式は、データベースまたはデータのテーブルからデータを返します。参照フォームは、セル参照またはテーブル内のデータの場所を示します。

このチュートリアルでは、データベース内のこのサプライヤへのセル参照ではなく、配列フォームを使用してチタンウィジェットのサプライヤの名前を検索します。

次の手順に従って、INDEX関数を作成します。

1. セルF3を選択します それをアクティブセルにします。このセルは、入れ子関数が入力される場所です。

2. 数式に移動します 。

   

3. ルックアップとリファレンスを選択します 機能のドロップダウンリストを開きます。

4. INDEXを選択します 引数の選択を開くには ダイアログボックス。

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

6. OKを選択します 関数の引数を開くには ダイアログボックス。 Excel for Macで、FormulaBuilderが開きます。

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

8. セルを強調表示するD6 F11まで ワークシートで範囲をダイアログボックスに入力します。

   [関数の引数]ダイアログボックスを開いたままにします。数式は終了していません。以下の手順で数式を完成させます。

   

ネストされたMATCH関数を開始します

ある関数を別の関数の中にネストする場合、2番目の、またはネストされた関数の数式ビルダーを開いて、必要な引数を入力することはできません。入れ子関数は、最初の関数の引数の1つとして入力する必要があります。

関数を手動で入力する場合、関数の引数はコンマで区切られます。

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

Lookup_valueは、1つの検索条件または用語のみを受け入れます。複数の条件を検索するには、アンパサンド記号（＆）を使用して2つ以上のセル参照を連結または結合することにより、Lookup_valueを拡張します。

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

2. MATCH（ 。

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

4. ＆と入力します （アンパサンド）セル参照の後 D3 2番目のセル参照を追加します。

5. セルE3を選択します 2番目のセル参照を入力します。

6. 、と入力します （コンマ）セル参照E3の後に、MATCH関数のLookup_value引数の入力を完了します。

   

   チュートリアルの最後のステップでは、Lookup_valuesがワークシートのセルD3とE3に入力されます。

ネストされたMATCH関数を完了します

この手順では、ネストされたMATCH関数のLookup_array引数を追加します。 Lookup_arrayは、チュートリアルの前のステップで追加されたLookup_value引数を見つけるためにMATCH関数が検索するセルの範囲です。

Lookup_array引数で2つの検索フィールドが識別されたため、Lookup_arrayについても同じことを行う必要があります。 MATCH関数は、指定された用語ごとに1つの配列のみを検索します。複数の配列を入力するには、アンパサンドを使用して配列を連結します。

1. Row_numのデータの最後にカーソルを置きます テキストボックス。カーソルは、現在のエントリの最後のコンマの後に表示されます。

2. セルを強調表示するD6 D11まで ワークシートに範囲を入力します。この範囲は、関数が検索する最初の配列です。

3. ＆と入力します （アンパサンド）セルが D6：D11を参照した後 。この記号により、関数は2つの配列を検索します。

4. セルを強調表示するE6 E11を介して ワークシートに範囲を入力します。この範囲は、関数が検索する2番目の配列です。

5. 、と入力します （コンマ）セル参照の後 E3 MATCH関数のLookup_array引数の入力を完了します。

   

6. チュートリアルの次のステップのために、ダイアログボックスを開いたままにします。

MATCHタイプ引数を追加します

の3番目で最後の引数 MATCH関数はMatch_typeです。 口論。この引数は、Lookup_valueをLookup_arrayの値と一致させる方法をExcelに指示します。使用可能な選択肢は、1、0、または-1です。

この引数はオプションです。省略した場合、関数はデフォルト値の1を使用します。

• Match_type =1または省略された場合、MATCHはLookup_value以下の最大値を検出します。 Lookup_arrayデータは昇順で並べ替える必要があります。
• Match_type =0の場合、MATCHはLookup_valueと等しい最初の値を見つけます。 Lookup_arrayデータは任意の順序で並べ替えることができます。
• Match_type =-1の場合、MATCHはLookup_value以上の最小値を検出します。 Lookup_arrayデータは降順で並べ替える必要があります。

INDEX関数のRow_num行の前の手順で入力したコンマの後に次の手順を入力します。

1. 0と入力します （ゼロ） Row_numのコンマの後 テキストボックス。この番号により、入れ子関数はセルD3およびE3に入力された用語と完全に一致するものを返します。

2. ）と入力します （閉じ角かっこ）MATCH機能を完了します。

   

3. チュートリアルの次のステップのために、ダイアログボックスを開いたままにします。

INDEX関数を終了します

MATCH関数が実行されます。ダイアログボックスのColumn_numテキストボックスに移動し、INDEX関数の最後の引数を入力します。この引数は、列番号がD6からF11の範囲にあることをExcelに通知します。この範囲は、関数によって返される情報を見つける場所です。この場合、チタンウィジェットのサプライヤーです。

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

2. 3と入力します （3番目）。この数値は、D6からF11の範囲の3番目の列でデータを検索するように数式に指示します。

   

3. チュートリアルの次のステップのために、ダイアログボックスを開いたままにします。

配列数式を作成する

ダイアログボックスを閉じる前に、ネストされた関数を配列数式に変換します。この配列により、関数はデータのテーブルで複数の用語を検索できます。このチュートリアルでは、2つの用語が一致しています。列1のウィジェットと列2のチタンです。

Excelで配列数式を作成するには、 CTRLを押します。 、シフト 、および ENTER 同時にキー。一度押すと、関数は中括弧で囲まれ、関数が配列になったことを示します。

1. OKを選択します ダイアログボックスを閉じます。 Excel for Macで、[完了]を選択します 。

2. セルF3を選択します 数式を表示するには、数式バーの数式の最後にカーソルを置きます。

3. 数式を配列に変換するには、 CTRLを押します。 +シフト +入力 。

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

5. セルD3とE3が空白であるため、セルF3に＃N/Aエラーが表示されます。 D3とE3は、関数がLookup_valueを見つけるために検索するセルです。これらの2つのセルにデータが追加されると、エラーはデータベースからの情報に置き換えられます。

   

検索基準を追加する

最後のステップは、ワークシートに検索語を追加することです。この手順は、列1およびのウィジェットという用語と一致します。 列2のチタン。

数式がデータベースの適切な列で両方の用語に一致するものを見つけると、3番目の列から値を返します。

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

2. ウィジェットと入力します 。

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

4. チタンと入力します 、 Enterを押します 。

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

6. セルF3を選択します 。関数はワークシートの上の数式バーに表示されます。

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

   この例では、チタンウィジェットのサプライヤは1つだけです。複数のサプライヤがあった場合、データベースの最初にリストされているサプライヤが関数によって返されます。