Excelで右から左への水平検索を行う方法です。
左から右への水平検索を行う、VLOOKUPは有名ですね。
しかしこの逆のとき
「右の情報を検索して、左のデータが欲しいんだけど……」
なんてときに困ります。これが出来る関数がないですからね。
そんなとき、この方法を行えば、実現可能です！
INDEXとMATCHの2つの関数を使う複合技です。
水平検索とは？
水平検索とは、ある表のある列に対して、検索を行います。
そこで一致したデータの、別の列のデータを取得する。
という動作を行うものです。
例えば、このように「名前」と「ID」を含んだ表のとき、「IDから名前を検索する」なんてときに使えます。
やり方
やり方はコチラです。
=INDEX(配列,MATCH(検索値,検索範囲,0),列番号) ※MATCHの箇所 = 行番号
このように、INDEXとMATCHの複合技になります。
INDEXとMATCHについて、詳しく知りたい方は、コチラの記事をどうぞ！
各関数の解説
では詳細と解説をしていきます。
説明中の式に「$」マーク付きの参照が出てきます。
「なにそれ？」
って方は、コチラをご覧ください。
それではそれぞれ解説していきます。
MATCH
まずはMATCHの部分です。
INDEX関数の行番号に当たる箇所で使用します。
=INDEX($B$4:$C$7,MATCH(F2,$C$4:$C$7,0),1)
MATCHは、このような構成になっています。
MATCH(検索するデータ,検索する範囲,0固定でOK)
今回の例の場合、検索する範囲をIDの部分(緑枠)に設定しています。
なのでこのIDの値から、「検索するデータと一致するものがあるか？」を調べてくれます。
例えば「0712」を調べているので、結果は「2」となります。
ちなみに3つ目を「0固定でOK」と書きましたが、「0」は「完全一致するデータを検索してね」という意味になります。
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点が挙げられます。
- 見出しが別の行(今回でいう行番号2)ではない表を作る場合は、その表を作るときに参照を変えないといけない。
- 検索対象の表(左表)と、検索データを使用する表(右表)の見出しを同じものにしなければならない。
でもこの2点を注意していれば、簡単にこのような表を作ることも可能な訳です！
コピペで済むからデータ量が増えても、メンテする手間が結構はぶけます！
あとがき
いかがでしたでしょうか？
データの集計なんかに使えるので、「どうなるのか？」を1度ご自身の目で確認してみると、エクセルの幅が広がるかもしれませんよ！
