INDEX+MATCHで好きな位置の値を取得する。

Excel

VLOOKUPやHLOOKUPだと検索位置を左や上に固定する必要が有ります。
毎回その仕様に合わせてレイアウトを修正して関数を組んでいる場合は、INDEXとMATCHを組み合わせましょう。

スポンサーリンク

INDEX関数について

関数の使い方

INDEXの使い方は2種類有りますが今回は(配列,行番号,列番号)の使い方です。

INDEX関数の次のような指示を行います。
=INDEX(配列,行番号,列番号)
行番号・列番号は配列の左上を1として番号がついています。
VLOOKUPの列番号と同じイメージです。

画像の表では下記でこの結果になります。
=INDEX(B3:D7,2,1)・・・くぅま
=INDEX(B3:D7,2,2)・・・くぅまの郵便番号
=INDEX(B3:D7,2,3)・・・くぅまの住所

行番号を3にすればくまこの情報が取得できます。

INDEX関数はVLOOKUPの検索と検索条件が無く行も列も番号で指定して使います。

エラーについて

#REF!

#REF!はINDEX関数の配列より大きい行・列番号を指定している時に出ます。
行が縦方向・列が横方向ですので再度確認してみましょう。

#VALUE

#VALUE!は行・列番号が1未満になっている時に出ます。
番号は1~なので0や負の値になってないか確認しましょう。

次はMATCH関数について説明を行います。

MATCH関数について

関数の使い方

MATCH関数は検査値が検査範囲の何番目に有るかを返す関数です。

MATCH関数の次のような指示を行います。
=MATCH(検査値,検査範囲,照合の種類)
検索値はVLOOKUP同様です。
注意点として検査範囲は行もしくは列での指定なのでB3:C7のようにどちらも複数行・列にすると#N/Aエラーが出ます。
照合の種類は 1-以下,0-完全一致,-1-以上 の三種類有ります。
今回は完全一致の0を指定しています。

画像の表では下記でこの結果になります。
=MATCH(“くぅま”,$B$3:$B$7,0)・・・2
=MATCH(“くまこ”,$B$3:$B$7,0)・・・3
=MATCH(“くまみ”,$B$3:$B$7,0)・・・4
実際には文字列で指定では無くセル参照を使います。

列も同様に使うことが出来ます。

画像の表では下記でこの結果になります。
=MATCH(“氏名”,$B$3:$D$3,0)・・・1
=MATCH(“郵便番号”,$B$3:$D$3,0)・・・2
=MATCH(“住所”,$B$3:$D$3,0)・・・3
実際には文字列で指定では無くセル参照を使います。

エラーについて

#N/A

#N/AはMATCH関数使用時に2種類のことをしたときに時に出ます。
検索値が検索範囲内で見つからない場合。
 完全一致の場合は空白でも出るので空白の場合はIF関数を使用して空白にしましょう。

複数の行と列を指定している場合
 使い方でも書きましたが、列・行どちらかのみ複数で指示します。

次にINDEX関数とMATCH関数を組み合わせた説明を行っていきます。

INDEX関数とMATCH関数を組み合わせる。

実際の使い方ですがINDEX関数の行番号・列番号をMATCH関数で算出していきます。

INDEX関数でのくぅまの郵便番号は下記で出ていました。
=INDEX(B3:D7,2,2)・・・くぅまの郵便番号

MATCHと組み合わせた場合は下記のようになります。
=INDEX(B3:D7,MATCH(“くぅま”,B3:B7,0),MATCH(“郵便番号”,B3:D3,0))

※必要に応じてセル参照や$マークでコピーしても参照位置が動かないようにしましょう。

検索する行・列はどこでもよい。

VLOOKUP関数やHLOOKUP関数は検索行・列の位置が固定でしたが
INDEX関数+MATCH関数の場合はどの項目でも検索行・列に出来ます。

画像のように同じ表のままで郵便番号を指定して住所を算出が可能です。

まとめ

最後まで閲覧して頂きありがとうございました!
最後にこの記事のまとめを記載します。

INDEX関数・MATCH関数組み合わせまとめ

  • INDEX関数は範囲内の位置を指定してセルを参照する関数。
  • MATCH関数は検査値が検査範囲の何番目かを返す関数
  • INDEX関数の列番号をMATCHに置き換えるとVLOOKUPと同様に使える。
  • 検索列・行の位置を自由に設定可能

コメント

タイトルとURLをコピーしました