Excel 検索値に一致する行の特定列の値を取得（VLOOKUP関数）
この記事ではVLOOKUP関数の仕様と使い方を紹介します。
このVLOOKUP関数はExcel脱初心者の象徴のような関数で
多くの活用方法があります。
ただしMicrosoft 365のExcelではXLOOKUP関数という
上位版が追加されています。（反映に時間差があるようですが）
HLOOKUP関数の機能も兼ねているうえに
これまで不便だった点の多くが改善されています。
利用できるバージョンのExcelであれば
そちらに切り替えていくとよいでしょう。
使用例
最も一般的な用法はコードなどで台帳と突合し
名称や価格などの情報を検索して表示することです。
これにより転記ミスを無くすことが可能です。
下の例ではF列のコードをB列から検索し、
対応する商品名と価格を自動表示しています。
仕様
=VLOOKUP(検索値,範囲,列番号,検索の型)
| 引数 | 省略時の値 | 説明 |
|---|---|---|
| 検索値 | 省略不可 | 検索する値（検索ワード）を指定。 |
| 範囲 | 省略不可 | 検索値を探す列（左端）～結果を取得する列を指定。 |
| 列番号 | 省略不可 | 検索結果を表示する列を範囲の一番左を1としたときの連番で指定。 |
| 検索方法 | TRUE | ・FALSE：完全に一致するものを取得。 ・TRUE：検索キー以下で最も大きいものを取得。 |
引数1：検索値
今回の例ではF列（コード）を指定しています。
F列の指定を絶対参照$で固定する方が安全です。
なおここに比較演算子は使用できません。
基本的には一致させたい文字列を指定します。
前方、部分、後方一致させることも可能ですが、
ただしVLOOKUP関数自体が1個のセルを
検索する機能のため検索結果を複数表示した場合には向きません。
他の関数を組み合わせるか、Excel2019以降では
FILTER関数を使用する選択肢もあります。
引数2：範囲
検索するセル範囲を指定します。
検索値を探す列は常に一番左である必要がある点に注意が必要です。
例のようにコードで検索する場合、範囲の一番左をコードにしましょう。
なおXLOOKUP関数ではその点が改善されており、
一番左でなくても検索できるようになっています。
もし数式を複数セルにコピーする場合、
列指定のみ絶対参照$で固定すると安全です。
引数3：列番号
結果として表示する項目です。
この例だと1でコード、2で商品名、3で価格が表示されます。
XLOOKUP関数では番号ではなくセル範囲指定（戻り範囲）になります。
絶対参照$も可能なためより便利になっています。
更に検索結果に画像を使用することも可能になりました。
引数4：検索の型
完全に一致させたいので基本的にFALSEを指定します。
（TRUEを活用する少ない例）
TRUEを指定した場合、存在しない「S006」を指定した際に
「S005」の「鯛」がHITする動作になります。
FALSEでは「S006」がHITしません。
検索の型TRUEが省略値なことはVLOOKUP関数の初心者泣かせで
不便な点でしたがXLOOKUP関数ではFALSEにあたるものが
省略値になっており大きな改善となっています。
一致するものがない場合の対応
検索結果が存在しない場合、#N/Aエラーとなり
他の式に悪影響があります。表示だけの問題であれば
そのままでも重大な影響はありませんが、
文書として見た目がよくないため
対処するほうが好ましいです。
XLOOKUP関数では引数（見つからない場合）で対処できるようになっています。
スピルを利用する方法とメリット
Office365やExcel2019以降ではスピルという形式で記述可能です。
これは複数セルに数式をコピーする場合に便利な機能です。
スピルの利用には引数1の検索値を複数セルの範囲で指定します。
数式を入力するのは最初の一つのセルですが、
引数1で指定した高さ分、数式がセルのコピーなしで自動拡大されます。
スピルを利用することで今回の例では、
G3セルに入力するだけでよいため（G4以降にコピーペーストが不要）
以下のメリットがあります。
- 入力の手間が少ない(数式のセルが多いほど効果が大きい)
- 数式を編集した時、コピー忘れのリスクがない（最初のセルを更新すると全セルに反映）
- 絶対参照が不要
- 途中に行を追加した場合、数式のコピー＆ペーストが不要。行削除でも壊れにくい。