本記事では、Excel 2021を使用して検証および画像の作成を行っています。
VLOOKUP関数で左側の値を取得！
「あれ？ VLOOKUP関数って、検索する値より右側の列しか取り出せないんじゃ…？」
そう思ったあなた、正解です！
通常、VLOOKUP関数はそういう仕様になっています。
でも、ちょっと待ってください！
実は、いくつかの関数を組み合わせることで、
VLOOKUP関数でも検索値の左側にあるデータを取得できてしまいます！
今回の記事では、通常はまず使うことはないであろう、
この「VLOOKUPで左側を取得する」テクニックを紹介します。
「なんでそんな面倒なことを？」
そう思いますよね？
目的は、Excelの関数の仕組み、特に「配列」の考え方への理解を深めること！
INDEX関数やCHOOSE関数、IF関数などが、実はこんな動きもできるんだ！
という発見があるはずです。
もちろん、もっと簡単に左側を取得できる XLOOKUP関数 や、
定番の INDEX & MATCH関数 もあります。
（それらは後ほど軽くご紹介します！）
でも今回は、あえてVLOOKUP関数だけで挑んでみましょう！
このチャレンジを通して、あなたのExcel関数スキルが一段階レベルアップするかもしれませんよ！
今回のミッション
現在の状態とゴール
現在の状態: 製品リストがExcelシートにある状態。以下の画像を参照ください。
目指すゴール: 上記のリストから、「製品コード」を入力したら、
対応する「製品名」（左側の列！）と「価格（円）」をVLOOKUP関数を使って表示させる。
ルール：関数以外使用禁止 & 元データ変更ルール
- 使用するのはExcelの「関数」のみです。VBA（マクロ）などは使いません。
- 方法1 では、元データを変更・加工してもOK。
- 方法2, 3, 4 では、元データの変更・加工は禁止！ 関数だけで解決します。
データの準備
サンプルファイル excel_de_himatsubushi007.xlsx (10KB)をダウンロードします。
または、下のデータをコピー＆ペーストします。
| 製品名 | カテゴリ | 価格 (円) | 製品コード |
|---|---|---|---|
| プリンター | 家電 | 35000 | P101 |
| ノートPC | パソコン | 128000 | P102 |
| スマートフォン | 通信機器 | 82000 | P103 |
| モニター | 家電 | 28000 | P104 |
| スキャナー | 家電 | 17800 | P105 |
| キーボード | 周辺機器 | 4300 | P106 |
| タブレット | パソコン | 74000 | P107 |
| マウス | マウス | 980 | P108 |
さあ、準備はできましたか？
VLOOKUP関数の限界（？）を超える旅に出発しましょう！
方法1：元データを加工！ 列を並べ替えてVLOOKUP
考えかた
まずは一番シンプル（？）な方法です。
VLOOKUP関数の「検索範囲の一番左の列を検索する」という基本ルールに立ち返ります。
つまり、検索したい「製品コード」が一番左の列になるように、
元データを並べ替えた別の表を作ってしまおう！ という作戦です。
手順
元データをコピー
まず、元のデータ範囲 (A1:D9) をコピーし、
別の場所 (例: F1セルから) に貼り付けます。
列の入れ替え
次に、貼り付けた表で、「製品コード」の列 (元のD列、貼り付け先ではI列) を一番左 (F列) に移動します。
続いて、I列を選択 → 切り取り → F列を選択 → 右クリックメニューから「切り取ったセルの挿入」を実行。
これで、
- F列に製品コード
- G列に製品名
- H列にカテゴリ
- I列に価格（円）
という並びの表ができます。
VLOOKUP数式入力
検索値を入力するセル (例: K2セル) と、結果を表示するセル (例: L2セルに製品名、M2セルに価格) を用意します。
まず、L2セルに =VLOOKUP(K2, F2:I9, 2, FALSE) と入力します。
（F列の製品コードを検索し、2列目の製品名を取得）
次に、M2セルに =VLOOKUP(K2, F2:I9, 4, FALSE) と入力します。
（F列の製品コードを検索し、4列目の価格を取得）
動作確認
最後に、K2セルに「P103」などと入力し、L2セルに “スマートフォン“、
M2セルに “82000” と表示されれば成功です！
解説
これはVLOOKUP関数の最も基本的な使い方そのものです。
検索列（製品コード）が範囲の左端にあれば、
問題なく右側の列（製品名、価格）を取得できます。
ただし、そのために元データを加工する手間が発生します。
メリット＆デメリット
- メリット:
- VLOOKUP関数の基本動作だけで完結するので、理解しやすい。
- どんなExcelバージョンでも使える。
- デメリット:
- 元データをコピーして加工する手間がかかる。
- 元データが更新された場合に、加工した表も更新する必要がある。（場合によっては）
- ルールで元データ変更禁止の場合は使えない。
方法2：INDEX関数で範囲を再構築！
考えかた
ここからが本番です！
元データには一切手を加えません。
まず、使うのは INDEX 関数！
この関数は、指定した範囲から特定の行や列を取り出すことができます。
たとえば、「表の3行目・2列目にあるデータを取り出したい」といったときに使います。
簡単な使用例
もし、A1:C5 までの表があったとき、=INDEX(A1:C5, 3, 2)
と入力すれば、3行2列目にあるデータを取り出すことができます。
さらに、行番号や列番号に「配列」を使うことで、
元の範囲から必要な列だけを選んだり、好きな順番に並べたりすることも可能です。
これによって、
元のデータをもとにした「仮想的な表」をメモリ上に作り出すことができます。
この仮想的な表をVLOOKUP関数の検索範囲にしてしまえば、
左側の値も取得できる、というわけです。
手順
数式入力
はじめに、検索値を入力するセル (例: F2) を用意します。
次に、結果を表示したいセル (例: G2) に以下の数式を入力します。
=VLOOKUP(F2,INDEX(A2:D9,{1;2;3;4;5;6;7;8},{4,1,3}),{2,3},FALSE)
この数式は、(Microsoft 365/ Excel 2021以降など)スピル対応のExcelで、製品名と価格を同時に隣接セルに表示します。古いバージョンではG12に「製品名」のみ表示される場合があります。
動作確認
最後に、F12セルに “P105” などと入力します。
G12セルに “スキャナー”、H12セルに “17800” と表示されれば成功です！
(スピル非対応の場合はG12に “スキャナー” のみ)
解説
ちょっと複雑に見えますが、分解してみましょう。
INDEX(A2:D9, {1;2;3;4;5;6;7;8}, {4,1,3}) の部分がポイントです！
- A2:D9: 元データの範囲。
- {1;2;3;4;5;6;7;8}: 行番号の配列です。セミコロン ; 区切りなので「縦方向」＝全行を指定しています。( =INDEX(A2:D9, ROW(A1:A8), {4,1,3}) と書いても同じです)
- {4,1,3}: 列番号の配列です。カンマ , 区切りなので「横方向」の列順序を指定します。つまり、元の範囲の 4列目(製品コード)、1列目(製品名)、3列目(価格) という順番で列を取り出して、メモリ上に新しい表を作っています。
要するに、以下の画像のような表を作っています。
VLOOKUP(F12, [INDEXが作った仮想的な表], {2,3}, FALSE)
- 検索値 F12 を、INDEX関数がメモリ上に作成した「製品コードが1列目、製品名が2列目、価格が3列目」の仮想的な表から検索します。
- {2,3}: 取得したい列番号を配列で指定。これで仮想表の2列目（製品名）と3列目（価格）を一度に取得しようとします。（スピル機能）
- FALSE: 完全一致で検索します。
メリット＆デメリット
- メリット:
- 元データを一切変更せずに済む！
- INDEX関数の強力な配列操作機能を学べる。
- デメリット:
- 数式が長く、複雑に見えるため、理解や修正が難しい。
- {1;2;…;8} の部分は行数が増えると書くのが大変 ( SEQUENCE(ROWS(範囲)など) を使う方がスマート)。
- {2,3} での複数列同時取得は、スピル対応のExcelが必要。（非対応の場合は、製品名用に列番号 2、価格用に列番号 3 で別々のVLOOKUP数式を書く必要があります。）
方法3：CHOOSE関数で検索範囲を組み立てる！
考えかた
次は、CHOOSE関数！
この関数は通常、インデックス番号に基づいてリストから特定の値を選ぶのに使いますが、
ここでも配列の力を借ります。
簡単な使用例
=CHOOSE(番号, 値1, 値2, 値3, …)
例えば、数字に応じて曜日を表示したいとき。
=CHOOSE(2, “月曜日”, “火曜日”, “水曜日”, “木曜日”, “金曜日”)
この式は、2 を選んでいるので、結果は 「火曜日」 になります。
今回は、CHOOSE関数にインデックス番号として配列 {1,2,3} を渡します。
それぞれの番号に対応する列範囲（製品コード、製品名、価格）を指定することで、
それらを結合した仮想的な表をメモリ上に作り出すことができます。
これをVLOOKUPで検索します！
手順
数式入力
まず、検索値を入力するセル (例: F4) を用意します。
次に、結果を表示したいセル (例: G4) に以下の数式を入力します。
=VLOOKUP(F4,CHOOSE({1,2,3},D2:D9,A2:A9,C2:C9),{2,3},FALSE)
(注: こちらもスピル対応Excelでの複数列同時表示を想定しています)
動作確認
最後に、F4セルに “P107” などと入力します。
G4セルに “タブレット”、H4セルに “74000” と表示されれば成功です！
(スピル非対応の場合はG4に “タブレット” のみ)
解説
これも分解してみましょう。
CHOOSE({1,2,3}, D2:D9, A2:A9, C2:C9) の部分がカギです！
- {1,2,3}: インデックス番号の配列。CHOOSE関数に「1番目、2番目、3番目の引数を順番に使ってね」と指示します。
- D2:D9: 1番目の引数。元の製品コード列です。
- A2:A9: 2番目の引数。元の製品名列です。
- C2:C9: 3番目の引数。元の価格列です。
- 結果として、メモリ上に「製品コード、製品名、価格」という順番の3列の仮想的な表が生成されます。
VLOOKUP(F4, [CHOOSEが作った仮想的な表], {2,3}, FALSE)
- INDEXの時と同様に、CHOOSEが作った仮想表を検索範囲としてVLOOKUPを実行します。
- {2,3} で仮想表の2列目（製品名）と3列目（価格）を取得します。
メリット＆デメリット
- メリット:
- 元データ変更不要！
- INDEX関数よりは、列を直接指定する感覚で少し直感的に理解しやすいかもしれない。
- CHOOSE関数の意外な使い方を知ることができる。
- デメリット:
- 数式がややトリッキーであることに変わりはない。
- 指定する列が増えると、CHOOSE関数の引数が長くなる。
- 複数列同時取得はスピル依存。（非対応の場合はINDEX同様、列ごとに数式が必要）
方法4：IF関数で列を入れ替える！？
考えかた
最後は、なんと IF関数！
「え、IFって条件分岐でしょ？」
と思いますよね？
ところが、IF関数の第1引数（論理式）に 配列定数 {TRUE,FALSE} を使うと、
ちょっと不思議なことが起こります。
TRUE に対応する範囲と FALSE に対応する範囲を強制的に結合（というか並べる）した、
仮想的な表を作り出すのです！
これを使って、製品コードと製品名の列を入れ替えた表を作ります。
手順
数式入力
はじめに、検索値を入力するセル (例: F6) を用意します。
次に、結果（製品名）を表示したいセル (例: G6) に以下の数式を入力します。
=VLOOKUP(F6,IF({TRUE,FALSE},D2:D9,A2:A9),2,FALSE)
注意: この数式では「製品名」しか取得できません！
つまり、価格を取得する場合は、H6セルに以下の数式を入力します。
（考え方は上記と同様なので説明は省略します。）
=VLOOKUP(F6,IF({TRUE,FALSE},D2:D9,C2:C9),2,FALSE)
動作確認
確認のため、F6セルに “P102” などと入力します。
G6セルに “ノートPC” と表示されれば成功です。
解説
まさにトリッキー！
IF({TRUE,FALSE}, D2:D9, A2:A9) が核心部分です。
- {TRUE,FALSE}: この配列定数がミソ。
- D2:D9: IF関数の「真の場合」の引数。元の製品コード列です。
- A2:A9: IF関数の「偽の場合」の引数。元の製品名列です。
- {TRUE,FALSE} によって、Excelはまず TRUE に対応する D2:D9 を1列目とし、
次に FALSE に対応する A2:A9 を2列目とする、仮想的な2列の表をメモリ上に生成します。
VLOOKUP(F6, [IFが作った仮想的な表], 2, FALSE)
- このIF関数が作った「製品コードが1列目、製品名が2列目」の仮想表を検索します。
- 2: 仮想表の2列目、つまり製品名を取得します。
【重要】この方法の限界
この IF({TRUE,FALSE}, …) の書き方では、基本的に2つの範囲しか組み合わせられません。
そのため、提示した数式では製品名（2列目に対応）しか取得できません。
価格（元のC列）も同時に取得したい場合は、このIFの形では困難です。
方法2(INDEX)や方法3(CHOOSE)を使うか、「価格」取得用に別途数式を組む必要があります。
メリット＆デメリット
- メリット:
- 元データ変更不要！
- IF関数の非常にトリッキーで面白い（マニアックな？）使い方を体験できる。
- デメリット:
- 非常に分かりにくい！ なぜこれで動くのか直感的に理解するのが困難。
- 上記【重要】の通り、この形では基本的に2列構成の仮想表しか作れず、
3つ以上の列を一度に扱いたい場合には不向き。（方法2や3の方が良い）
参考：簡単な方法を紹介！
ここまでVLOOKUP関数で頑張ってきましたが、
実務で「左側の値を取得したい！」という場面に遭遇したら、
素直に以下の関数を使うのがおすすめです！
XLOOKUP関数 (Excel 365 / 2021以降)
=XLOOKUP(検索値, 検索範囲, 戻り範囲)
例: =XLOOKUP(F2, D2:D9, A2:A9)
（製品コードで検索して製品名を返す）
VLOOKUPの弱点を克服した後継関数。
検索範囲と戻り範囲を別々に指定できるので、左側だろうが右側だろうが関係なく検索できます。超シンプル！
INDEX関数 & MATCH関数
=INDEX(戻り値が含まれる範囲, MATCH(検索値, 検索範囲, 0))
例: =INDEX(A2:A9, MATCH(F2, D2:D9, 0))
（製品コード(D列)でF2に一致する行を探し、A列(製品名)のその行の値を返す）
昔からの定番コンビ。XLOOKUP登場前はこれが主流でした。非常に柔軟性が高いです。
FILTER関数 (Excel 365 / 2021以降)
=FILTER(配列, 含む条件)
例: =FILTER(A2:A9, D2:D9=F2)
（D列がF2と一致する行の、A列のデータを抽出）
特定の条件に合うデータを複数行（または単一行）丸ごと抽出したい場合に便利。
複数行の場合、スピル機能で結果を表示します。
これらの関数を使えば、今回の課題はもっと簡単に解決できます。
状況に応じて使い分けましょう！
まとめ
今回は、あえてVLOOKUP関数だけで「検索値の左側を取得する」という、
ちょっと変わった課題に挑戦してみました。
INDEX関数、CHOOSE関数、IF関数といったお馴染みの関数も、
配列と組み合わせることで、普段とは違うデータ操作ができることがお分かりいただけたでしょうか？
紹介した方法2〜4は、正直なところ実務で積極的に使うようなものではありません（笑）
数式が複雑になりがちで、他の人が見たときに理解するのが大変です。
でも、こういったトリッキーな使い方を試してみることで、
関数の本質的な動作や、数式の組み立て方の引き出しが増えることは間違いありません！
普段何気なく使っている関数にも、実は奥深い世界が広がっています。
実務では XLOOKUP や INDEX/MATCH を使いつつ、
たまにはこんな「関数パズル」に挑戦してみるのも面白いかもしれません。
今回の探求が、皆さんのExcelライフをより豊かにする一助となれば幸いです！
