【Excel/関数】キーが範囲の左端になくVlookupが使えない場合　Match関数とINDIRECT関数を使ったらできた

VlookUpは検索範囲の左端をキーとするため、左端にキーがないと使えません。

キー列が持ってきたいカラム（例えば担任氏名）より左にないとダメなんです。
↓たとえばこうだとダメ

もちろん、担任氏名より左にキーを持ってくればいい、だけの話です。

だけど、なにかしらの制約で担任氏名より左にもってこらないとしたら。注文主がキーはD列じゃないとダメとかいうワガママを言ったら。。。

うおおお、と考えた末、INDIRECTとMATCH関数でVLOOKUPみたいな動きを再現することができました。INDIRECT関数もMATCH関数も使ったとこがなかったのでお勉強になりました。

分解して解説します。

まず、MATCH関数。

MATCH（検索したい値 , 検索する範囲 , 検索の種類）

検索したい値にF2の1-4を指定しています。

検索する範囲はD:DとD列全体を指定しています。

検索の種類は完全一致「０」を指定しています。

検索値「1-4」は検索範囲の中で4行目なので、MATCH関数は 4 を返します。

下図はMACTH関数だけにしたものです。

下図の二つ目の図は検索範囲を「D2:D6」と範囲をD列の2行目から6行目と限定した例です。その場合、「1-4」は範囲内の3つ目になるので「３」が返ります。

今回は組み合わせるINDIRECT関数でセル番地を求めたいので、D列全体を検索範囲として、「エクセルの何行目か」を出力するようにしました。

次のに、INDIRECT関数です。

INDIRECT関数はむずかしい・・・と思って今まで使ってこなかったのですが、

セル番地を文字列で指定するとそのセルの値を持ってきてくれる関数、ということらしいです。

たとえば、＝INDIRECT(“A2”)　とすると、セルA２の値を表示します。

下図では指定したセルA２の「はなこ」が結果として表示されています。

INDIRECT関数についてはこちらのサイトにて勉強させていただきました！

で、先ほどの話に戻って、MATCH関数で「４」が求められたので、値を持ってきたいC列と組み合わせて「C4」としたい→”C”& MATCH(F2,D:D,0) で「C4」ができますね。

で、INDIRECT（”C4”）でセルC4の「中村花子」を持ってこれるので、

INDIRECT(“C”&MATCH(F2,D:D,0)) 　です。

Microsoft Office365 （今はMicrosoft365というのかな）ではXLOOKUP関数という新しい関数があり（今はMicrosoft365だけなのかな？）それだとキーが左端じゃなくても大丈夫です。

XLOOKUP関数で同じことをやろうとする場合、下図のように指定します。

検索値：F2（1-4）を指定します

検索範囲：キー値のあるD列の範囲を指定します

戻り範囲：持ってきたい値のある範囲、C列（担任氏名）の範囲を指定します。

見つからない場合：今回は「該当なし」が返るように指定しました。

一致モードは0（完全一致）を指定しました。

見つからない場合の戻り値まで指定できるとは親切だわ～

XLOOKUP関数については以下のサイトを参考とさせていただきました。

お読みいただきありがとうございました！