更新日:、 作成日:

エクセル HLOOKUP 関数:検索して一致した列の下方向にある値を取得する

はじめに

エクセルの HLOOKUP 関数の使い方を紹介します。

HLOOKUP 関数は検索して一致した列の下方向にある値を取得します。好きな行から検索できるようにした上位互換の「XLOOKUP 関数」があります。

=HLOOKUP(100,A1:C2,2,FALSE) のようにして、100 を行「1」から検索して一致する列の 2 行目の値を取得できます。

=HLOOKUP("エクセル",A1:C2,2,FALSE) のようにして、エクセル を行「1」から検索して一致する列の 2 行目の値を取得できます。

=IFERROR(HLOOKUP(100,A1:C2,2,FALSE),0) のようにして、検索結果が見つからないときに 0 を表示できます。

列全体の値を取得したり、複数条件に一致する値を取得できます。

検索して一致した行の右方向にある値を取得するには「VLOOKUP 関数」を使用します。
セルを検索する方法は「文字列やセルを検索する」をご覧ください。

HLOOKUP 関数の引数

HLOOKUP(検索値, 範囲, 行番号)
検索値範囲の 1 行目のデータと比較し、一番近い列の行番号の値を取得します。

HLOOKUP(検索値, 範囲, 行番号, 一致モード)
一致モード検索値と一致するデータを取得するように指定できます。

引数「検索値」検索する値を、数値、文字列、セル参照、関数などで指定します。
引数「一致モード」が FALSE のときだけ、ワイルドカードを使用できます。
スピル化
引数「範囲」検索対象となるデータと取得するデータが含まれたセルの範囲を指定します。この範囲の一番上の行を検索します。
引数「行番号」取得する行番号を指定します。引数「範囲」の一番上の行が 1 です。上から 2 番目の行は 2 になります。スピル化
引数「一致モード」省略できます。
TRUE または省略:検索する値と同じまたは、それ以下の最大値と一致します。引数「範囲」の一番上の行が昇順に並べられている必要があります。
FALSE:検索する値と同じ値と一致します。
スピル化

スピル化:セルの範囲や配列を指定すると結果が「スピル」します。

引数「検索値」

引数「一致モード」が FALSE のときに使用できるワイルドカードには次のものがあります。

パターン 説明 使用例 一致例
*任意の長さの文字"あ*"あ, あい, あいう
?任意の 1 文字"あ?"あい, あか, あき
~ワイルドカードの文字 * ? を検索する"あ~?~*"あ?*

使い方

HLOOKUP 関数の使い方を簡単に説明すると次のようになります。

1. 何を:引数「検索値」に指定した値を検索します。

2. どの行から検索:引数「範囲」の 1 行目のデータと一致するか検索します。

3. どの行を取得:「2.」で一致した列の引数「行番号」の値を取得します。

=HLOOKUP(B8,C2:E5,2,FALSE)
29

No. を検索して名前を取得する

No. が 1 の列の名前を取得します。引数「行番号」に名前の行の番号 2 を入力してその値を取得できます。

=HLOOKUP(B8,$C$2:$E$5,2,FALSE)
21

No. を検索して列の値を取得する

No. が 2 の列のすべての行の値を取得します。引数「行番号」に引数「範囲」のすべての行の番号を入力して列の値を取得できます。

=HLOOKUP(B8,$C$2:$E$5,C8,FALSE)

結果を下方向にコピーしています。

22

ROW 関数」を使用して、セルをコピーして行番号を 1 からの連番にできます。

=HLOOKUP($B$8,$C$2:$E$5,ROW(C2)-1,FALSE)

結果を下方向にコピーしています。

26

ROW(C2)-1 のようにして、行「2」を基準に 1 からの連番にできます。

行「3」を基準にするには ROW(C3)-2 のように、引き算する値が増えます。

特定の文字が入っている名前を検索する

ワイルドカードを使用して、名前に ワード が入っている列の個数を取得します。

=HLOOKUP("*ワード*",C3:F5,3,FALSE)
=HLOOKUP("ワード*",C3:F5,3,FALSE)
=HLOOKUP("*ワード",C3:F5,3,FALSE)

名前が ワード で始まる値は "ワード*" のように入力します。

名前が ワード で終わる値は "*ワード" のように入力します。

23

見つからないときに 0 にする

検索結果が見つからないときに 0 を表示します。「IFERROR 関数」を使用して、エラーのときの値を表示できます。

=IFERROR(HLOOKUP(B8,C2:E5,1,FALSE),0)
=HLOOKUP(B8,C2:E5,1,FALSE)

HLOOKUP 関数は検索結果が見つからないときにエラーになります。

24

値が基準値以下の最大値を検索する

基準値が 80 のとき、値が 80 以下の最大値の名前を取得します。引数「一致モード」に TRUE を指定して、引数「検索値」以下の最も近い値を検索できます。

=HLOOKUP(B8,$C$2:$E$5,2,TRUE)

日付ならその日以前の一番近い日付になります。

検索対象の行が昇順に並べ替えられている必要があります。

25

高度な使い方

HLOOKUP 関数と他の関数を組み合わせた使い方などを紹介します。

好きな行を検索する

名前を検索して No. を取得します。「INDEX 関数」と「MATCH 関数」を組み合わせて好きな行から検索できます。

365 または 2021 からは「XLOOKUP 関数」を使用します。
=INDEX($C$2:$E$5,1,MATCH(B8,$C$3:$E$3,0))
=XLOOKUP(B9,$C$3:$E$3,$C$2:$E$2)
8
詳しくは「INDEX と MATCH 関数を組み合わせる」をご覧ください。

複数条件で検索する

名前とバージョンを検索して両方に一致する個数を取得します。

検索専用の行を上端に作成して、その中に名前とバージョンを区切り文字を付けて結合します。

=C3&","&C4
27

引数「検索値」に検索専用と同じように名前とバージョンを区切り文字を付けて結合した値を入力して、複数条件に対応できます。

365 または 2021 からは、検索専用の列を作成しないで「XLOOKUP 関数」を使用します。
=HLOOKUP(B7&","&C7,C2:E5,4,FALSE)
=XLOOKUP(B7&","&C7,C3:E3&","&C4:E4,C5:E5)
28

解説

好きな行から検索できるようにした上位互換である「XLOOKUP 関数」があります。通常はそちらを使用します。

引数「範囲」の一番上の行が検索対象になります。それ以外の行からは検索できません。

引数「検索値」が引数「範囲」から見つからないときはエラー #N/A になります。

引数「行番号」は、引数「範囲」の一番上の行を基準とした 1 からの連番です。

引数「行番号」が引数「範囲」の行数を超えるときはエラー #REF! になります。

文字列を検索するには、引数「一致モード」を FALSE にします。一致する文字列を検索できます。

引数「一致モード」が FALSE なら、文字列の大文字と小文字は区別しません。"ABC""abc" は等しいです。

引数「一致モード」が FALSE なら、引数「検索値」にワイルドカードを使用できます。

引数「一致モード」が TRUE のとき、引数「範囲」の一番上の行が昇順に並べ替えられている必要があります。そうでないときは正しい結果を取得できません。

例えば、引数「検索値」が 2 なら、次の画像では 1, 10, 2, 20 の順で検索します。検索値より大きい値が見つかった時点で、それ以降の値は検索しません。昇順のときにそれ以降に一致する値はないからです。10 と比較したとき 2 より大きいのでこれ以降の値は検索されません。結果を 10 の 1 つ前の列から取得します。

=HLOOKUP(B5,$B$2:$E$2,1,TRUE)
19