エクセルINDEX関数とMATCH関数で、別シートまたは別ファイルにあるデータから値を検索して抽出する方法

エクセルINDEX関数とMATCH関数を組み合わせて、データが別シートまたは別ファイルにある場合の、値の抽出方法を説明しています。エクセルINDEX関数とMATCH関数を組み合わせて値を抽出する場合、同じシート、または同じファイルで使うことは良く知られていますが、検索、抽出するデータが別シートまたは別ファイルにある時はどうでしょうか?データ量が多いと、別シートまたは別ファイルから、データを参照することもよくあります。このような時、役に立つ方法です。仕事アップできます。

 

 

INDEXとMATCHを組み合わせて、同じシートにあるデータから値を抽出

INDEX関数とMATCH関数を組み合わせて、抽出する基本を復習しましょう。

F2に、商品№を検索し、その商品№の担当者名を抽出する式を入力します。

 

F2 =INDEX($B$2:$B$6,MATCH(D2,$C$2:$C$6,0),1)

INDEX関数の配列は「$B$2:$B$6」と絶対値にします。

「マツイ」が返されます。

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

担当者名が表示されました!

 

INDEX関数とMATCH関数を組み合わせて、別シートから値を抽出

シート「本社」に、シート「茨城」の合計金額を表示させます。

茨城シート

 

本社シート

E2に、本社シートの品番を、茨城シートの品番から検索し、「茨城の合計金額」を抽出する式を入力しましょう。

 

E2 =INDEX(茨城!$D$2:$D$6,MATCH(A2,茨城!$A$2:$A$6,0),1)

INDEX関数の配列は茨城シートにあるので、「茨城!$D$2:$D$6」です。範囲は絶対値にします。

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

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

「茨城」の合計金額が、表示されました!

INDEXとMATCHを組み合わせて、別ファイルから値を抽出

ファイル名「本社」、シート名「本社」に、ファイル名「埼玉」、シート名「埼玉」の担当社員番号を表示させます。

埼玉ファイル、埼玉シート

 

本社ファイル、本社シート

D2に、本社ファイル、本社シートの商品コードを、埼玉ファイル、埼玉シートの商品コードから検索し、埼玉の担当社員番号を抽出する式を入力しましょう。

 

D2 =INDEX([埼玉支社.xls]埼玉支社!$B$2:$B$6,MATCH(A2,[埼玉支社.xls]埼玉支社!$A$2:$A$6,0),1)

範囲は「[埼玉支社.xls]埼玉支社!$B$2:$B$6」と、絶対値にします。

「MN10023」が返されます。

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

埼玉支社の担当社員番号が、抽出されました!

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

 

関数の使い方

INDEX関数

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

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

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

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

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

配列:  配列が1行または1列の場合、行番号または列番号を省略できます。

行番号: 行番号を省略した場合は、必ず列番号を指定します。

列番号: 列番号を省略した場合は、必ず行番号を指定します。

INDEX関数、配列形式の使用例

 

F2に、範囲を1列で指定して式を入力しましょう。

F2 =INDEX(C2:C6,2,1) または F2 =INDEX(C2:C6,2)

「熊本」を返します。

F2の式は、範囲「C2:C6」の上から2番目の行、左から1番目の列の値を返しています。

F3に、セルの範囲を指定して式を入力しましょう。

F3 =INDEX(A2:C6,4,3)

「埼玉」を返します。

F3の式は、範囲「A2:C6」の上から4番目の行、左から3番目の列の値を返しています。

こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って検索、抽出する方法

 

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

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

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

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

INDEX関数、セル範囲形式の使用例

 

F2に式を入力しましょう。

F2 =INDEX((A2:C3,A5:C7),2,2,2)

「水野」を返します。

F2の式は、範囲「A2:D3とA5:C7」で、2番目の領域「A5:C7」が選択されるので、2番目の領域「A5:C7」の上から2番目の行、左から2番目の列の値を返します。

 

 

MATCH関数

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

MATCH関数の書式と引数

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

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

照合の型

「1」:検査値以下の最大の値を返します。この場合、検査範囲を昇順の並べ替える必要があります。

「0」:検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。

「-1」:検査値以上の最小の値が検索されます。この場合、検査範囲を降順に並べ替える必要があります。

MATCH関数の使用例

 

I2に式を入力しましょう。

担当者名「カワノ」の、品番2「B206」の位置を求めます。

I2 =MATCH(“B206”,B3:E3,0)

「2」になります。

I2の式は、検索する範囲を「B3:E3」に指定しています。この範囲で「B206」は2番目なので、「2」の値を返します。

INDEXとMATCH以外の関数で、別シートまたは別ファイルから抽出

エクセルINDEX関数とMATCH関数を組み合わせて、別シートまたは別ファイルのデータの値を抽出することができますが、VLOOKUP関数とHLOOKUP関数でもできます。

特に、エクセルVLOOKUP関数は別シート、別ファイルを参照して抽出することがよくあります。このテクニックを覚えると、仕事の幅が広がります。

こちらの記事もご覧ください。⇒エクセルVLOOKUP関数の使い方!別シートを参照する方法