VLookupの列指定

本日頂いたご質問。

こんな表で店Noを入力したときに、グループマスターから対応するグループコードを表示したい。

このような場合に真っ先に思いつくのはVLOOKUP関数です。
しかし残念ながら今回の事例では使えません。

列指定は右側だけ

VLOOKUPの引数は以下のとおりです

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

引数の中の列番号のHELPは以下のとおりです

列番号 必ず指定します。目的のデータが入力されている列を、範囲内の左端から数えた列数で指定します。列番号に 1 を指定すると、範囲の左端の列の値が返され、列番号に 2 を指定すると、範囲の左から 2 列目の値が返されます (3 列目以降も同様に指定します)。

列番号に次の値を指定すると、対応するエラーが返されます。
・1より小さい場合、#VALUE! エラー値が返されます。
・範囲の列数より大きい場合、#REF! エラー値が返されます。

VLOOKUP関数は引数範囲で指定した範囲の左端から、引数列数で指定した数だけ右側に移動した列から値を引っぱってきます。 またHELPにある通り列番号には１以上の数値を指定しなければいけないため、マイナスの値を入力して範囲の左側から値を引っぱって来ることが出来ません。

このような時の対処方法として以下のような方法があります。

表を組み替える

可能であれば、素直に表の列を並び替えるのが手っ取り早いです。
そもそもVLOOKUPで値をひっぱてくることが前提であれば、そのようにマスターテーブルを作りましょう。

INDEX関数とMATCH関数を使用する

表を組み替えずに対処する方法としてINDEX関数とMATCH関数を使用する方法があります。
この方法だと検索した列より左側の値を引っぱって来ることが可能です。

INDEX(範囲、行番号, [列番号], [領域番号])

MATCH(検査値, 検査範囲, [照合の型])

INDEX関数は引数範囲に指定した範囲内で縦方向に引数行番号、横方向に引数列番号だけ移動した所にある値を表示する関数です。

MATCH関数は引数検査値の値が引数検査範囲ないのどの位置にあるのか相対的な場所を数字で返す関数です。
検査範囲を指定する時は１列or１行の範囲を指定してください。

実際に入力する関数は以下のとおりです
INDEX(E4:E8,MATCH(A3,F4:F8),1)

関数をばらして処理内容を追ってみましょう。
まずMATCH関数部分。

引数検査値に指定した「５」が引数検査範囲のどの場所にあるのか探します。
この場合指定した場所は上から５番目にありますので「５」という答えが返ってきます。

次にINDEX関数部分。
引数範囲に指定した範囲内で上から５番目(MATCH関数で算出)、横に１番目の値を返します。

基本はVLOOKUP関数で

INDEX関数とMATCH関数の組み合わせだとマスターテーブルを柔軟に作ることが出来るのはメリットですが、やはり関数が入れ子になりわかりにくくなります。
基本はVLOOKUP関数を使えるようにマスターテーブルを作るのがベターだと思います。

たった1日で即戦力になるExcelの教科書