HLOOKUP関数の使い方｜横方向の表から値を検索する

HLOOKUPは、横方向に並んだ表から値を探す関数です。VLOOKUP の縦横が入れ替わったもの、と考えるのが一番わかりやすいです。

H は Horizontal（水平）の頭文字です。

書式

=HLOOKUP(検索キー, 範囲, 行番号, [検索の型])

引数	内容
検索キー	探したい値
範囲	探しに行く表。一番上の行が検索対象になる
行番号	範囲の上端を1として、取り出したい行が何番目か
検索の型	`FALSE`＝完全一致 / `TRUE`＝近似一致

VLOOKUPが「左端の列を検索して、右へ数える」のに対し、HLOOKUPは「上端の行を検索して、下へ数える」動きになります。

基本の使い方

月ごとの数字が横に並んだ、よくある集計表です。

	A	B	C	D
1	項目	4月	5月	6月
2	売上	1200	1450	1380
3	原価	700	820	790
4	利益	500	630	590

「5月の利益」を取り出します。

=HLOOKUP("5月", A1:D4, 4, FALSE)

結果：630

1行目から「5月」を探し、見つかった列（C列）の4行目を返しています。

実務での使いどころ

月次・年次の推移表は横に伸びる形が多いため、「月を指定して、その月の数字を引く」用途でよく使います。

=HLOOKUP($B$1, $A$1:$M$10, 3, FALSE)

B1セルに月を入れると、その月の数字が出てくるダッシュボードのような作りにできます。範囲は絶対参照にしておいてください。

行番号を MATCH で動的に求めると、項目名も指定できるようになります。

=HLOOKUP($B$1, $A$1:$M$10, MATCH($A$5, $A$1:$A$10, 0), FALSE)

使わないほうがいい場面

HLOOKUPには、VLOOKUPと同じ弱点がそのまま残っています。

行番号が「上から何番目」という位置指定なので、行を挿入すると壊れる
検索できるのは範囲の一番上の行だけ
見つからないときの値を指定できない

これらは XLOOKUP で全部解決します。XLOOKUPは検索範囲を「行」で指定すれば横方向の検索になるため、HLOOKUPの上位互換として使えます。

=XLOOKUP("5月", B1:D1, B4:D4)   → 630

行を挿入しても壊れず、読んでも意味が分かりやすい。新しく作るならこちらを推奨します。

そもそも表の形を見直す

横に長い表は、集計・並べ替え・QUERY による抽出のすべてで扱いにくくなります。データを蓄積する目的なら、縦持ち（1行1レコード）に直すほうが後が楽です。

月	項目	金額
4月	売上	1200
4月	原価	700
5月	売上	1450

この形にしておけば、SUMIFS や QUERY で自由に切り出せます。HLOOKUPが必要になった時点で、表の設計を疑ってみる価値はあります。

エラーの対処

#N/A が出る

一番上の行に検索キーが存在しません。「5月」と「5月」（末尾スペース）、「5月」と「05月」のような表記ゆれが原因のことが多いです。

#REF! が出る

行番号が範囲の行数を超えています。範囲が4行なのに行番号5を指定した、というケースです。

横方向の表が生まれる理由と、その代償

HLOOKUPは横並びの表から値を検索する関数です。使い方はVLOOKUPと同じ考え方で、方向が違うだけです。しかし実務で使う機会は多くありません。理由は、横方向の表そのものが扱いにくいためです。

横方向の表が生まれるのは、たいてい印刷や見た目の都合です。月を横に並べた予算表、項目を横に並べた比較表。人が読むには分かりやすい形ですが、データとしては扱いにくい構造をしています。

扱いにくさは具体的に三つあります。第一に、データが増える方向が右になるため、列を追加し続けることになります。列数には上限があり、また横スクロールが必要になって閲覧性が落ちます。第二に、並べ替えや絞り込みといった機能は縦方向のデータを前提としているため、そのままでは使えません。第三に、集計関数の多くも縦方向を想定した書き方が自然で、横方向だと数式が読みにくくなります。

したがって、HLOOKUPを書く前に一度立ち止まる価値があります。その表は本当に横並びである必要があるのか。縦に持ち替えられないか。持ち替えられるなら、以降の作業がすべて楽になります。

縦に持ち替える方法

横方向の表を縦方向に変える方法はいくつかあります。

もっとも手軽なのは、行と列を入れ替える関数を使う方法です。範囲を指定するだけで、縦横が入れ替わった結果が返ります。元データと連動するので、更新も自動です。→ TRANSPOSE関数の使い方

一度きりの変換でよければ、コピーしてから行列を入れ替えて貼り付ける機能があります。数式ではなく値として固定されるので、元データが変わっても追従しません。

構造的に持ち替えたい場合は、データの持ち方そのものを設計し直すことになります。月を横に並べるのではなく、日付の列を一本持たせて縦に積む形です。この形にすると、集計も絞り込みも並べ替えも標準の機能で扱えるようになります。

見た目として横並びが必要なら、縦に持ったデータから集計表を生成すればよいのです。元データは縦、表示は横、という分離ができると、両方の利点が得られます。

それでも横方向で扱う場合

構造を変えられない場面もあります。他部署から受け取る決まった形式、システムから出力される固定のレイアウト。こうした場合はHLOOKUPで対応することになります。

書き方はVLOOKUPと同じ考え方です。検索する行が範囲の最上段にある必要があり、そこから何行目を返すかを数字で指定します。VLOOKUPで列番号を数えていたところが、行番号に変わるだけです。

事故の起き方も同じです。範囲を絶対参照で固定しないと、コピーしたときにずれます。行番号を固定の数字で書いていると、行を挿入したときに参照先が変わります。完全一致の指定を省略すると、近似一致になって意図しない値が返ります。

より堅牢に書くなら、行番号を検索関数で求める方法があります。見出しの名前から位置を求めれば、行を挿入しても壊れません。

新しい検索関数が使える環境なら、そちらのほうが安全です。縦横を問わず同じ書き方で検索でき、範囲の方向さえ揃っていれば動きます。→ XLOOKUP関数の使い方

検索できないときの確認

値が見つからない場合の原因は、縦方向の検索とまったく同じです。順に確かめてください。

型の違いが第一です。見た目が同じでも、片方が文字列で片方が数値なら一致しません。表示の寄せ方が手がかりになります。

前後の空白が第二です。末尾に半角スペースが入っているだけで一致しません。文字数を数えれば分かります。

範囲の指定が第三です。検索する行が範囲の最上段になっているか、範囲が絶対参照で固定されているかを確認してください。

行番号が第四です。範囲の行数を超えていないか、範囲を広げたときに番号を直し忘れていないかを見てください。

完全一致の指定が第五です。省略していると近似一致になります。エラーは出ず、もっともらしい値が返るため、もっとも発見が遅れる原因です。

この五つを順に潰せば、原因は特定できます。

縦持ちに変えたあとの集計

横方向の表を縦に持ち替えると、集計の書き方が一変します。どう変わるかを具体的に見ておくと、持ち替える価値が分かります。

横持ちのままでは、月ごとの合計を出すのに列ごとの合計を並べることになります。月が増えるたびに数式を足す必要があります。特定の月だけを抜き出すには、列の位置を求める処理が要ります。

縦持ちにすると、月は列の値になります。条件付きの集計関数で、月を条件に指定するだけで済みます。月が増えても数式は変わりません。

期間で絞る場合も同様です。横持ちでは複数の列を足し合わせることになりますが、縦持ちなら開始日と終了日の条件を指定するだけです。

商品分類と月のクロス集計を作る場合も、縦持ちのほうが単純になります。行に分類、列に月を並べ、条件付きの集計関数を一つ書いてコピーするだけです。

さらに、並べ替えも絞り込みも標準の機能が使えます。横持ちではこれらが使えません。

持ち替える手間は一度きりですが、以降の作業がすべて楽になります。長く使う表であれば、間違いなく元が取れます。

受け取る形式を変えられない場合

他部署やシステムから横持ちで受け取る場合、形式そのものは変えられないことがあります。この場合の対処を整理しておきます。

もっとも単純なのは、受け取ったシートをそのまま保持し、変換用のシートを別に作る方法です。受け取ったデータは触らず、変換後のデータを集計に使います。受け取り元の形式が変わらない限り、変換の仕組みはそのまま機能します。

変換には、行と列を入れ替える関数や、複数の範囲を一列にまとめる関数を使います。値だけでなく、月や項目の情報も同じ形で並べる必要があるため、手順はいくつかの段階に分かれます。

もうひとつの選択肢は、横持ちのまま集計する方法です。列の位置を求める処理を挟めば、特定の月を抜き出せます。ただし、月が増えるたびに範囲を広げる必要があります。

判断の目安は、その表を何回使うかです。一度きりなら横持ちのまま処理し、継続して使うなら変換の仕組みを作る。この基準で決めてください。

いずれの場合も、受け取ったままのデータは必ず残しておいてください。変換に誤りが見つかったときに、元に戻れます。

横持ちのデータをそのまま扱わない

HLOOKUPが必要になる場面の多くは、データが横方向に並んでいるためです。ただし、横持ちのまま集計を続けると、後で必ず行き詰まります。

列が増え続けます。 月ごとに列を足していく形は、数式の範囲を毎回広げる作業が発生します。

条件を足せません。 複数条件で絞り込む関数は、縦持ちを前提に作られています。

並べ替えができません。 横方向の並べ替えは、操作が限られます。

したがって、可能であればTRANSPOSEで縦に変換してから扱ってください。 一度縦持ちにすれば、VLOOKUPやXLOOKUP、FILTERといった扱いやすい関数が使えます。

元データを直接変換できない場合は、別シートに縦持ちの作業表を作ってください。 元データはそのまま残し、集計は作業表側で行う形です。

HLOOKUPを使うのは、横持ちの表を変更できない場合に限定してください。 使えないわけではありませんが、選択肢としては最後に来ます。

よくある質問

Q. VLOOKUPとどちらを使うべきですか

データが縦に並んでいるならVLOOKUP、横に並んでいるならHLOOKUPです。ただし、そもそも縦に持ち替えられないかを先に検討してください。

Q. 上方向に検索できますか

できません。検索する行は範囲の最上段である必要があります。上方向に検索したい場合は、INDEXと検索関数の組み合わせを使ってください。

Q. 行を挿入したら結果がずれました

行番号は固定の数値なので追従しません。検索関数で行番号を求める形に書き換えると壊れにくくなります。

Q. 見つからないときに空欄にしたいのですが

エラー回避の関数で包みます。ただし、本来見つかるはずのものまで隠れるので、動作確認が終わってから包んでください。

Q. 横方向のデータを集計したいのですが

集計関数は横方向の範囲も受け付けます。ただし、条件付きの集計になると書き方が煩雑になるため、縦に持ち替えるほうが楽です。

Q. 列数に上限はありますか

あります。横方向にデータを増やし続ける設計は、いずれ上限に達します。この点でも縦持ちが有利です。

Q. 印刷の都合で横並びにしています

元データを縦に持ち、印刷用の表を別に用意する構成にできます。行と列を入れ替える関数を使えば、表示用の表は自動生成できます。

Q. スプレッドシートでも同じですか

同じです。書き方も挙動も変わりません。

まとめ

書式は =HLOOKUP(検索キー, 範囲, 行番号, FALSE)
一番上の行を検索し、下へ数えて返す
VLOOKUPの縦横が入れ替わったもの
行の挿入で壊れる弱点があるので、可能なら XLOOKUP を使う