こんにちは、sysin-jamです。
Vlookup関数はキーで表を検索すると値を返してくれるとても便利な関数です！
ただ、検索するキー列より左側は検索することができません。
Xlookupがあればできるんですが、バージョンが古い開発端末とかだと未実装です。。。
キー列より左側にある値を取得する方法を紹介します！
Vlookupとは
表の縦方向にデータを検索して、一致した値と同じ行の値を返す関数です。
縦（垂直）＝「Vertical」 、探す＝「Lookup」でVlookup。
だから横（水平）＝「Horizon」のHlookupもありますよ。
例だと「E2の値(aaa)をキーにB1:C6の範囲を検索し、一致行の2列目の値(100円)を返す」って感じ。
=VLOOKUP(E2,$B$1:$C$6,2,FALSE)
ただ、Vlookup関数だと右側しか検索ができません。
キー列がB列のため、逆にあるA列は取得できません。。。
これが常々やりたくなる処理です。
キー列を左側に持ってきても良いですが表のフォーマットを変えるのも、、、
方法①：Vlookupで配列を使用する
=VLOOKUP(E2,IF({1,0},$B$1:$B$6,$A$1:$A$6),2,FALSE)Vlokupの「範囲」の部分がIf関数に変えています。
If関数の第1引数に指定している{ }は配列定数と呼ばれていて配列を設定できる記述方式のよう。
{1,0}で指定：If関数の第2引数・第3引数で2次元配列を作ります
{0,1}で指定：第3引数・第2引数で2次元配列を作ります
ちなみに1はTrue、0はFalseなので、{True, False}と書いても大丈夫です。
※{1,0,1}と記述すると、第2引数・第3引数・第2引数で3次元配列を作るみたい。何か凄いな。
今回は{1,0}で指定しているので、B列（キー列）、A列（値1列）で2次元配列を作ります。
↓こんな感じ↓
{
"キー","値1";
"aaa","リンゴ";
"bbb","みかん";
"ccc","バナナ";
"ddd","キウイ";
"eee","ぶどう"
}
2次元配列の中ではキー列が左側にあるので、Vlookupが使えるようになります！
他のサイトだと配列を使用した関数は重くなるみたいなので推奨されてなさそうです。
私はVlookupと同じ処理をしているのがパッと見で分かりやすいので使いがちです。
大量に関数を使う場合は控えようかなと思います。
方法②：「INDEX関数」と「MATCH関数」を組み合わせる
=INDEX($A$1:$C$6,MATCH(E2,$B$1:$B$6,0),1)Index関数は範囲の中から、行・列を指定して値を返す関数です。
Match関数は範囲の中から値を検索し、相対的な位置を返す関数です。
Indexの「行番号」の引数内でMatchを使用しています。
Matchでキー列内のHITした行（例だと2行目）の範囲内の1列目（例だとA列）の値を返します。
Indexの列番号に2を設定するとB列（キー）、3を設定するとC列（値2）を返してきます。
方法③：「OFFSET関数」と「MATCH関数」を組み合わせる
=OFFSET($A$1,MATCH(E2,$B$1:$B$6,0)-1,0)Offset関数は参照で指定した位置を起点として、行・列数をシフトした位置の情報を返す関数です。
起点の行・列数は0・0なので、1・2だと1行下、2列右のセルの情報を返します。
行・列数はマイナスも指定できます。
行数マイナスの場合は上にシフト、列数マイナスの場合は左にシフトしていきます。
Match関数は範囲の中から値を検索し、相対的な位置を返す関数です。
Offsetの「行数」の引数内でMatchを使用しています。
Matchでキー列内のHITした行-1（例だと2行目）、0（例だとA列）の値を返します。
まとめ
Vlookupを使っていて、「左側の値を取得したいなぁ」となって使うことも多々あるので、方法①「Vlookupで配列を使用する」をよく使いますが、データ量が多いと遅くなるようです。
なのでOFFSETよりも感覚的に使えるINDEXを使う方法②の方がオーソドックスなんですかね。
一長一短ですが、INDEXの方が、OFFSETのように起点からｘｘって考えずに使えるし。（表範囲の追加削除が多い場合は、指定するセル位置・範囲も少ないOFFSETの方が楽なのかな）
コメント