【Excel】VLOOKUP関数、HLOOKUP関数、XLOOKUP関数の違いと使い分け。結局どの関数を使えばよいのか

Excelには、指定した値に基づき検索を行い、対応するデータを表示するため、VLOOKUP関数、HLOOKUP関数、XLOOKUP関数があります。以下では、それぞれの特徴や使い分けについて解説していきます。

縦方向に並ぶリストから検索するならVLOOKUP関数

VLOOKUP関数では、縦方向に並ぶリストから特定の値に紐づくデータを自動で入力することができます。

たとえば、商品の注文内容に対して自動で価格を表示させる際に用います。以下のような注文内容の価格を縦方向に並んでいる商品リストから引用して表示させてみます。商品リストは別のシートなどにすることが多いですが、今回はわかりやすさのため同じシートにしています。

VLOOKUP関数では、検索したい値（検索値）を指定し、検索に用いるデータの範囲、列から紐づく値を指定することができます。

関数	役割
VLOOKUP(検索値,範囲,列番号,[検索方法])	検索値で指定した値を、範囲の左端列から検索。 検索値と一致するデータの左から列番号目の値を抽出し表示する。

商品Dの価格を表示させるため、以下のように数式を入力します。注意が必要なのは、指定する範囲は商品名と価格の範囲です。VLOOKUP関数では、左端の列を検索するように決まっているので、商品名が範囲の左端の列に来るようにしなければいけません。

=VLOOKUP(C4,$H$4:$I$13,2,FALSE)

それぞれの数式と役割については以下の通りです。

数式	役割
VLOOKUP(C4	セルC4の値を検索する。今回は「商品D」という名前で検索する
$H$4:$I$13	検索に用いるデータの範囲はセルH4~I13の範囲。 今回は、商品名と価格のデータの範囲を指定する。また、商品名で検索したいので、商品名の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は価格を表示させたいので範囲の「2」列目を指定する。
FALSE	検索値と完全に一致するデータを検索する。

そうすると以下のように、商品Dの価格が表示されました。

このようにVLOOKUP関数は、縦方向に並べられたリストから検索することに活用することができます。

横方向に並ぶリストから検索するならHLOOKUP関数

VLOOKUP関数が縦方向に並ぶリストから検索するのに対して、HLOOKUP関数は、横方向に並ぶリストから検索します。

VLOOKUP関数と同じように商品の注文内容に対して自動で価格を表示させるようにします。今回は、商品リストは左から右に横方向に並んでいます。

HLOOKUP関数では、検索したい値（検索値）を指定し、検索に用いるデータの範囲、行番号から紐づく値を指定することができます。基本的な数式の構造はVLOOKUP関数と同じですが、HLOOKUP関数では「行番号」を指定します。

関数	役割
HLOOKUP(検索値,範囲,行番号,[検索方法])	検索値で指定した値を、範囲の上端行から検索。 検索値と一致するデータの上から行番号目の値を抽出し表示する。

商品Dの価格を検索するには、以下のように数式を入力します。VLOOKUP関数と同じように、注意点としては商品名の行が上端列（一番上の行）になるように範囲を選択します。

=HLOOKUP(C4,$H$4:$Q$5,2,FALSE)

それぞれの数式と役割については以下の通りです。

数式	役割
HLOOKUP(C4	セルC4の値を検索する。「商品D」という名前で検索する
$H$4:$Q$5	検索に用いるデータの範囲はセルH4~Q5の範囲。 今回は、商品名と価格のデータの範囲を指定する。また、商品名で検索したいので、商品名の行が範囲の上端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は価格を表示させたいので範囲の「2」行目を指定する。
FALSE	検索値と完全に一致するデータを検索する。

そうすると以下のように、商品Dの価格が表示されました。

このようにHLOOKUP関数は、横方向に並べられたリストから検索することに活用することができます。

縦方向・横方向に並べられたリストから検索できるXLOOKUP関数

XLOOKUP関数は、Microsoft 365/Office 365向けに追加された機能です。VLOOKUP関数、HLOOKUP関数両方の機能を兼ね備えており、より柔軟な検索を実現することができます。具体的には以下のような特徴があります。

• 縦方向並べられたリスト、横方向に並べられたリスト両方から検索できる
• 検索範囲が左端列または上端行になくても検索することができる

縦方向に並べられたリストから検索

まずは縦方向の検索について見ていきます。VLOOKUP関数と同様に、商品名に基づき検索を行い、それぞれの価格を表示させていきます。ただし、今回は商品リストの商品名が価格の右側にあるため、VLOOKUP関数は使用できません。

XLOOKUP関数では、検索値で指定した値を検索範囲から探し、同じ位置にある戻り範囲のデータを表示させます。検索範囲と戻り範囲は縦方向・横方向どちらも指定できます。

関数	役割
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])	「検索値」で指定した値を、「検索範囲」から検索。 検索値と一致するデータと同じ位置（列または行）にある「戻り範囲」のデータを表示させる。 「[見つからない場合]」「[一致モード]」「[検索モード]」は省略可能。

たとえば、商品Dの価格を表示させるには以下のように数式を入力します。

=XLOOKUP(C4,$I$4:$I$13,$H$4:$H$13)

それぞれの数式と役割については以下の通りです。

数式	役割
XLOOKUP(C4	セルC4の値を検索する。「商品D」という名前で検索する
$I$4:$I$13	検索に用いるデータの範囲はセルI4~I13の範囲。 今回は、商品名の記載された列を指定する。 他の商品も同じ範囲で検索したいので、絶対参照にする。
$H$4:$H$13	検索した結果、表示させたいデータの範囲はセルH4~H13の範囲。今回は価格を表示させたいので、価格が記載された列を指定する。

そうすると以下のように、商品Dの価格が表示されました。

このようにXLOOKUP関数は、縦方向のリストで、検索したい範囲が左端になくても検索することができます。

横方向に並べられたリストから検索

横方向の検索についても基本的な考え方は同じです。今回のリストでは、今回は商品リストの商品名が価格の下にあるため、HLOOKUP関数は使用できません。

商品Dの価格を表示させるには、以下のように数式を入力します。

=XLOOKUP(C4,$H$5:$Q$5,$H$4:$Q$4)

それぞれの数式と役割については以下の通りです。

数式	役割
XLOOKUP(C4	セルC4の値を検索する。「商品D」という名前で検索する
$H$5:$Q$5	検索に用いるデータの範囲はセルH5~Q5の範囲。 今回は、商品名の記載された行を指定する。 他の商品も同じ範囲で検索したいので、絶対参照にする。
$H$4:$Q$4	検索した結果、表示させたいデータの範囲はセルH4~Q4の範囲。今回は価格を表示させたいので、価格が記載された行を指定する。

そうすると以下のように、商品Dの価格が表示されました。

このようにXLOOKUP関数は、横方向のリストで、検索したい範囲が上端になくても検索することができます。

結局どの関数を使えばよいのか

VLOOKUP関数、HLOOKUP関数、XLOOKUP関数には、それぞれ特徴がありますが、基本的にはVLOOKUP関数を使用することをおすすめします。理由は以下の通りです。

• XLOOKUP関数はMicrosoft 365/Office 365以降のバージョンでしか使用できない
• 横方向に並べられたリストはデータの操作の観点で望ましくない

XLOOKUP関数はMicrosoft 365/Office 365以降のバージョンでしか使用できない

上記の通り、XLOOKUP関数はVLOOKUP関数、HLOOKUP関数よりも柔軟な検索ができますが、Microsoft 365/Office 365以降のバージョンでしか機能しません。人によっては古いバージョンのExcelでファイルを開く可能性もあるため、どのようなバージョンでも機能するVLOOKUP関数を使用する方が望ましいです。

そのため、XLOOKUP関数を使用せずに済むように、データを左端列から検索できるような順序で並べるなど工夫しましょう。

横方向に並べられたリストはデータの操作の観点で望ましくない

検索が必要な膨大なデータを横方向に並べることは、データの操作の観点で望ましくはありません。そのため、基本的にはリストは縦方向に作成し、HLOOKUP関数を使用しないようにします。

Excelでは、縦方向のリストを前提としてフィルター、テーブル機能が実装されており、横方向のデータを並べ替えたり、絞り込んだりすることはできません。また、横方向に膨大なデータが並ぶと見づらく、スクロールも手間がかかってしまいます。そのため、検索が必要なほど多くのデータを並べる際は縦方向が望ましいです。

HLOOKUP関数を使用するのは、編集・加工ができない横方向のリストを使用する場合のみにすることをおすすめします。

上記の通り、原則VLOOKUP関数を使用することを推奨しますが、データの状況や作業環境に応じて柔軟に使い分けていただければと思います。