VLOOKUP関数を使って検索列より左側の値を取得することって出来ないの？
どうして出来ないのかイマイチ分からない
VLOOKUP関数を使わない方法で検索列より左側の値を取得する方法は無いの？
方法があるなら、やり方を教えてほしい
こういった疑問に答えます
✔検索列より左側の値を取得する方法のテーマ
①VLOOKUP関数では検索列より左側の値は取得出来ません
②MATCH関数+INDEX関数を使えば左側の値を取得出来ます
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「検索列より左側の値を取得する方法」に関して実体験に基づいて解説しています
今回の作業を一緒にやってみたい場合は、ファイルをダウンロードしておいて下さい
VLOOKUP関数では検索列より左側の値は取得出来ません
結論から言うとVLOOKUP関数では検索列より左側の値を取得することは出来ません
なぜなら、VLOOKUP関数は検索する列より右側を取得(表示)してくれる専門の関数だからです
まずは、VLOOKUP関数の基本をもう一度簡単におさらいしておきましょう
今回は洗濯機の情報を自動的に取得する方法を例にしてみます
これが基本的な設定方法です
「洗濯機管理表」を基に「洗濯機情報一覧」の「洗濯容量」を自動で表示させた設定ですね
もし、VLOOKUP関数の基本的な使い方が分からない時は「VLOOKUP関数は使う場面と基本を押さえれば簡単」という記事で紹介しているので参考にしてみて下さい
つまり、参照範囲の「一番左側の列を基準」に「右側の列の好きなところ」を表示してくれるのがVLOOKUP関数の機能です
さて、ココで問題になってくるのが、種類を選んだら型番を表示させたいと言う時です
「洗濯機管理表」では型番を基準に(一番左にして)管理するのがベストな方法です
ところが買う側からすると「種類から調べて機能的に満足できるなら型番で買う」といった手順になるので２つの表は型番が左右逆になってしまいます
こうなると、「洗濯機管理表」の種類列より左側の値を反映してほしいですよね
そこで無理やり、こんな風に設定してみました
１列目は「種類」なので検索する列を「=VLOOKUP(A14,$B$5:$C$8,0,0)」として型番を取得しようと思いましたが、やっぱりエラーが表示されるだけで上手くいきません
つまり、VLOOKUP関数は指定した範囲の「一番左側の値を基に右側に移動して検索することしか出来ない関数」ということです
繰り返しますが、VLOOKUP関数では検索列より左側の値を取得することは出来ません
MATCH関数+INDEX関数を使えば左側の値を取得出来ます
検索列より左側の値を取得するには、VLOOKUP関数の代わりにMATCH関数とINDEX関数の2つの関数を組合せるのが正解です
なぜなら、2つの関数を組合せることで基準列より左側の値を取得出来るからです
では順番にやり方を見ていきましょう
まずは、使い方がVLOOKUP関数と似ているMATCH関数だけ使ってみましょう
MATCH関数は「検索する値が何行目にあるのか調べるため」の関数です
今回の式は「=MATCH(A14,B5:B8,0)」なのでこれを日本語にしてみましょう
「=MATCH(A14セルを調べてね,B5セル～B8セルの範囲で,検索するの条件は完全一致するもので)」といった感じです
つまり、「全自動洗濯機」という名前を「種類」列の何行目にあるか？ という結果を数字で表示してくれるのがMATCH関数で出来ることです
表示されているのは「３」なので「３行目」に探しているものがあるよ。と教えてくれています
さて、続いてはINDEX関数です
INDEX関数は、２通りの使い方がありますが１種類だけ押さえておけば良いのでまずはINDEX関数だけで使い方を見てみましょう
INDEX関数の説明に入る前にMATCH関数は好きな場所に「=を除いて」置いておくのがオススメです
理由は後で組合せる時に使えるからです
関数を組合せる(ネスト)させると関数の内容が長くなって混乱しやすくなります
慣れないうちは、後で使えるように取っておいて準備が整ったら合体させた方が簡単です
ではINDEX関数の解説です
今回の式は「=INDEX(A5:C8,3,1)」なのでこれを日本語にしてみましょう
「=INDEX(A5セル～C8セルを対象に,3行,1列目の値を表示してね)」という意味になります
ただ、INDEX関数は表現が難しいので使いにくく思えます
今回利用したつ使い方は「配列,行番号,列番号」という方法です
この「配列」を「範囲」と同じ意味だと思うと理解しやすいと思います
今回は「A5～C8の範囲を対象に3行1列目」つまり「A7のセル」の内容を表示してくれるという訳です
これで下準備は出来ました
今回のポイントはINDEX関数の行数をどうやって柔軟な設定にするか？ ということです
INDEX関数を使えば一番左の列の値を取得出来ますが、このままだと「全自動洗濯機」が何行目か判断できません
でも、さっきのMATCH関数で「全自動乾燥機の行数を特定」出来てましたよね？
もう気づきましたか？ つまりINDEX関数の中にMATCH関数を組み合わせれば柔軟に型番を表示することが出来ますよね
では、実際に設定してみましょう
こんな感じで無事型番を取得する事ができました
今回やったことは、3行目という行数を直接入力する代わりにMATCH関数を貼り付けただけです
では式を見ていきましょう「=INDEX(A5:C8,MATCH(A14,B5:B8,0),1)」
これを日本語にすると「=INDEX(A5セル～C8セルの範囲の,MATCH関数の結果(3)行,1列目の値を表示してね)」という意味になります
行数の指定をMATCH関数にするだけなので、思ったよりも簡単ですよね
こんな風に検索系の関数を組合せることで、出来そうで出来ないことも実現出来てしまうということに気付けるとExcelが益々面白くなりますね
繰り返しになりますが、検索列より左側の値を取得するには、VLOOKUP関数の代わりにMATCH関数とINDEX関数の2つの関数を組合せるのが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す