サイトについて      連絡先   検索

エクセル HLOOKUP 関数:検索する値に対応した値を指定の行から取得する

はじめに

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

HLOOKUP 関数は検索する値に対応した値を指定の行から取得します。

2 行目に対応する値を 3 行目や 4 行目から取得したいときに使用します。複数条件の指定やエラーを回避する方法も紹介します。

  • 目次
    • 構文
    • 使用例
    • 解説

構文

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

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

引数「検索値」検索する値を、数値、文字列、セル参照、関数などで指定します。
引数「検索の型」が FALSE のときだけ、ワイルドカードを使用できます。
引数「範囲」検索対象となるデータと取得するデータが含まれたセルの範囲を指定します。この範囲の一番上の行を検索します。
引数「検索の型」に TRUE を指定するときは、一番上の行が昇順に並んでいる必要があります。
引数「行番号」取得する行番号を指定します。引数「範囲」の一番上の行が「1」です。上から 2 番目の行は「2」になります。
引数「検索の型」省略できます。
TRUE または省略: 検索する値に一番近い小さい値を取得します。
FALSE: 検索する値に完全一致する値を取得します。

使用例

「HLOOKUP 関数」がどのような動作をするかを簡単に説明すると次のようになります。

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

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

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

=HLOOKUP(B7,B2:D4,C7,FALSE)
18

引数「検索値」を変えたときにどの列の値を取得するか確認する。

=HLOOKUP(B7,$B$2:$D$4,2,FALSE)
16

引数「行番号」を変えたときにどの行の値を取得するか確認する。

=HLOOKUP("エクセル",$B$2:$D$4,B7,FALSE)
17

引数「検索の型」の違いを確認する。

=HLOOKUP(B5,$B$2:$E$2,1,C5)
9

複数条件で検索する

「エクセル2016」の価格を取得したいように、名前とバージョンを条件にして取得したいデータがあります。

10

まずは検索専用行を作成します。条件となる名前とバージョンを結合した値にします。

=C3&C4
11

引数「検索値」にも名前とバージョンを結合した値を指定します。これを検索専用行から検索すれば、複数条件に対応できます。

=HLOOKUP(B8&C8,$C$2:$F$5,4,FALSE)
12

解説

引数「検索の型」に通常は FALSE を指定します。

引数「検索の型」に FALSE を指定したときに大文字と小文字は区別されません。"ABC""abc"は同じものとして一致します。

引数「検索の型」が FALSE のときだけ、引数「検索値」にワイルドカードを使用できます。使用できるワイルドカードには次のものがあります。

パターン 説明 使用例 一致例
*任意の長さの文字"あ*"あ, あい, あいう
?任意の 1 文字"あ?"あい, あか, あき
~ワイルドカードの文字 * ? を検索する"あ~?~*"あ?*
=HLOOKUP(B5,$B$2:$D$2,1,FALSE)
13

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

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

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

引数「検索値」が存在しないときはエラー#N/Aになります。

エラーを回避する

引数「検索値」が空や一致するデータが存在しないときはエラーになります。エラーを表示したくないなら「IFERROR 関数」と組み合わせて回避できます。

=IFERROR(HLOOKUP(B6,$B$2:$B$3,2,FALSE),"")
15

オートフィルを使ってコピーする

「HLOOKUP 関数」をコピーしたときに引数「行番号」を対応させて移動したいときは「ROW 関数」を使用します。

=HLOOKUP(B7,$B$2:$B$4,ROW(B7)-7,FALSE)
20

「ROW 関数」は引数に指定したセルの行番号を取得します。ROW(B7)と入力すると 7 が取得されます。これをコピーして下の行に貼り付ければROW(B8)ROW(B9)になるので 8, 9 といった値を取得できます。後は取得したい行番号に合わせるために -7 のような調整をします。

引数「行番号」を検索した位置にするには「MATCH 関数」を使用します。

  • 目次
    • 構文
    • 使用例
    • 解説