エクセル関数で複数の項目で検索、抽出する

エクセルINDEX関数とMATCH関数を組み合わせて、複数の項目で値の検索、抽出する方法を紹介しています。検索値に重複がある場合、1つの条件では検索はできません。2つ以上の項目を結合して、検索します。まず、下準備として、&(アンパサンド)で複数の項目を結合します。そうすると、INDEX関数とMATCH関数を組み合わせて、検索、抽出ができるようになります。INDEX関数とMATCH関数を組み合わせて抽出できない例から、下準備、抽出方法まで、分かりやすいように、順を追って説明しています。

 

 

INDEX関数とMATCH関数を組み合わせて値を抽出

INDEX関数とMATCH関数を組み合わせると、VLOOKUPと同じように値を抽出することができます。

INDEX関数とMATCH関数の組み合わせの、基本的な使い方をおさらいしてみましょう。

A列の「社員番号」を検査値として、B列「氏名」に、右表のG列「氏名」を表示してみます。

 

B2に、式を入力しましょう。

B2 =INDEX($G$2:$G$9,MATCH(A2,$E$2:$E$9,0),1)

B2の式は「MATCH(A2,$E$2:$E$9,0))」で、行番号を取得しています。

「佐藤 義人」が返されます。

B2の式をドラッグして、下にコピーしましょう。

「氏名」が表示されました。

B2の式は配列が1列なので、列番号を省略できます。次の式でも「氏名」を求められます。

B2 =INDEX($G$2:$G$9,MATCH(A2,$E$2:$E$9,0))

 

 

INDEX関数とMATCH関数を組み合わせて、複数条件で抽出

検索値に重複がある場合、1つの条件では検索はできません。複数の条件で検索して、抽出しなければいけません。

D列の「合計金額」に、右表の「合計金額」を表示させてみましょう。

その場合、A列の「商品番号」で検索します。

ですが、「商品番号」は重複しているので、「商品番号」だけでは検索できません。

そこで、「商品番号」と「枝番」を、「&」(アンパサンド)で結合します。

H列に、「商品番号」と「枝番」を結合する式を入力してみましょう。

 

H2に式を入力します。

H2 =F2&G2

H2をドラッグして、下にコピーしましょう。

H列が完成しました。

次に、D2に式を入力しましょう。

 

D2 =INDEX($K$2:$K$9,MATCH(A2&B2,$H$2:$H$9,0))

「3,000」が返されます。

D2の式は、「A2&B2」で「商品番号」と「枝番」をつないでいます。MATCH関数の検査値は「A2&B2」です。範囲「H2:H9」から検査値を探しています。

D2をドラッグして、下にコピーしましょう。

D列に、「合計金額」が表示できました。

 

 

関数の説明

INDEX関数

エクセルINDEX関数は、行番号と列番号が交差する位置にあるセルの値を返す関数です。エクセルINDEX関数には、配列形式とセル範囲形式の2つの使い方があります。

配列形式は、指定した範囲からある値を返します。セル範囲形式は指定した範囲からセルの参照を返します。 

INDEX関数、配列形式の書式と引数

INDEX(配列, 行番号, [列番号])

配列 セルの範囲を指定します。
行番号 値を返す行を数値で指定します。
列番号 値を返す列を数値で指定します。

INDEX関数、配列形式の基本的な使い方

数式 説明
 C8 =INDEX(B2:D5,2,3) 範囲「B2:D5」の1番上の行から2番目、左端から3列目の値が返されます。「D3」の値が返されます。

INDEX関数、セル範囲形式の書式と引数

INDEX(参照, 行番号, [列番号], [領域番号])

参照 1つまたは複数のセルの参照を指定します。
行番号 セル参照を返す行位置を数値で指定します。
列番号 セル参照を返す列位置を数値で指定します。
領域番号 任意です。行番号と列番号が交差する位置を返す参照の範囲を1つ選択します。最初の範囲は「1」、次は「2」というように番号で領域を指定します。

 

解説

参照:  複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、1つの範囲と範囲の間をを半角のコンマ(,)で区切ります。各領域が1行または1列の場合、行番号または列番号はそれぞれ省略できます。

INDEX関数、セル範囲形式の基本的な使い方

数式 説明
C10 =INDEX((B3:D4,B7:D8),1,2,2) 領域番号は2です。範囲の2番目「B7:D8」から値を返します。「B7:D8」の1番上の行から1番目、左端から2列目の値が返されます。「C7」の値が返されます。

 

 

MATCH関数

指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。

MATCH関数の書式と引数

MATCH(検査値, 検査範囲, [照合の型])

検査値 検索する値を指定します。検査値には、数値、文字列、または論理値、またはこれらの値に対するセル参照を指定できます。
検査範囲 検索するセルの範囲を指定します。
照合の型 -1、0、1の数値のいずれかを指定します。省略した場合は自動的に1が選択されます。

 

解説

照合の型

「1」:検査値以下の最大の値を返します。

「0」:検査値と一致する一番最初の値を返します。

「-1」:検査値以上の最小の値が検索されます。

MATCH関数の基本的な使い方

数式 説明
 C5 =MATCH(“りんご”,B2:E2,0)  「B2:E2」の範囲から「りんご」と一致している値を探して、その位置を返します。「C2」に「りんご」が入力されています。「C5」には「2」が返されます。

INDEX関数とMATCH関数で複数条件OR(または)、 AND(かつ)で抽出

エクセルINDEX関数とMATCH関数の組み合わせての抽出は、よく使われます。INDEX関数とMATCH関数に他の関数を組み合わせて使うと、1つの条件だけでなく、OR(または)と、AND(かつ)の複数条件で値を検索、抽出することもできます。方法は、1つだけでなく幾つかかあります。INDEX関数とMATCH関数に複数条件が使えると、より複雑な抽出ができます。

こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って複数条件で値を取得する