Excel(エクセル)のHLOOKUP関数(エイチルックアップ)は、表を横方向に検索して検索値を抽出する大変便利な関数です。
VLOOKUP関数(ブイルックアップ)が表を縦方向に検索して抽出する関数なので、その姉妹関数というイメージですね。
HLOOKUP関数の”H"は、「Horizon（水平、横）」の頭文字で、VLOOKUP関数の”V"は「Vertical（垂直）」の頭文字と覚えましょう。
今回は、HLOOKUP関数の使い方と、HLOOKUP関数に関する操作全般を基本から応用まで幅広く紹介します。
⇩VLOOKUP関数の使い方は下の記事からどうぞ
HLOOKUP関数は、横方向に検索して値を抽出する関数
HLOOKUP関数(エイチルックアップ)は、表を横方向に検索して情報を抽出する関数です。
HLOOKUPの「H」は横を意味する「Horizon」の頭文字で、LOOKUPは英語で「探す」という意味です。
指定した検索条件に『当てはまる値』を、指定した検索範囲から調べて、指定した位置から抽出することが出来る関数です。
もう少しかみ砕いて、具体的な下の図で説明すると、
商品番号が『２５』の商品名を、指定した商品リスト表から調べて、表の上から３行目の値を抽出することが出来る関数です。
この説明で理解出来なくても大丈夫です。
この後に紹介するHLOOKUP関数の使い方を見れば感覚的に理解出来るようになります。
いったん『表から条件に合った値を抽出することが出来る』と、理解していただいて結構です。
HLOOKUP関数の構文と引数
HLOOKUP関数の構文(書式）と引数を紹介します。
構文とは、関数によって利用する引数の数や種類のことを言います。関数の書式ともいいます。
引数とは、関数を使用するために指定するセル番号や数値、文字などのことをいいます。
HLOOKUP関数の構文(書式)
HLOOKUP関数の構文(書式)は下のとおりで、４つの引数「検索値」「検索範囲」「行番号」「検索方法」を指定する必要があります。
＝HLOOKUP(検索値,検索範囲,行番号,[検索方法]）
HLOOKUP関数の引数
HLOOKUP関数を利用するために必要な４つの引数の内容を紹介します。
第１引数「検索値」
見つけたい検索値を数値やセル番号、文字列で指定します。セルを指定せず、文字列を指定する場合は「”」で囲みます。（例：”薬”、B4）
第２引数「検索範囲」
検索するセル範囲を指定します。検索範囲を固定したければ、絶対参照の$(ドル）を付与します。（例：B4:E15、$B$4:$E$15）
第３引数「行番号」
第２引数「検索範囲」の一番上の行から何行目の値を抽出するかを数値で指定します。（例：３）
第４引数「検索方法」
検索方法を指定します。「FALSE(もしくは0）」か「TRUE(もしくは1）」のどちらかを選びますが、「FALSE」一択で覚えてください。「TRUE」を選択する場合は、省略可能です。（例：FALSE、TRUE、0、1） ※詳細は以下を参照
HLOOKUP関数の検索方法の「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関数が活躍します。
HLOOKUP関数を使う上での注意点
HLOOKUP関数を利用する上で、知っておくべき３つの注意点を紹介します。
注意点１：検索値が複数存在する場合
HLOOKUP関数を使う上での注意点その１は、検索範囲の中に検索値が２つ以上存在している場合です。
例えば、下の表には、商品番号「25」がF2セルとH2セルの２つ存在してます。
この場合、HLOOKUP関数で商品番号「25」の商品名を抽出しようとすると、一番左の値が優先されて、F4セルの商品名「風邪薬」が抽出されます。
そのため、右側のH4セルの商品名「消臭剤」を抽出しようとする場合は、HLOOKUP関数を複数条件で利用する必要があります。
複数条件での利用方法を使いたい場合は、下の「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」は表内に存在しているので一見問題なさそうに見えますが、、B9セルの書式設定(表示形式)が「文字列」であるため、HLOOKUP関数の抽出結果は「#N/A」です。
対処方法は、B9セルを右クリックし、右クリックメニューの「セルの書式設定」からセルの表示形式を「文字列」から「標準」に変更してください。
VLOOKUP関数のエラー値の発生原因を以下の記事で詳しく紹介しています。HLOOKUP関数でも同様に適用できますので、合わせて確認してください。
HLOOKUP関数で別のシートや別ブックの値を抽出する方法
HLOOKUP関数の検索値や検索範囲が異なるワークシートや異なるExcelブックに存在する場合でも、HLOOKUP関数は使用することができます。
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(B10&C10,$B$4:$I$6,2,FALSE)
HLOOKUP関数を３つ、４つの複数条件で使う方法を以下の記事で詳しく紹介していますので、以下を参考にしてみてください。
HLOOKUP関数の[検索方法]を「TRUE」で利用する方法
HLOOKUP関数の第４引数「検索方法」を「TRUE」で利用した際、「検索値」が見つからない場合には、近似値が表示（「検索値」未満で最も大きい値）されます。
HLOOKUP関数の[検索方法]を「TRUE」で利用した事例を紹介します。
下の表から商品番号「24」の商品名を抽出するため、HLOOKUP関数を挿入します。しかし、表には商品番号「24」は存在しません。
=HLOOKUP(B9,$B$2:$J$5,3,TRUE)
このように検索値が検索範囲から見つからない場合は、検索値未満で最大の値である商品番号「18」が近似の検索値として利用されるので、商品名「ヘアワックス」が抽出されます。
このように、「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関数を合体した後継関数として、XLOOKUP関数の認知度が高まっています。
HLOOKUP関数は、慣れている人でも行番号の入力やエラー表示の解消など不便さを感じる方は多いかと思います。
XLOOKUP関数はそんな不便さを解消して誕生しました。この関数が使えるExcelのバージョンは、Microsoft365とOffice2021です。
利便性がかなりアップしていますので、利用出来るバージョンであればHLOOKUP関数からXLOOKUP関数に移行していきましょう。
