Excel 検索値に一致する行の特定列の値を取得（VLOOKUP関数）
VLOOKUP関数はセルの値を検索する関数で多くの活用方法があり、
またExcel脱初心者の象徴のような関数でもあります。
ただし新しいバージョンのExcelではXLOOKUP関数という上位版が追加されています。
XLOOKUP関数はHLOOKUP関数の役割も兼ねている上に、
VLOOKUP関数で不便だった点の多くが改善されています。
利用できるバージョンのExcelであればXLOOKUP関数に切り替えていくとよいでしょう。
使用例
最も一般的な用法はコードなどで台帳と突合し、名称や価格などの情報を検索して表示することです。
これにより転記ミスを無くすことが可能です。
下の例ではF列のコードをB列から検索し、対応する商品名と価格を自動表示しています。
仕様
=VLOOKUP(検索値,範囲,列番号,検索の型)
| 引数 | 省略時の値 | 説明 |
|---|---|---|
| 検索値 | 省略不可 | 検索する値（検索ワード）を指定。 |
| 範囲 | 省略不可 | 検索値を探す列（左端）～結果を取得する列を指定。 |
| 列番号 | 省略不可 | 検索結果を表示する列を指定。範囲の一番左を1としたときの連番。 |
| 検索方法 | TRUE | ・FALSE：完全に一致するものを取得。 ・TRUE：検索キー以下で最も大きいものを取得。 |
引数1：検索値
今回の例ではF列（コード）を指定しています。
F列の指定を絶対参照$で固定する方が安全です。
ここには比較演算子は使用できません。基本的に、一致させたい文字列を指定します。
前方、部分、後方一致も可能ですが、VLOOKUP関数自体が1個のセルを検索する機能のため、検索結果を複数表示した場合には向きません。
他の関数を組み合わせるか、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以降にコピーペーストが不要）
以下のメリットがあります。
- 入力の手間が少ない(数式のセルが多いほど効果が大きい)
- 数式を編集した時、コピー忘れのリスクがない（最初のセルを更新すると全セルに反映）
- 絶対参照が不要
- 途中に行を追加した場合、数式のコピー＆ペーストが不要。行削除でも壊れにくい。
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。