Excel(エクセル)のHLOOKUP関数(エイチルックアップ)は、表を横方向に検索して検索値を抽出する大変便利な関数です。
VLOOKUP関数(ブイルックアップ)が表を縦方向に検索して抽出する関数なので、その姉妹関数というイメージですね。
HLOOKUP関数の”H"は、「Horizon（水平、横）」の頭文字で、VLOOKUP関数の”V"は「Vertical（垂直）」の頭文字と覚えましょう。
今回は、HLOOKUP関数の使い方と、HLOOKUP関数に関する操作全般を幅広く紹介します。
VLOOKUP関数の使い方は下の記事からどうぞ
HLOOKUP関数は、横方向に検索して値を抽出する関数
HLOOKUP関数(エイチルックアップ)は、表を横方向に検索して情報を抽出する関数です。
HLOOKUPの「H」は横を意味する「Horizon」の頭文字で、LOOKUPは英語で「探す」という意味です。
指定した検索条件に『当てはまる値』を、指定した検索範囲から調べて、指定した位置から抽出することが出来る関数です。
もう少しかみ砕いて、具体的な下の図で説明すると、
商品番号が『２５』の商品を、指定した商品リスト表から調べて、表の上から３行目の『商品名』を抽出することが出来る関数です。
この説明で理解出来なくても大丈夫です。
この後に紹介するHLOOKUP関数の使い方を見れば感覚的に理解出来るようになります。
いったん『表から条件に合った値を抽出することが出来る』と、理解していただいて結構です。
HLOOKUP関数の構文と引数
HLOOKUP関数の構文(書式）と引数を紹介します。
構文とは、関数によって利用する引数の数や種類のことを言います。関数の書式ともいいます。
引数とは、関数を使用するために指定するセル番号や数値、文字などのことを言います。
構文
HLOOKUP関数の構文は下のとおりで、４つの引数を指定する必要があります。
「検索値」「検索範囲」「行番号」「検索方法」の４つを指定することで利用出来ます。
＝HLOOKUP(検索値,検索範囲,行番号,[検索方法]）
４つの引数
HLOOKUP関数を利用するためには、以下の４つの引数を設定する必要があります。
検索値
数値やセル範囲、文字列を指定できます。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：”薬”、B4）
検索範囲
検索する範囲を指定します。検索範囲を固定したければ、絶対参照の$(ドル）を付与します。（例：B4:E15、$B$4:$E$15）
行番号
検索範囲の一番上から何行目の値を抽出するかを数値で指定します。（例：３）
検索方法
「FALSE(もしくは0）」か、「TRUE(もしくは1）」のどちらかを選びます。（例：FALSE、TRUE、0、1） ※詳細は以下を参照
検索方法の「FALSE」と「TRUE」の違い
４つ目の引数「検索方法」には２種類がありますが、「FALSE」と「TRUE」の違いを説明します。
結論からお伝えすると、HLOOKUP関数を初めて触る方は「FALSE」の一択で覚えましょう。「TRUE」を利用する機会はほぼ無いと言っていいでしょう。
FALSE
検索方法「FALSE」を指定した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合は、エラー値「#N/A」が表示されます。
TRUE
検索方法「TRUE」を指定した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合には、近似値が表示（「検索値」未満で最も大きい値）されます。
「FALSE」と「TRUE」の違いは、検索値が検索範囲に無かった場合のアウトプットが異なります。そのため、検索値が検索範囲に存在していた場合は、FALSEでもTRUEでも計算結果は同じです。
実務的に「TRUE」で利用する場面は極端に少ないため、「FALSE」を使いましょう。
「TRUE」を使った実例は下の見出しで紹介していますので興味のある方は確認してみてください。
HLOOKUP関数の使い方と手順を紹介
それでは下の表を使って、HLOOKUP関数で「商品番号」が「25」の「商品名」を抽出する方法を手順に沿って説明します。
手順１．HLOOKUP関数を挿入
C9セルにHLOOKUP関数を挿入するため、「＝HLOOKUP(」と入力します。
＝HLOOKUP(
手順２．HLOOKUP関数の第１引数を入力
続いて第１引数「検索値」を設定するため、商品番号「25」が入力されているB9セルをクリックし、”,”(カンマ)を入力します。
関数の真横のセルでクリックしづらい場合は、キー入力で直接「B9」と入力してください。
＝HLOOKUP(B9,
手順３．HLOOKUP関数の第２引数を入力
続いて第２引数「検索範囲」を設定するため、表全体をドラッグで選択します。選択し終えたら、”,”(カンマ)を入力します。
=HLOOKUP(B9,B2:J5,
HLOOKUP関数を他のセルにもコピペで利用する場合は、必要に応じてファンクションキー「F4」を使って絶対参照の「$」を付けておきましょう。絶対参照が分からない方は、以下の記事を参考にしてみてください。
=HLOOKUP(B9,$B$2:$J$5,
手順４．HLOOKUP関数の第３引数を入力
続いて第３引数「行番号」を設定します。手順３で選択したセル範囲の一番上の行から３行目が「商品名」行なので、「3」を入力します。
=HLOOKUP(B9,$B$2:$J$5,3,
手順５．HLOOKUP関数の第４引数を入力
続いて第４引数「検索方法」を設定します。「FALSE」と「TRUE」の２種類がありますが、上の見出しで説明したとおり、基本的に「FALSE」を使いましょう。
=HLOOKUP(B9,$B$2:$J$5,3,FALSE
手順６．HLOOKUP関数を確定
第４引数を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、表の中から商品番号25の商品名「風邪薬」が抽出されました。
HLOOKUP関数を使う上での注意点
HLOOKUP関数を利用する上で、知っておくべき３つの注意点を紹介します。
検索値が複数存在する場合
１つ目は、検索範囲の中に検索値が２つ以上存在している場合です。
下の表には、検索値の商品番号「25」が検索範囲内に２つ存在してます。(F2セルとH2セル）
この場合、HLOOKUP関数で商品名を抽出しようとすると、一番左の値が優先されて、F4セルの商品名「風邪薬」が抽出されます。
そのため、右側のH4セルの商品名「消臭剤」を抽出しようとする場合は、HLOOKUP関数を複数条件で利用する必要があります。
この方法を使いたい場合は、下の「HLOOKUP関数の複数条件の使い方」の見出しを参考にしてください。
引数「検索値」と「検索範囲」の位置関係
２つ目の注意点は、1つ目の引数「検索値」と２つ目の引数「検索範囲」の位置関係です。
下のHLOOKUP関数の正しい例のように、検索値が検索範囲の一番上の行にある場合は、正しく値が抽出されます。
しかし、下の図のように検索値が検索範囲の中に入っていない状態でHLOOKUP関数を利用すると・・・
下の図のように、HLOOKUP関数の結果は「#N/A」とエラー値が表示されてしまいます。
検索値が検索範囲の中央にいるような場合でも、同様に「#N/A」とエラー値が表示されてしまいます。
他のセルにコピペする場合
HLOOKUP関数を他のセルにコピペして利用する際には、引数「検索範囲」は絶対参照を設定しておく必要があります。
どういうことか図解で説明します。
C9セルに挿入されているHLOOKUP関数「=HLOOKUP(B9,B2:J5,3,FALSE)」を真下のC10セルにコピペします。
そうすると、検索範囲内に商品番号「18」は存在するにも関わらず、HLOOKUP関数の結果は「#N/A」とエラー値が表示されてしまいます。
あらためてコピーしたC10セルのHLOOKUP関数を確認してみます。
そうすると、以下のように３つ目の引数「検索範囲」まで一つ下にスライド(移動)してしまっています。
=HLOOKUP(B10,B3:J6,3,FALSE)
そのため、検索値の商品番号「18」が検索範囲から外れてしまっているためエラーが表示されます。
HLOOKUP関数を他のセルにもコピペで利用する場合は、ファンクションキー「F4」を使って、下のように引数「検索範囲」に絶対参照【$】を設定するように癖付けましょう。
=HLOOKUP(B10,$B$2:$J$5,3,FALSE)
HLOOKUP関数の結果がエラー「#N/A」になる場合
HLOOKUP関数を使った結果がエラー値「#N/A」となる理由は、検索範囲内に検索値が見つからないということです。
エラーが発生する原因は以下の４つが考えられます。
- 検索値が検索範囲の一番上の行に存在しない
- HLOOKUP関数の検索範囲が絶対参照に未設定
- HLOOKUP関数の検索値がわずかに異なる
- セルの書式が文字列
番号①と➁の原因は上の見出しで紹介した通りです。エラーの原因となった操作があれば修正しましょう。
番号③と④がどういった内容か紹介します。
③検索値が表内の文字とわずかに異なる
３つ目のパターンは、関数で指定した検索値と表内の文字がわずかに異なるために、検索値がヒットしない場合です。
例えば、「11 222」と「11 222」の数値は一見同じ値に見えるかもしれませんが、前者は”11”と”222”の間に半角スペースが含まれていて、後者は、”11”と”222”の間に全角スペースが含まれます。
検索値が「11 222」(半角スペース）で検索範囲の値が「11 222」(全角スペース）のため、半角スペース分異なるのでHLOOKUP関数上は異なる値とみなされ、「#N/A」が表示されます。
同様に、半角スペースが先頭や末尾に入り込んでいる、漢字が異なるなど、意外に陥りやすい誤りです。
④セルの書式が文字列
４つ目のパターンは、検索値か検索範囲の値どちらかのセルの書式設定が【文字列】になっている場合です。
下図のように、検索値「25」は表内に存在しているので一見問題なさそうに見えますが、HLOOKUP関数の抽出結果は「#N/A」です。
原因は、B9セルの書式設定(表示形式)は【文字列】であることが原因です。
セルの書式設定(表示形式）を確認するには、対象のセルを右クリックし、右クリックメニューの「セルの書式設定」からセルの表示形式を確認してください。
文字列になっている場合は、表示形式を「標準」に変更しましょう。
VLOOKUP関数のエラー値の発生原因とエラーの解消方法、別の値への変換方法は以下の記事で詳しく紹介しています。HLOOKUP関数でも同様に適用できますので、合わせて確認してください。
HLOOKUP関数で別のシートや別ブックの値を抽出する方法
HLOOKUP関数の検索値や検索範囲が異なるワークシートや異なるExcelブックに存在する場合でも関数は利用出来ます。
HLOOKUP関数の使い方は基本的にほぼ同じですが、シート（ブック）を切り替える手順が追加されますので操作手順を紹介します。
手順１．HLOOKUP関数の第１引数を入力
HLOOKUP関数を挿入するセルに、第１引数「検索値」を入力し、末尾に「,」(カンマ)を入力します。
手順２．検索範囲のシートをクリック
検索範囲のデータがある「表」シートをクリックします。そうすると、関数の末尾に「表！」とワークシート名の後に「！」が付きます。
＝HLOOKUP(B3,表!
手順３．検索範囲をドラッグ
続いて第２引数「検索範囲」を設定するため、表のセル範囲をドラッグで選択します。選択し終えたら、「F4」キーを押して絶対参照に設定します。
=HLOOKUP(B3,表!$B$2:$J$5,
手順４．関数を完成させる
残りは通常の手順と同じです。第３引数「行番号」と第４引数「検索方法」を入力して「Enter」キーを押して関数を確定させてください。
=HLOOKUP(B3,表!$B$2:$J$5,3,FALSE)
手順５．HLOOKUP関数を確定
「Enter」キーで確定させると、HLOOKUP関数を挿入したシートに表示画面が戻ります。また、表の中から商品番号25の商品名「風邪薬」が抽出されます。
異なるブックのシートを指定する場合は、タスクバーのExcelのアイコンにマウスのカーソルを合わせてください。(図①)
そうすると、起動中のExcelブックが表示されるので、対象のExcelブックをマウス操作で選択(図➁）して検索範囲に使用するエクセルブックを切り替えて下さい。
HLOOKUP関数の複数条件の使い方
検索範囲内に検索値が複数存在する場合、HLOOKUP関数は一番左の値を返します。
そのため、下の表のような場合、検索値「25」で商品名を抽出すると、左側の「風邪薬」が抽出されます。
それでは、右側の検索値「25」の商品名「消臭剤」を抽出するためにはどうすればよいでしょうか。
検索値が複数ある場合は、下の２つの手順を実施するだけで複数条件に対応出来ます。
- 表にそれぞれの条件を結合したHLOOKUP関数用のキー行を挿入する
- HLOOKUP関数の1つ目の引数「検索値」に「＆」演算子を利用する
表内の4行目に「商品番号」と「商品カテゴリ」の文字列を結合した「検索キー」行を挿入し、HLOOKUP関数の第１引数も「商品番号」と「商品カテゴリ」を「＆」演算子を用いて結合します。
そして、以下のように関数を利用することで、右側の検索値「25」の商品名「消臭剤」を抽出することが出来ます。
このHLOOKUP関数の複数条件の使い方の詳しい手順は以下の記事で詳しく紹介していますので、参考にしてみてください。
[検索方法]を「TRUE」で利用する場合
HLOOKUP関数の第４引数「検索方法」を「TRUE」で利用した場合、「検索値」と完全一致する値が検索され、「検索値」が見つからない場合には、近似値が表示（「検索値」未満で最も大きい値）されます。
この見出しは「TRUE」を使った事例を具体的に１つ紹介します。
下の表から商品番号「24」の商品名を抽出しようとします。しかし、表には商品番号「24」は存在しません。
このように検索値が検索範囲から見つからない場合は、検索値未満で最大の値である商品番号「18」が近似の検索値として利用されるので、商品名「ヘアワックス」が抽出されます。
=HLOOKUP(B9,$B$2:$J$5,3,TRUE)
このように、「FALSE」と「TRUE」の違いは、検索値が検索範囲に無かった場合に限り抽出結果が異なります。
そのため、検索値が検索範囲に存在していた場合は、FALSEでもTRUEでも計算結果は同じです。
HLOOKUP関数の上級テクを紹介
HLOOKUP関数に関連する３つの関連記事を紹介します。
関数に使い慣れてきたら、ぜひこの３つのテクニックも覚えてみて下さい。
HLOOKUP関数とIF関数を組み合わせる
HLOOKUP関数とIF関数と組み合わせることで、『表から情報を抽出し、その抽出値を条件によって別の値を変換する』ことが出来るようになります。
具体的な実例を１つご紹介します。
下の表には商品番号毎の販売高が入力されています。
セルC10にIF関数とHLOOKUP関数を組み合わせて利用することで、セルB10の商品番号の販売高が600(千円）を越える場合は、”○”を、超えない場合は”×”と表示させることが出来ます。
=IF(HLOOKUP(B10,$B$3:$I$5,3,FALSE)>=600,"○","×")
IF関数と組み合わた使い方は、以下の記事で詳しく紹介していますので、興味のある方は参考にしてみてください。
HLOOKUP関数を行番号の変更を自動にする方法
HLOOKUP関数を複数のセルでも利用する際、引数「行番号」をいちいち切り替えるのは面倒です。
そんな時、「行番号」が自動で切り替わる時短テクニックが２つあります。
下のリンク記事で使い方を紹介していますので、ぜひ参考にしてみてください。
HLOOKUP関数の進化版「XLOOKUP関数」
XLOOKUP関数という関数をご存じでしょうか。
HLOOKUP関数やVLOOKUP関数を合体した後継関数として認知度が高まっています。
HLOOKUP関数は、慣れている人でも行番号の入力やエラー表示の解消など不便さを感じる方は多いかと思います。
XLOOKUP関数はそんな不便さを解消して誕生しました。
この関数が使えるExcelのバージョンは、Microsoft365とOffice2021です。
利便性がかなりアップしていますので、利用出来るバージョンであればHLOOKUP関数とはお別れしましょう！
