INDEX+MATCH関数で別シートからデータを抽出する方法

ここでは、INDEX関数+MATCH関数で「別シートからマッチングしたデータを抽出する方法」を解説します。

 

条件に一致したデータを、元の表があるシートとは別のシートに抽出する方法で、元の表が変わると抽出したデータも自動的に変わります。

 

ただしこのページで説明するのは、「一つだけ抽出する方法」です。

 

「条件に合うもの全てを抽出する方法」は、次のページで解説しています。

⇒ 条件に合うデータ全てを一覧表から関数で順に抽出する方法/

1.数式の作り方

まずは、INDEX+MATCH関数で「別シートから値を抜き出す」ことのできる数式の作り方から見ていきます。

 

1-1.例に使う表とやることの確認

始めに、例に使う表とやることの確認です。

Sheet1には、氏名や年齢などが表に入力された「社員基本情報一覧」が、Sheet2には一覧から№で特定のデータを抜き出した結果を表示させるための表があるとします。

 

そして、Sheet1から値を抽出するためのINDEX+MATCH関数の数式を、Sheet2のセルB2に入力してみます。

 

1-2.数式の形

使う数式の形は、次の通りです。

=INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

引数は「配列」、「検査値」、「検査範囲」、「照合の種類」、「列番号」の5つです。

 

そして、そのうち赤字になっている「配列」と「検査範囲」に「別シートの範囲」を指定することで、別シート参照になります。

 

1-3.数式を作ってみる

では、さっそく作ってみます。

 

以下手順です。

 

(1)sheet2のセルB2に「=INDEX(」と入力し、fxボタンを押す

 

(2)引数の選択で「配列、行番号、列番号」をクリックする

 

(3)関数の引数ダイアログで「配列」クリック後、Sheet1をクリックする

 

4)セルA4~E8を選択する

 

(5)関数の引数ダイアログの「行番号」をクリックし、「MATCH(」と入力後、数式バー上の「MATCH」をクリックする

 

(6)関数の引数ダイアログの「検査値」に「A2」と入力した後、「照合の種類」に「FALSE」と入力する

 

(7)関数の引数ダイアログの「検査範囲」クリック後、Sheet1をクリックする

 

(8)セルA4~A8を選択する

 

(9)数式バー上の「INDEX」をクリックする

 

(10)「列番号」に「2」と入力後、OKボタンを押せば完了

 

これでSheet2の氏名欄に結果が表示されるようになりました。

 

後はこの数式を右のセルにコピーし、都度INDEX関数の引数「列番号」を、3、4…など適切な数値に直せばOKです。

2.別シート参照時のポイント

では、次に数式作成時のポイントを見てみましょう。

 

ちなみに、数式入力後の出来上がりは、次の通りです。

手順中にもありますが、INDEX+MATCH関数で別シートを参照する時のポイントは「参照したいシートにクリックして切り替え、対象セルをクリック(範囲はドラッグ)する」ことです。

 

シート参照を手入力で表現するには、「シート名!」と入力する必要があります。

 

これは手間がかかり、入力間違いも起こりやすいです。

 

対して、別シートを参照させたい引数を入力する際に「クリックでそのシートを表示させ、対象セルをクリック、範囲はドラッグ」すれば、数式に「シート名!」と自動で入るので、とても簡単です。

3.元が変わると抽出データも自動反映される

INDEX+MATCH関数は、シートが別であっても元の表が変わると「抽出したデータも自動的に変わり」ます。

 

ですから、INDEX+MATCH関数で別シートからマッチングしたデータを抽出した場合には、「常に最新の元の表のデータをもとに抽出している」と考えていいのです。

 

万が一自動的に反映されない場合には、「数式」タブにある「計算方法の設定」が「自動」になっているか確認してください。

 

4.必要な列だけ抽出するには

最後に、「必要な列だけ抽出する」方法です。

 

必要な列だけ抽出したい時、いじるのは緑色の「列番号」です。

=INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

列番号をいじる時のポイントは、「『配列』で指定した範囲のうち『何列目』を表示させるか?を数値で入れる」ことです。

 

例えば、配列が「セルB1からD10」だった場合、列番号が「1」だとB列、「2」だとC列、「3」だとD列の値を抽出できます。

 

このように、「配列の中での範囲」を意識して列番号を数値で指定しましょう。

 

以上、参考になれば幸いです。

 


口コミ・評判のいいExcel本の紹介】


Top