lisz-works

プログラミングと興味を貴方に

【Excel】右から左への水平検索:INDEX-MATCH

【スポンサーリンク】

Excel ロゴ

Excelで右から左への水平検索を行う方法です。

左から右への水平検索を行う、VLOOKUPは有名ですね。

しかしこの逆のとき

「右の情報を検索して、左のデータが欲しいんだけど……」

なんてときに困ります。これが出来る関数がないですからね。

そんなとき、この方法を行えば、実現可能です!

INDEXとMATCHの2つの関数を使う複合技です。

水平検索とは?

水平検索とは、ある表のある列に対して、検索を行います。
そこで一致したデータの、別の列のデータを取得する。

という動作を行うものです。

例えば、このように「名前」と「ID」を含んだ表のとき、「IDから名前を検索する」なんてときに使えます。

水平検索 結果サンプル

やり方

やり方はコチラです。

水平検索 例

=INDEX(配列,MATCH(検索値,検索範囲,0),列番号)

※MATCHの箇所 = 行番号

このように、INDEXとMATCHの複合技になります。
INDEXとMATCHについて、詳しく知りたい方は、コチラの記事をどうぞ!

www.lisz-works.com

www.lisz-works.com

各関数の解説

では詳細と解説をしていきます。

説明中の式に「$」マーク付きの参照が出てきます。

「なにそれ?」

って方は、コチラをご覧ください。

www.lisz-works.com

それではそれぞれ解説していきます。

MATCH

まずはMATCHの部分です。

INDEX関数の行番号に当たる箇所で使用します。

=INDEX($B$4:$C$7,MATCH(F2,$C$4:$C$7,0),1)

MATCHは、このような構成になっています。

MATCH(検索するデータ,検索する範囲,0固定でOK)

今回の例の場合、検索する範囲をIDの部分(緑枠)に設定しています。

MATCH 選択範囲

なのでこのIDの値から、「検索するデータと一致するものがあるか?」を調べてくれます。

例えば「0712」を調べているので、結果は「2」となります。

ちなみに3つ目を「0固定でOK」と書きましたが、「0」は「完全一致するデータを検索してね」という意味になります。

INDEX

INDEXは、このような構成になっています。

=INDEX(検索する範囲,行番号,列番号)

今回の例の場合、検索する範囲をデータ全体(赤枠)に設定しています。

INDEX 選択範囲

この範囲の中から、指定した行番号・列番号の位置の値を返します。

例えば、行番号を3、列番号を2と設定すると、結果は「女」(斉藤さんの性別)となります。

組合せ

さて、ここでINDEXとMATCHの組合せです。

はじめの例にあったコレ

=INDEX($B$4:$C$7,MATCH(F2,$C$4:$C$7,0),1)

ですね。

これがどうなるかを順番に解体して見てみましょう。

まずMATCHが動きます。検索データが「0216」となります。

=INDEX($B$4:$C$7,MATCH(0216,$C$4:$C$7,0),1)

この検索値で検索をすると、結果が「4」。なのでこんな感じに。

=INDEX($B$4:$C$7,4,1)

あとはINDEXの動作そのままですね。

範囲内から、行番号:4、列番号:1のデータを見ると……

結果は「西村」ですね!

列番号も自動化

今回「名前」の情報を取る例として出したので、列番号が「1」と固定で入力していましたよね。

ですが、これをMATCHに変えることで、自動でデータを取ることも可能です。

水平検索 列も自動化 例

やり方

はじめに書いていたこの式。

=INDEX($B$4:$C$7,MATCH(F2,$C$4:$C$7,0),1)

1番右の「1」の部分をこのように変更します。

MATCH(I$2,$B$2:$F$2,0)

式全体としては、このようにする。
=INDEX($B$3:$F$7,MATCH($H3,$F$3:$F$7,0),MATCH(I$2,$B$2:$F$2,0))

これで、「今調べたいデータ(見出しの名前)」で「検索対象の表の見出しの位置」を検索します。

水平検索 列自動化 解説

さらにこの検索値を「I$2」のように、「行固定」にすることで、この例でいう、I3~J5までコピペで全て検索してくれちゃうんですね。

ただし注意点としては、この2点が挙げられます。

  1. 見出しが別の行(今回でいう行番号2)ではない表を作る場合は、その表を作るときに参照を変えないといけない。
  2. 検索対象の表(左表)と、検索データを使用する表(右表)の見出しを同じものにしなければならない。

でもこの2点を注意していれば、簡単にこのような表を作ることも可能な訳です!

コピペで済むからデータ量が増えても、メンテする手間が結構はぶけます!

あとがき

いかがでしたでしょうか?

データの集計なんかに使えるので、「どうなるのか?」を1度ご自身の目で確認してみると、エクセルの幅が広がるかもしれませんよ!