月別の目標表や商品マスタなど、項目が横に並んだ表をスプレッドシートで受け取ることはありませんか。VLOOKUPで引こうとしても、縦向きの表を前提にした関数なので思うようにいきません。
そこで使うのがHLOOKUP関数です。ただし行番号を1つ数え間違えたり、第4引数を省略したりすると、エラーにならず別の値が返ってきます。気づかないまま報告資料に載せてしまうのが一番怖いところ。
この記事では、HLOOKUPの引数と行番号の数え方、#N/Aの切り分け方を順に説明します。例には架空の商品表と月別表を使います。今から表を作る場合に、XLOOKUPやINDEX+MATCHを選ぶ判断材料もまとめました。
スプレッドシートのHLOOKUPは横向きの表から値を引く関数
HLOOKUP関数（読み方: エイチルックアップ）は、スプレッドシートで横向きの表から値を取り出す関数です。範囲の先頭行を左から右へ探し、見つかった列の中から指定した行の値を返します。
名前の由来はHorizontal（水平）とLookup（検索）。「商品コードから単価を引く」「月から目標値を引く」といった横方向の表引きを、数式1つで済ませられます。
縦の表ならVLOOKUP、横の表ならHLOOKUP
VLOOKUP関数とHLOOKUPは、探す向きが違うだけの兄弟のような関数です。VLOOKUPは左端の列を上から下へ探し、HLOOKUPは先頭の行を左から右へ探します。
| 項目 | VLOOKUP | HLOOKUP |
|---|---|---|
| 探す場所 | 範囲の左端の列 | 範囲の先頭行 |
| 探す向き | 上から下（縦） | 左から右（横） |
| 返す値の指定 | 左端から何列目か | 先頭行から何行目か |
検索に使う値が縦に並ぶ表ならVLOOKUP、横に並ぶ表ならHLOOKUPと覚えておけば迷いません。月が横に並ぶ予算表や、商品が横に並ぶマスタはHLOOKUPの出番です。
この記事で使う表のイメージ
ここからは、次の商品表を例に進めます。データはすべて架空のものです。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品コード | P-101 | P-102 | P-103 | P-104 |
| 2 | 商品名 | ボールペン | ノート | 付箋 | クリップ |
| 3 | 単価 | 120 | 180 | 250 | 90 |
| 4 | 在庫 | 340 | 125 | 80 | 500 |
たとえば「P-103の単価は？」と聞かれた場面を考えてみましょう。先頭行でP-103を探し、その列を3行目まで降りて250を読むはずです。HLOOKUPは、この目で追う作業をそのまま数式にした関数といえます。
HLOOKUPの構文と引数｜番号は範囲の先頭行を1と数える
HLOOKUPの構文は次のとおりです。
=HLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])Googleの公式ヘルプでの引数名は「検索キー」「範囲」「番号」「並べ替え済み」の4つ。最後の並べ替え済みだけは省略できます。
4つの引数
| 引数 | 必須/省略可 | 意味 | 例 |
|---|---|---|---|
| 検索キー | 必須 | 範囲の先頭行から探す値 | “P-103” |
| 範囲 | 必須 | 検索する表。先頭行に検索キーを含む | B1:E4 |
| 番号 | 必須 | 範囲の先頭行を1として何行目を返すか | 3 |
| 並べ替え済み | 省略可 | FALSE＝完全一致／TRUE＝近似一致（省略時はTRUE） | FALSE |
番号はシートの行番号ではなく、範囲の中での位置です。範囲をB1から選べばシートの1行目が1、B2から選べばシートの2行目が1になります。
商品表で単価を引くなら、こう書きましょう。
=HLOOKUP("P-103", B1:E4, 3, FALSE)結果は250です。先頭行の「P-103」を探し、範囲の3行目（単価の行）の値を返しています。
第4引数を省略すると近似一致（TRUE）になる
つまずきやすいのが第4引数の並べ替え済みです。省略するとTRUE（近似一致）として扱われ、検索キー以下で最も近い値の列が使われます。
完全一致で探したいのに省略すると、どうなるでしょうか。商品表に存在しない「P-105」を探す例で比べてみます。
=HLOOKUP("P-105", B1:E4, 3) → 90（P-104の単価）
=HLOOKUP("P-105", B1:E4, 3, FALSE) → #N/A省略した式は、P-105がないのにエラーになりません。P-105以下で最も近いP-104の列が選ばれ、別の商品の単価90が返るためです。登録漏れのコードがあっても気づけないので、完全一致で探すときはFALSEを必ず書くと決めておきましょう。
公式ヘルプには、近似一致で先頭行が並べ替えられていないと間違った値が返ることがある、とも書かれています。先頭行の値がすべて検索キーより大きい場合は#N/Aです。近似一致は、点数帯のように先頭行を昇順に並べた表で使うものと考えてください。
番号で間違えやすい3パターン
同じ商品表を使い、番号のミスが起きやすい場面を正誤で並べます。
パターン1: 範囲を2行目から選んだ
商品名で探すために、範囲を商品名の行（B2:E4）から選んだケースです。範囲の先頭が変わると、単価の行は3ではなく2になります。
誤: =HLOOKUP("付箋", B2:E4, 3, FALSE) → 80（在庫が返る）
正: =HLOOKUP("付箋", B2:E4, 2, FALSE) → 250シートの行番号（単価は3行目）につられて3と書くと、在庫の値が返ってしまいます。番号は範囲を選び直すたびに数え直してください。
パターン2: 表の途中に行を挿入した
商品名と単価の間に「仕入先」の行を足したとします。範囲の参照はB1:E5に広がりますが、数式の番号3は数字のまま変わりません。
その結果、単価ではなく仕入先の値が返ります。行の増減に強くしたいなら、番号をMATCH関数で求める方法が便利です。
=HLOOKUP("P-103", B1:E5, MATCH("単価", A1:A5, 0), FALSE)MATCHがA列から「単価」の位置（挿入後は4）を探すので、行を足しても単価の行を追いかけます。HLOOKUPの範囲とMATCHの範囲は、開始行（ここでは1行目）をそろえるのがコツ。MATCHの使い方はMATCH関数の記事で解説しています。
パターン3: 検索したい値が範囲の先頭行にない
範囲B1:E4のまま、商品名「付箋」で探すとどうなるでしょうか。HLOOKUPが探すのは先頭行（商品コードの行）だけなので、結果は#N/Aです。
誤: =HLOOKUP("付箋", B1:E4, 3, FALSE) → #N/A
正: =HLOOKUP("付箋", B2:E4, 2, FALSE) → 250検索に使う行が先頭に来るよう範囲を選び直せば解決します。ただし、単価から商品コードを引くように返したい行が検索する行より上にある場合は、HLOOKUPでは引けません。後半で紹介するXLOOKUPかINDEX+MATCHの出番です。
HLOOKUPの基本の使い方と実務例
商品コードから単価を引く
実務では、検索キーを数式に直接書かずセル参照にするのが基本です。G1に商品コードを入力し、G2に単価を出す形にしてみましょう。
=HLOOKUP(G1, B1:E4, 3, FALSE)G1に「P-103」と入れれば250、「P-101」に変えれば120が返ります。番号を4にすれば、在庫数（P-103なら80）も引けます。
数式を下や右へコピーするなら、範囲は絶対参照（$B$1:$E$4）にしておくと安心です。コピー先で範囲がずれて、#N/Aや別の値が返るのを防げます。
月から目標値と実績を引く
次の例は月別の目標・実績表。月が横に並ぶ表は、HLOOKUPがいちばん活躍する形といえます。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 | |
| 2 | 目標 | 500 | 520 | 480 | 550 | 450 | 600 |
| 3 | 実績 | 512 | 498 | 503 | 561 | 430 | 615 |
I1に月を入力し、目標と実績を引き出します。
目標: =HLOOKUP(I1, B1:G3, 2, FALSE)
実績: =HLOOKUP(I1, B1:G3, 3, FALSE)I1が「7月」なら、目標は550、実績は561です。2つを割り算すれば、達成率も1つのセルで出せます。
=HLOOKUP(I1, B1:G3, 3, FALSE) / HLOOKUP(I1, B1:G3, 2, FALSE)7月は561÷550で1.02になり、表示形式をパーセントにすれば102%と表示されます。I1の月を切り替えるだけで、どの月の達成率もすぐ確かめられるのが便利なところ。
点数から評価ランクを引く（近似一致）
第4引数をTRUEにする近似一致は、点数や金額を「帯」で判定するときに使います。先頭行に、各ランクの下限値を昇順で並べておくのが条件。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 点数 | 0 | 60 | 75 | 90 |
| 2 | 評価 | D | C | B | A |
=HLOOKUP(82, B1:E2, 2, TRUE)82以下で最も近い値は75なので、結果は「B」です。45なら0の列が選ばれて「D」、90ちょうどなら「A」になります。
先頭行の最小値を0にしておけば、0点以上はどれかの帯に必ず入ります。先頭行のどの値よりも小さい検索キー（たとえば-5）を渡すと、該当する列がなく#N/Aです。
別シートの横表を参照する
マスタが別シートにある場合は、範囲の前に「シート名!」を付けます。
=HLOOKUP("P-103", 商品マスタ!B1:E4, 3, FALSE)
=HLOOKUP("P-103", '商品 マスタ'!B1:E4, 3, FALSE)シート名に空白を含むときは、2行目のようにシングルクォーテーションで囲みましょう。数式の入力中に別シートへ移って範囲を選ぶと、シート名が自動で入るので打ち間違いを防げます。
HLOOKUPの#N/Aなどエラーの切り分け早見表
症状から原因を探す
HLOOKUPの#N/Aは、検索キーが先頭行で見つからないという意味です。見た目は同じでも原因はいくつかあるので、次の表で当てはまるものを探してください。
| 症状 | 考えられる原因 | 確認と対処 |
|---|---|---|
| 表にある値なのに#N/A | 検索キーが範囲の先頭行にない | 検索に使う行が先頭に来るよう範囲を選び直す |
| 同じ文字に見えるのに#N/A | 前後に空白が混じっている | TRIM関数で空白を取り除く |
| 数字のコードで#N/A | 数値と文字列が一致していない | VALUE関数で数値にそろえる、または検索キー&””で文字列にそろえる |
| 近似一致で#N/A | 先頭行のすべての値が検索キーより大きい | 先頭行の最初に最小値（0など）を置く |
| エラーにならず別の値が返る | 第4引数の省略、番号の数え間違い、近似一致で先頭行が昇順でない | FALSEを明示する／番号を範囲の先頭行から数え直す／先頭行を昇順に並べる |
| 存在しない値で#N/A | 登録漏れ・入力ミス | 元データを確認する |
空白の混入は、他のシステムから貼り付けたデータで起こりがちです。見た目では区別できないため、空白がどこにあるか確かめたいならFIND関数で「” “」の位置を探す手もあります。
数値と文字列の不一致が起こるのは、先頭行の「101」が文字列で、検索キーが数値といった組み合わせ。公式ヘルプも、数値や日付を探すときは先頭行がテキストになっていないよう注意を促しています。
番号が範囲外だと#VALUE!になる
番号が1から範囲の行数までに収まっていないと、HLOOKUPは#VALUE!を返します。範囲が4行（B1:E4）なのに番号を5にした場合や、番号に0を入れた場合が当てはまります。
=HLOOKUP("P-103", B1:E4, 5, FALSE) → #VALUE!
=HLOOKUP("P-103", B1:E4, 0, FALSE) → #VALUE!Googleスプレッドシートでは、この場面のエラーは#REF!ではなく#VALUE!です。行を削除して範囲が縮んだときにも起こりやすいので、#VALUE!が出たら番号と範囲の行数を見比べてみてください。
IFERRORで隠す前に確認したいこと
#N/Aを「該当なし」などに置き換えたいときは、IFERROR関数で囲む方法がよく使われます。
=IFERROR(HLOOKUP(G1, B1:E4, 3, FALSE), "該当なし")ただしIFERRORは#N/Aだけでなく、番号ミスによる#VALUE!も同じように隠してしまいます。数式が完成するまでは囲まずにエラーの種類を確かめ、原因をつぶしてから最後に付けるのが安全です。使い方の詳細はIFERROR関数の記事をどうぞ。
HLOOKUPとVLOOKUP・XLOOKUP・INDEX+MATCHの使い分け
HLOOKUPは横の表を読むには便利ですが、いつでも最善とは限りません。ここでは、どの関数を選ぶかの判断材料を整理します。
状況別の判断表
| 状況 | 候補 | 理由 |
|---|---|---|
| 受け取った横表を読むだけ | HLOOKUP | 表の形を変えずにすぐ書ける |
| 縦向きの表から引く | VLOOKUP | 左端の列で探す関数 |
| 検索する行が先頭にない・返したい行が上にある | XLOOKUP | 検索範囲と結果範囲を別々に指定できる |
| 行の挿入や削除が多い表 | INDEX+MATCH | 番号を手で数えずに位置を求められる |
| 条件に合う値を複数件まとめて取り出す | FILTER・QUERY | 1件ではなく一覧で返せる |
判断の軸は「表をもう作り終えているか」。人から受け取った横表や、形を変えられない帳票なら、HLOOKUPがいちばん手早い選択になります。
今から作る表なら縦に組むか、XLOOKUP・INDEX+MATCHへ
これから自分で表を作るなら、項目を縦に並べる形がおすすめです。データは行を足しながら増えていくことが多く、縦の表のほうが後から扱いやすいからです。VLOOKUPはもちろん、FILTERやQUERYでの抽出にもつなげやすいでしょう。
横の表のまま使う場合でも、XLOOKUP関数は検索範囲に横1行を指定できます。検索範囲と結果範囲を別々に選ぶので、先頭行以外の行で探すことも可能です。引数の詳しい使い方は、XLOOKUPの記事にまとめています。
INDEX関数とMATCH関数を組み合わせる方法も定番。MATCHで位置を求め、INDEXでその位置の値を取り出すので、番号を手で数える必要がありません。
=INDEX(B3:E3, MATCH("付箋", B2:E2, 0))商品名の行（B2:E2）で「付箋」の位置（3番目）を求めます。そのうえで、単価の行（B3:E3）の3番目にある250を返す仕組みです。単価から商品名を逆に引くような、HLOOKUPでは書けない向きにも対応できるのが強みです。
そのほかの代替手段
- 横表を縦向きにしたいなら、TRANSPOSE関数で行と列を入れ替えてVLOOKUPで引く方法があります
- 先頭行が昇順に並んだ表ならLOOKUP関数も選択肢です。並べ替えていないデータでは、公式もVLOOKUPやHLOOKUPを案内しています
- 条件に合う値をすべて取り出したいときは、FILTER関数が候補
- 複数の条件で抽出や集計をしたいなら、QUERY関数を検討してみてください
HLOOKUPのよくある質問とまとめ
第4引数は省略してよい？
完全一致で探すなら、省略せずFALSEを書いてください。省略するとTRUE（近似一致）になり、存在しない検索キーでも近い値の列が返ることがあります。TRUEを使うのは、評価ランクのように先頭行を昇順に並べた表で帯を判定するときだけです。
2行以上を一度に引くには？
いちばん確実なのは、番号だけを変えたHLOOKUPを行ごとに書く方法です。月別表なら、目標用に番号2、実績用に番号3の式を並べます。
取り出したい行が多いときや、条件で絞り込みたいときは別の関数も考えましょう。INDEX+MATCHやFILTER、QUERYのほうが扱いやすい場面もあります。
Excelとの違いは？
横表の先頭行で探し、指定した行の値を返すという役割はExcelのHLOOKUPと共通です。この記事で使った引数名は、Googleスプレッドシートの公式ヘルプの表記です。Excelでの引数名やエラーの出方は、ExcelのHLOOKUP関数の解説で確認してください。
まとめ
最後に、HLOOKUPのポイントを振り返ります。
- HLOOKUPは、範囲の先頭行を横に探して、指定した行の値を返す関数
- 番号はシートの行番号ではなく、範囲の先頭行を1として数える
- 完全一致で探すときは第4引数にFALSEを書く（省略するとTRUE）
- #N/Aは「先頭行にない・空白・数値と文字列・近似一致の条件」の順に疑う
- 番号が範囲外なら#VALUE!。IFERRORで囲むのは原因をつぶしてから
- 今から作る表なら縦に組むか、XLOOKUP・INDEX+MATCHも検討する
まずは手元の横表で、FALSEを付けた基本の式を1本書いてみてください。番号を範囲の中で数える感覚がつかめれば、横の表からの転記はぐっと楽になるはずです。
