Excel 検索値に一致する行の特定列の値を取得(VLOOKUP関数)

2023年1月14日

VLOOKUP関数はセルの値を検索する関数で多くの活用方法があり、

またExcel脱初心者の象徴のような関数でもあります。

ただし新しいバージョンのExcelではXLOOKUP関数という上位版が追加されています。

XLOOKUP関数HLOOKUP関数の役割も兼ねている上に、

VLOOKUP関数で不便だった点の多くが改善されています。

利用できるバージョンのExcelであればXLOOKUP関数に切り替えていくとよいでしょう。

使用例

最も一般的な用法はコードなどで台帳と突合し、名称や価格などの情報を検索して表示することです。

これにより転記ミスを無くすことが可能です。

下の例ではF列のコードをB列から検索し、対応する商品名と価格を自動表示しています。

VLOOKUP関数の基本的な使用例
VLOOKUP関数の基本的な使用例

仕様

=VLOOKUP(検索値,範囲,列番号,検索の型)
引数省略時の値説明
検索値省略不可検索する値(検索ワード)を指定。
範囲省略不可検索値を探す列(左端)~結果を取得する列を指定。
列番号省略不可検索結果を表示する列を指定。範囲の一番左を1としたときの連番。
検索方法TRUE・FALSE:完全に一致するものを取得。
・TRUE:検索キー以下で最も大きいものを取得。
各引数の詳細

引数1:検索値

今回の例ではF列(コード)を指定しています。

オートフィルコピー&ペーストの使用を考えると

F列の指定を絶対参照$で固定する方が安全です。

VLOOKUP関数の基本的な使用例
VLOOKUP関数の基本的な使用例

ここには比較演算子は使用できません。基本的に、一致させたい文字列を指定します。

前方、部分、後方一致も可能ですが、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の検索値を複数セルの範囲で指定します。

スピルでVLOOKUP関数を記述する例
スピルでVLOOKUP関数を記述する例

数式を入力するのは最初の一つのセルですが、

引数1で指定した高さ分、数式がセルのコピーなしで自動拡大されます。

G3に指定したVLOOKUP関数がG5まで自動拡大

スピルを利用することで今回の例では、

G3セルに入力するだけでよいため(G4以降にコピーペーストが不要)

以下のメリットがあります。

  • 入力の手間が少ない(数式のセルが多いほど効果が大きい)
  • 数式を編集した時、コピー忘れのリスクがない(最初のセルを更新すると全セルに反映)
  • 絶対参照が不要
  • 途中に行を追加した場合、数式のコピー&ペーストが不要。行削除でも壊れにくい。

Excelを効率的に習得したい方へ

当サイトの情報を電子書籍用に読み易く整理したコンテンツを

買い切り950円またはKindle Unlimited (読み放題) で提供中です。

Word-A4サイズ:1,400ページの情報量で

(実際のページ数はデバイスで変わります)

基本的な使い方、関数の解説(140種類)、

頻出テクニックと実用例(109種類)、

XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。

体系的に学びたい方は是非ご検討ください。

アップデートなどの更新事項があれば随時反映しています。

なお購入後に最新版をダウンロードするには

Amazonへの問い合わせが必要です。

関連記事

XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧

関数の仕様と使い方の記事一覧