MATCH関数+INDEX関数の使い方 社員番号から社員のデータを取り出すには

社員番号から社員のデータを取り出す

MATCH関数+INDEX関数の合わせ技

INDEX関数

MATCH関数+INDEX関数|エクセルの関数技

MATCH関数+INDEX関数の合わせ技

MATCH関数で行番号を、INDEXで交わる列のデータを
MATCH関数を使用すると、表の中から目的のデータを検索します。そして、そのデータが表の何行目または何列目にあるのかを調べることができます。
また、INDEX関数を使用すると、指定したセル範囲の中で○行○列目の位置にあるセルのデータを調べることがでます。
今回は、この2つの関数を組み合わせるとどんなことができるのかを紹介します。MATCH関数、INDEX関数は、Excelの検索と行列関数の仲間です。
(サンプルファイルは、こちらから 関数技33回サンプルデータ

INDEX関数でデータを取り出すには

INDEX関数は、範囲の中で、指定した行位置、列位置にあるセルの内容を返す関数です。
INDEX関数の書式は2通りあります。
配列を使う時は、配列の書式、セル範囲を使う時は、セル範囲用の書式を選択することになります。
=INDEX (範囲,行位置,列位置,[領域番号]) 
INDEX関数
離れた場所にある複数の範囲を指定することもでき、その場合は範囲を順に1、2、3・・・と数え、領域番号に指定します。

今回の例題で操作しようとしていることは、
1.B2セルに社員番号を入力すると
2.別のシートにある表の何行目かを示し
3.その行に対応する、氏名の列を表示
4.その行に対応する、所属を表示
5.その行に対応する、出身を表示
MATCH関数+INDEX関数の組み合わせ
今回、社員の一覧は別シートに用意されています。(同じシートでも可)
 シートには、マスタと名前をつけています。
MATCH関数+INDEX関数の組み合わせ2
(1)セルB2に社員番号を入力します。
(2)すると、B3セルに、マスタから対応する、社員番号は、何行目に当たるかを返すように式を入力します。
MATCH関数+INDEX関数の組み合わせ3

MATCH関数でその位置を知らせる

MATCH関数は
セルの範囲内で指定された項目を検索し、範囲内のその項目の相対的な位置を返します。
MATCH関数
=MATCH(検査値, 検査範囲, 照合の型)
照合の型
1・・・検査値以下の最大値がヒット
0・・・検査値と等しい値がヒット
-1・・・検査値以上の最小値がヒット

MATCH関数で行番号を、INDEXで交わる列のデータを

 =MATCH(B2,マスタ!A1:A10,0)
式から、社員番号に対応する、行番号が 4と表示されます

INDEX,MATCH関数1
(3)B4セルには、その行に対応する、氏名の列を表示させます。
ここでINDEX関数の登場となります。
 INDEX関数は、
 ■範囲の中で、指定した行位置、列位置にあるセルの内容を返す関数です。
 
 範囲は、”マスタ!A1:D10” で表全体です。
=INDEX (範囲,行位置,列位置,[領域番号])
行位置は、B3セルに表示されているので、B3
列位置は、マスタ!A1:D10の表の2列目に氏名が該当しますから、”2”
これで、氏名が取り出せます。
(領域番号は複数の範囲を指定する場合だけ)
INDEX,MATCH関数2
コピーするために絶対参照にします =INDEX(マスタ!$A$1:$D$10,$B$3,2)

(4)B5セル その行に対応する、所属を表示。
  (3)と同様に考えて、対応する3列目を表示させればOKです。
=INDEX(マスタ!$A$1:$D$10,$B$3,3)
(5)B6セル その行に対応する、出身を表示
  (3)と同様に考えて、対応する4列目を表示させればOKです。
=INDEX(マスタ!$A$1:$D$10,$B$3,4)
 
INDEX,MATCH関数3
今日の講義は以上です。お疲れ様でした。