【Microsoft EXCEL】VLOOKUPではできない検索値の左側にある列の値を取得する数式をMATCHとINDEX、INDIRECTで自作

IT

EXCELのVLOOKUPは与えた検索値を指定範囲から探し出して、指定した列番号の位置の値を取得する関数である

例えば以下↓のようなシート

  A B C
1 SEX NAME_ID NAME
2 男 A Aさんです。
3 女 B Bさんです。
4 男 C Cさんです。
5 IDを入力⇒   ※


があったとき、B2:C4までを検索範囲として

  A B C
1 SEX NAME_ID NAME
2 男 A Aさんです。
3 女 B Bさんです。
4 男 C Cさんです。
5 IDを入力⇒   ※


C5セル（※のセル）に以下のVLOOKUP関数を記述

=vlookup(B5,B2:C4,2,false)


と入力すると、
「B5セルに与えた検索値をもとにB2:C4セルまでを検索して引っかかった行の2番目のセルを取得」するという指示になる。
よってB5セルに「A」と入力すれば「Aさんです。」という値が取得できるし
「C」と入力すれば「Cさんです。」という値が取得できる。
（こんな使い方はその辺ググれば出てくるがこの話の前段として…）

ただ、VLOOKUPは与えた検索値をもとにして右方向にしか検索できないので、
指定範囲内における検索値を持った列は一番左側の列に位置している必要がある。
よってこの例でいうと、「A」と与えたら列名「NAME_ID」の列より右側、列名でいうと「NAME」の項目しか取得できない。
つまり、「NAME_ID」より左側にある「SEX」の値を「NAME_ID」の検索値からは取得することが出来ない。

例えば以下のような検索範囲

  A B C
1 SEX NAME_ID NAME
2 男 A Aさんです。
3 女 B Bさんです。
4 男 C Cさんです。
5 IDを入力⇒   ※


において、C5セルに以下のVLOOKUP関数を記述

=vlookup(B5,A2:B4,0,false)


と入力してB５セルに「A」と入力しても、「男」という値は取得できない。
これは「A」で検索して引っかかった行の1つ左側のセルの値を取得しろ（つまり「SEX」列の値を取得しろ）という指示として無理矢理記述しているが
逆方向への検索にVLOOKUPが対応していないので、#N/Aになってしまう。（第三引数が1以上でないと関数が受け付けない）

ここまでが前置き。
要するに単純なVLOOKUPでは右側への検索しかできない。
検索値より左側にある列の値は、事前に右側にコピペしておくか、数式で参照させるなどして、
とにかく検索値より右側にしないとVLOOKUPでは検索できないのである。
これを、検索値より左側にある値をそのままにして、MATCHやINDEX、INDIRECTを使って検索する方法を実現する。

 

 
結論から言うと以下のように実装する。

  A B C
1 SEX NAME_ID NAME
2 男 A Aさんです。
3 女 B Bさんです。
4 男 C Cさんです。
5 IDを入力⇒   ※


↑の青色部分の指定範囲に対して「A」と入力して「男」を得るためには、
C5セルの数式を

=INDEX(A2:A4,MATCH(B5,B2:B4,0))


と記述する。
これでB5セルに「A」と入力すると「男」が、「B」と入力すると「女」が、C5セルの数式の結果として返される。

なお、「SEX」の列指定はINDEX関数の第一引数にハードコーディングしているので、
このやり方だと「SEX」よりさらに左側に別の列があって、その値を取得したいときにはまた別の数式を用意する必要がある。
INDIRECTを使って、範囲を別のシートからVLOOKUPで可変的に取得するようにすれば、この課題は解消できるが、
数式がゴチャゴチャしだすので個人的にも嫌です。（一回やったことあるが「メンテしづらい」と怒られたｗｗ）
一応、この方法は後述する。

●MATCHはVLOOKUPと使い方がちょっと似ている。
　第一引数に「検査値」
　第二引数に「検査範囲」
　第三引数に「照合の種類」を指定する。
　第一、第二のみ必須。第三は任意。ここでは第三引数は"0"（完全一致）を指定している（ちなみにVLOOKUPも同様－第四引数のfalseがそれに相当）

　MATCHは、与えられた検査値が検査範囲内で相対的にどの位置にいるのか検索し、ヒットした場合にその行番号を返却する関数である。
　この例でいうと、B5セルに入力された値を検査値、B2:B4の単一列範囲を検査範囲として検索し、見つかった行の範囲内での相対位置を返却する。
　ここでは「A」と入力すると検査範囲内で一番先頭に存在するので「1」が返ってくる。
　「C」と入力すると検査範囲内で3番めに存在するので「3」が返ってくる。
　検査範囲の開始位置が1行目でない場合、MATCHの返却値は検査範囲と一致しないことに注意したい。
　この例でも、B2:B4を指定しているので、「A」は「B2」セルにあるがMATCHの返却値は「1」になる。
　逆に言うと、このズレを最初から想定しておけば、
　ROW関数にズレ分を加算して見つけたセル番号（この例でいえば"B2"という文字列）を返却値として作り出すこともできる。
　これはINDIRECTとの併用が便利である。詳細は後述。

●INDEXは列範囲を指定して、そこに行番号を与えるとその位置にある値を取得してくれるというシンプルなもの。
　EXCELの関数マニュアルには同名で引数違いの２つの関数が存在するが、
　今回使用しているのは
　第一引数に「配列」
　第二引数に「行番号」を指定するタイプのものである。
　第一引数に「SEX」の列範囲を指定し、第二引数にはMATCHの返却値を指定する。
　これで、B5セルに「A」と入力されるとMATCHが「1」を返し、
　それをもとにINDEXがA2:A4範囲内での1行目の値を取得して「男」を返却する。

これを利用していくとVLOOKUPでは検索できない「左側」の列への検索が可能になる。


これと同じことをINDIRECTで実現できる。
C5セル（数式のセル）に

=INDIRECT("A"&MATCH(B5,B2:B4,0)+1)


と入力する。
B5セルに「A」と入力されると「1」が返ってきて、それに+1（1を足す）して2を作る。
先頭に文字列"A"を加えて"A2"という文字列を生成。
それをINDIRECTに与えてA2セルの値をモロに取得するという流れである。

この"A"の部分を可変的にすると、意識しなくても右や左への検索が可能になる。
例えば別の範囲に以下のようなマスタを作っておいて

  D E
1 SEX A
2 NAME C


で、実際のNAME_IDでの検索前に「どの項目検索するの？」を選ばせる↓

  A B C
1 SEX NAME_ID NAME
2 男 A Aさんです。
3 女 B Bさんです。
4 男 C Cさんです。
5 IDを入力⇒   ※
6 検索項目を指定⇒  


6行目に「検索項目指定行」を追加した。
B6セルにEXCELの入力規則で定めた検索項目の値を列挙し、リストから選択させる形である。
実際の検索の数式は以下のように変更する↓

=INDIRECT(VLOOKUP(B5,D1:E2,2,false)&MATCH(B5,B2:B4,0)+1)


検索項目としてリストで選択した項目に従い、
先のマスタから「この検索項目を指定されたならどこの列を使うか」をVLOOKUPで取得して、
検索するセルを可変的にしている。

検索する部分とは違う範囲や別シート等に、可変化の情報を外だしすると、
こういった汎用的な使い方も可能になる。
ただしあまりにも汎用的にしすぎると数式が複雑になって追い辛くなり、
作った本人でしか（あるいは数年たつと作った本人ですら）わからなくなることがある。


私があまり使わないだけで、多分HLOOKUP（横じゃなくて縦への検索を行う関数）でも同じことが言えるだろう。
ただ、MATCHはともかく、INDEXは行（横）方向への値の取得に対応しているのだろうか。
よくわからん。
INDIRECTを使う方法が確実な気もするので、困ったらINDIRECTを使っておけば問題ないだろう。