複数の条件で Excel ルックアップ式を作成する方法

知っておくべきこと

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

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

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

このチュートリアルの手順に従うには、下の画像に示すように、次のセルにサンプル データを入力します。行 3 と 4 は、このチュートリアルで作成した配列数式に対応するために空白のままにします。(このチュートリアルには、画像に表示されている書式設定は含まれていないことに注意してください。)

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

Excel で INDEX 関数を作成する

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

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

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

1. セルF3を選択してアクティブ セルにします。このセルは、ネストされた関数が入力される場所です。
2. 数式に移動します。

   

3. [検索と参照]を選択して、関数のドロップダウン リストを開きます。
4. INDEXを選択して、 [引数の選択] ダイアログ ボックスを開きます。
5. array,row_num,column_num を選択します。
6. [ OK ] を選択して [関数の引数] ダイアログ ボックスを開きます。Excel for Mac では、数式ビルダーが開きます。
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 タイプの引数を追加する

MATCH 関数の 3 番目で最後の引数は、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. Row_numテキスト ボックスのカンマの後に0 (ゼロ) を入力します。この数値により、ネストされた関数は、セル D3 および E3 に入力された用語と完全に一致するものを返します。
2. ) (閉じ丸括弧) を入力して、MATCH 関数を完成させます。

   

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

INDEX 関数を終了する

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

1. Column_numテキスト ボックスにカーソルを置きます。
2. 3 (数字の 3) を入力します。この数値は、D6 から F11 までの範囲の 3 列目のデータを検索するように数式に指示します。

   

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

配列数式を作成する

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

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

1. [ OK ] を選択して、ダイアログ ボックスを閉じます。Excel for Mac で、[完了]を選択します。
2. セルF3を選択して数式を表示し、数式バーの数式の末尾にカーソルを置きます。
3. 数式を配列に変換するには、CTRL + SHIFT + ENTERを押します。
4. #N/A エラーがセル F3 に表示されます。これは、関数が入力されたセルです。
5. セル D3 と E3 が空白であるため、セル F3 に #N/A エラーが表示されます。D3 と E3 は、関数が Lookup_value を見つけるために参照するセルです。これら 2 つのセルにデータが追加されると、エラーはデータベースからの情報に置き換えられます。

   

検索条件を追加する

最後のステップは、検索語をワークシートに追加することです。このステップは、列 1 の用語 Widgets と列 2 の Titanium を照合します。

数式がデータベースの適切な列で両方の用語の一致を検出すると、3 番目の列から値が返されます。

1. セルD3を選択します。
2. ウィジェットに入ります。
3. セルE3を選択します。
4. Titaniumと入力し、 Enterを押します。
5. 仕入先の名前 Widgets Inc. がセル F3 に表示されます。これは、リストされている唯一のチタン ウィジェットを販売するサプライヤーです。
6. セルF3を選択します。ワークシートの上の数式バーに関数が表示されます。この例では、titanium ウィジェットのサプライヤは 1 つだけです。複数のサプライヤが存在する場合は、データベースで最初にリストされているサプライヤが関数によって返されます。
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}