スプレッドシートのHLOOKUP関数の使い方や注意点を解説!列方向の値参照で利用

「関数のVLOOKUPとHLOOKUPの違いがわからない。」スプレッドシートで数式を組む際、この2つの関数は便利な一方、使い方が似ているので非常に間違いやすいです。

いずれの関数も指定の条件を入力することで、自動的に参照したいデータが返されるという点では同じですが、行方向か列方向かの違いがあります。

VLOOKUP関数では対応が難しいケースにHLOOKUP関数を使うことで、以下3点がメリットとなります。

  • 手入力をするよりも間違いが減ること
  • 自動化できるため手間が減ること
  • 価格改定があってもすぐに反映できること

今回はスプレッドシートのHLOOKUP関数について、使い方や注意点、VLOOKUPとの違いも含めてわかりやすく解説します。

スプレッドシートで条件に一致したデータを参照

早速HLOOKUP関数での参照方法を見ていきます。

例えば次のような見積書と商品マスタがあると仮定します。

スプレッドシートでHLOOKUP関数が使える表のサンプル

HLOOKUP関数が使えると、見積書の商品コードを入力するだけで、自動的に単価が反映されます。

  • 商品コードと単価の両方を入力する手間がなくなる
  • 間違いを減らすことができる

HLOOKUPを使用することで上記のようなメリットがあります。

見積書や請求などの内容に誤りがあるものの場合、金額が間違うことの影響が大きいです。

HLOOKUPのような参照関数を活用することで、スプレッドシートの正確性を高めることができます。

スプレッドシートのHLOOKUPの引数を解説

HLOOKUP関数は、次の4つの引数からできています。

※引数とは関数に入力する値のことを指します。

=HLOOKUP(検索キー、範囲、指数、[並び替え済み])

それぞれの引数を、見積書の単価(B3セル)を出す場合を想定して説明します。

スプレッドシートでHLOOKUP関数が使える表のサンプル

引数詳細
検索キー検索する「値」を示します。
上図の見積書のA3セルに入力する商品コードを指します。
範囲上記の指定キーを検索する「範囲」を示します。
上図では、商品マスタの10行目と11行目を指します。
指数指定した範囲の中から、取得する「行の値」を示します。
上図では商品マスタ内の単価を出したいので、2行目を指します。
並び替え済み近似値か完全一致かを示します。
近似値の場合は、”1”もしくは”True”、それ以外は0 or Falseを指定します。

※HLOOKUP関数の4番目の引数は必須ではなくオプションのため、省略可能です。

HLOOKUP関数の使い方と使用例

実際にHLOOKUP関数を使って見積書の単価を算出する手順をご紹介します。

1.商品コードの入力

商品マスタから商品コードを選び、A3~A5セルに転記します。

2.単価の列にHLOOKUP関数を入力

表1行目のB3セルには次のように入力します。

=HLOOKUP(A3,10:11,2,0)

スプレッドシートでHLOOKUP関数を入力している例

3.HLOOKUP関数を入力後、Enterキーを押すと次のとおりにデータが算出されます。

スプレッドシートのHLOOKUP関数で、第一引数を検索キーにして、合致した列の特定行のセルの値を参照

B3セルに”2,000”と表示されました。

ここでは次のような指示を与えています。

「A3セルの値(商品コード=A001)を10,11行目(商品マスタ)から検索して、一致する値の2列目(単価)を参照せよ」

この結果、A0001の検索キーにB10セルが合致し、2行目のB11セルに入力されている”2,000”が返されたことになります。

4.残りの商品コードの単価を算出(注意点あり)

B3セルの数式をコピーして貼り付ければ単価の参照は完成…と思ったらB4セル以降にエラーが表示されました。

スプレッドシートのHLOOKUP関数は行方向にオートフィルすると、N/Aのエラーが表示されるケースも

エラーの式を見てみると、範囲が1行ずつ下にずれていることがわかります。

(関数の範囲が11行目と12行目に変わっています。)

HLOOKUPを行方向にオートフィルすると、検索範囲がずれるため、検索キーが見つからずエラー

B3セルの関数をコピーする前に、絶対参照(指定する範囲を変えないという指示)をすることで解決できます。

※範囲の10、11についている「$」が絶対参照です。F4キーでショートカットもできます。

スプレッドシートのHLOOKUPを行方向にオートフィルした際のエラーは第2引数の検索範囲を$(ドル)マークの絶対参照の指定で解消

5.数量と小計の算出

最後に数量を入れ、小計には単価×数量の数式を予め設定することで、必要最小限の入力で見積書が完成します。

HLOOKUP関数を使う場合は、入力が必要な箇所がわかるように、色掛けするのがオススメ

※上図のように手入力が必要なセルだけを黄色にすることで、他の人とシートを共有した際にもわかりやすくなります。

※価格改定などがあった際は、マスタを変更するだけで見積書にも反映されます。

HLOOKUP関数とVLOOKUP関数の違い

前述した説明の通り、HLOOKUP関数はVLOOKUP関数と引数の指定数や内容も類似しています。

それぞれどのような違いがあるのでしょうか。

HLOOKUPとVLOOKUPの異なる点は、データを検索できる方向です。

HLOOKUP関数は先に述べたような商品マスタのデータ(レコード)が、横(列)方向(A→B→C…の順)に並んでいる際に検索が可能です。

一方でVLOOKUPは商品マスタのレコードが縦(行)方向(1→2→3‥の順)に並んでいる場合に検索が可能な関数です。

関数検索の方向
HLOOKUP横(列)方向のみ
VLOOKUP縦(行)方向のみ

それぞれの頭文字のHとVは、Horizontal(水平の)とVertical(垂直の)を意味しています。

検索する方向を忘れてしまった場合は、「HLOOKUP(水平)=横=列、VLOOKUP(垂直)=縦=行」と理解しておけば、簡単に思い出すこともできます。

スプレッドシートのVLOOKUPの使い方や注意点についての解説記事は以下をお読みください。

スプレッドシートのVLOOKUP関数の利用方法と注意点を解説!見積書や請求書に便利

HLOOKUPとVLOOKUP関数の使い分けの方法

HLOOKUPとVLOOKUPは使い方が似ている関数ですが、まず最初に覚えるべきは、VLOOKUPです。

それは一般的に企業などで使われるデータは、列方向(横)に項目があり、行方向(縦)にデータ(レコード)が並ぶことが多いからです。

(例)VLOOKUP関数が活用できるデータの並び

HLOOKUPではなく、VLOOKUPの参照関数が適している行方向にデータが追加されるスプレッドシートの表

スプレッドシートの表には列方向に商品コード~在庫、単価といった項目が並んでおり、行方向にデータが追加されています。

このような表の場合には参照関数としてVLOOKUP関数が推奨です。

一方で、先程の例に上げたような行方向(縦)に項目があり、列方向(横)にレコードが並ぶ際は、HLOOKUP関数が有効です。

(例)HLOOKUP関数が活用できるデータの並び

VLOOKUPではなく、HLOOKUPの参照関数が適しているスプレッドシートの列方向にデータが追加される表

先程の表と異なり、データは列方向に追加されていく形式のスプレッドシートの表はHLOOKUPが適しています。

使用するデータのフォーマットに合わせて、HLOOKUPとVLOOKUPを使い分けましょう。

まとめ・終わりに

今回、スプレッドシートの参照関数であるHLOOKUP関数について紹介しました。

HLOOKUP関数は見積書や請求書を作成する際に便利です。

入力する項目を必要最小限に抑えることができ、入力ミスやコピーの手間が減るからです。

今回解説したように、HLOOKUPのような参照関数を使うことで以下のメリットがあります。

  1. 商品コードだけで単価を出せること
  2. 必要最小限の入力で小計を出せること
  3. 価格改定時も、商品マスタの変更だけで完結すること

ただデータの並び方によっては、VLOOKUPとHLOOKUPのどちらを使うべきかの判断する必要があります。

VLOOKUP関数は一般的なデータが行方向に追加される表で利用するのに対し、列方向にデータを追加する表ではHLOOKUP関数を使用します。

そのため、まずVLOOKUPを使う想定として、表の形式によってはHLOOKUPに切り替えるという形で使い分けましょう。