更新日：、 作成日：
エクセル 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 を表示できます。
列全体の値を取得したり、複数条件に一致する値を取得できます。
HLOOKUP 関数の引数
HLOOKUP(検索値, 範囲, 行番号)
検索値を範囲の 1 行目のデータと比較し、一番近い列の行番号の値を取得します。
HLOOKUP(検索値, 範囲, 行番号, 一致モード)
一致モードで検索値と一致するデータを取得するように指定できます。
| 引数「検索値」 | 検索する値を、数値、文字列、セル参照、関数などで指定します。 引数「一致モード」が FALSE のときだけ、ワイルドカードを使用できます。 | スピル化 |
| 引数「範囲」 | 検索対象となるデータと取得するデータが含まれたセルの範囲を指定します。この範囲の一番上の行を検索します。 | |
| 引数「行番号」 | 取得する行番号を指定します。引数「範囲」の一番上の行が 1 です。上から 2 番目の行は 2 になります。 | スピル化 |
| 引数「一致モード」 | 省略できます。 TRUE または省略：検索する値と同じまたは、それ以下の最大値と一致します。引数「範囲」の一番上の行が昇順に並べられている必要があります。 FALSE：検索する値と同じ値と一致します。 | スピル化 |
スピル化：セルの範囲や配列を指定すると結果が「スピル」します。
引数「検索値」
引数「一致モード」が FALSE のときに使用できるワイルドカードには次のものがあります。
| パターン | 説明 | 使用例 | 一致例 |
| * | 任意の長さの文字 | "あ*" | あ, あい, あいう |
| ? | 任意の 1 文字 | "あ?" | あい, あか, あき |
| ~ | ワイルドカードの文字 * ? を検索する | "あ~?~*" | あ?* |
スポンサーリンク
使い方
HLOOKUP 関数の使い方を簡単に説明すると次のようになります。
1. 何を：引数「検索値」に指定した値を検索します。
2. どの行から検索：引数「範囲」の 1 行目のデータと一致するか検索します。
3. どの行を取得：「2.」で一致した列の引数「行番号」の値を取得します。
No. を検索して名前を取得する
No. が 1 の列の名前を取得します。引数「行番号」に名前の行の番号 2 を入力してその値を取得できます。
No. を検索して列の値を取得する
No. が 2 の列のすべての行の値を取得します。引数「行番号」に引数「範囲」のすべての行の番号を入力して列の値を取得できます。
結果を下方向にコピーしています。
「ROW 関数」を使用して、セルをコピーして行番号を 1 からの連番にできます。
結果を下方向にコピーしています。
ROW(C2)-1 のようにして、行「2」を基準に 1 からの連番にできます。
行「3」を基準にするには ROW(C3)-2 のように、引き算する値が増えます。
特定の文字が入っている名前を検索する
ワイルドカードを使用して、名前に ワード が入っている列の個数を取得します。
=HLOOKUP("ワード*",C3:F5,3,FALSE)
=HLOOKUP("*ワード",C3:F5,3,FALSE)
名前が ワード で始まる値は "ワード*" のように入力します。
名前が ワード で終わる値は "*ワード" のように入力します。
見つからないときに 0 にする
検索結果が見つからないときに 0 を表示します。「IFERROR 関数」を使用して、エラーのときの値を表示できます。
=HLOOKUP(B8,C2:E5,1,FALSE)
HLOOKUP 関数は検索結果が見つからないときにエラーになります。
値が基準値以下の最大値を検索する
基準値が 80 のとき、値が 80 以下の最大値の名前を取得します。引数「一致モード」に TRUE を指定して、引数「検索値」以下の最も近い値を検索できます。
日付ならその日以前の一番近い日付になります。
検索対象の行が昇順に並べ替えられている必要があります。
高度な使い方
HLOOKUP 関数と他の関数を組み合わせた使い方などを紹介します。
好きな行を検索する
名前を検索して No. を取得します。「INDEX 関数」と「MATCH 関数」を組み合わせて好きな行から検索できます。
=XLOOKUP(B9,$C$3:$E$3,$C$2:$E$2)
複数条件で検索する
名前とバージョンを検索して両方に一致する個数を取得します。
検索専用の行を上端に作成して、その中に名前とバージョンを区切り文字を付けて結合します。
引数「検索値」に検索専用と同じように名前とバージョンを区切り文字を付けて結合した値を入力して、複数条件に対応できます。
=XLOOKUP(B7&","&C7,C3:E3&","&C4:E4,C5:E5)
解説
好きな行から検索できるようにした上位互換である「XLOOKUP 関数」があります。通常はそちらを使用します。
引数「範囲」の一番上の行が検索対象になります。それ以外の行からは検索できません。
引数「検索値」が引数「範囲」から見つからないときはエラー #N/A になります。
引数「行番号」は、引数「範囲」の一番上の行を基準とした 1 からの連番です。
引数「行番号」が引数「範囲」の行数を超えるときはエラー #REF! になります。
文字列を検索するには、引数「一致モード」を FALSE にします。一致する文字列を検索できます。
引数「一致モード」が FALSE なら、文字列の大文字と小文字は区別しません。"ABC" と "abc" は等しいです。
引数「一致モード」が FALSE なら、引数「検索値」にワイルドカードを使用できます。
引数「一致モード」が TRUE のとき、引数「範囲」の一番上の行が昇順に並べ替えられている必要があります。そうでないときは正しい結果を取得できません。
例えば、引数「検索値」が 2 なら、次の画像では 1, 10, 2, 20 の順で検索します。検索値より大きい値が見つかった時点で、それ以降の値は検索しません。昇順のときにそれ以降に一致する値はないからです。10 と比較したとき 2 より大きいのでこれ以降の値は検索されません。結果を 10 の 1 つ前の列から取得します。
スポンサーリンク