ExcelにあるHLookUp関数の使用方法です。配列の先頭行を並び替え、#N/Aを返す、Match関数と組み合わせ/1行目ではない実例です

Excelでは、VLookUp関数とLookUp関数は縦方向を検索することに使用され、HLookUp関数は横方向を検索することに使用されます。通常、この関数はテーブルの1行目を検索しますが、選択した範囲はテーブルの1行目が含まれていない場合は、テーブルの1行目を検索しません。 HLookUp関数とVLookUp関数と同じく4つの引数があり、その中の3つは同じですが、1つの引数が違います。1つは行番号を返し、もう1つは列番号を返します。数値、文字および論理値は検査値の検索方法をTRUEに設定する場合に、範囲の1行目にあるデータを、昇順に並べ替えておく必要があります。昇順になってない場合、正しい結果が求められません。検索値を完全に一致するデータが無い場合エラー値「#N/A」が返されます。 HLookUp関数をMatch関数と組み合わせて、違い行の値が返されます。

Excel にあるHLookUp関数の書式

書式

=HLOOKUP(LookUp_Value, Table_Array, Row_Index_Num, [Range_LookUp])

=HLOOKUP（検索値,範囲,行番号,検索方法）

解説

1、LookUp_Valueは、指定された範囲の 1 行目で値を検索します。テーブルの1行目が含まれていない場合は、テーブルの1行目を検索しません。指定された範囲はB2:C9なら、B2:C2を検索します。つまりB2:C2は1行目です。テーブルの1行目を検索しません。

2、検索方法がTrueに設定されている場合は、範囲の1行目の数値、文字、および論理値は左から右、昇順に並べ替えておく必要があります。昇順になってない場合に、 HLookUpは正しい結果が得られません。検索方法がFalseに設定されている場合は、範囲の並べ替えが必要ないです。範囲の文字は大文字と小文字が区別されません。

3、行番号が1の場合は、範囲の1行目の値を返します。行番号が2の場合は、範囲の2行目の値を返します。その他はこれによって類推すればいいです。 行番号小なり1の場合に、HLookUpは「#VALUE!」エラーを返します。行番号が範囲の行数より大きい場合に、HLookUpは「#REF!」エラーを返します。

4、検索方法は二つの選択肢があります。1つはTrue（近似一致）であり、もう1つはFalse（完全一致）です。検索方法が省略されたまたはTrueの場合に、検索値が見つからない場合は、検索値より小さい値の最大値を返します 。 検索方法がFalseの場合に、検索値が見つからない場合は、「#N/A」エラーが返されます。

5、行番号がFalseに設定されており、検査値が文字列の場合は、ワイルドカードの疑問符（？）とアスタリスク（）をLookUp_Valueで使用できます。疑問符は任意の文字に一致し、アスタリスクは任意の文字または文字列に一致します。疑問符またはアスタリスクを検索する場合は「〜」を追加する必要があります。例えば、疑問符を探す場合は、「~?」と入力します。アスタリスクを探す場合は、「~」と入力します。

ExcelにあるHLookUp関数の使用方法と実例

引数の検索方法を省略する例

1、行目で「名前」を検索する場合は、 A9セルをダブルクリックし、

=HLOOKUP("名前",A1:C9,5)」

数式をAセルにコピーし、Enterキーを押して「経理部」を返します。また、A9セルをダブルクリックし、5の後「,」を追加し、Enterキーを押して「野原仁志」を返します。A9セルをダブルクリックし、「,」の後「true」を入力し、Enterキーを押して「経理部」を返します。A9セルをダブルクリックし、trueをfalseに変更し、Enterキーを押して「野原仁志」を返します。手順は次の図によって示します。

2、数式の解説：

A、数式「=HLOOKUP(“名前”,A1:C9,5)」では、 “名前”は検索値、A1:C9は検索範囲、5は返す行番号です。この数式は引数の検索方法を省略しています。この数式は、 最初の行（選択された範囲A1:C9の1行目を参照）で「名前」を検索し、5行目の値を返します。

B、数式

=HLOOKUP("名前",A1:C9,5)

と

=HLOOKUP("名前",A1:C9,5,True)

はどちらも “経理部”を返します。引数の行番号の後ろに「,」を追加しない場合は、検索方法のデフォルト値がTrueに設定されます。数式

=HLOOKUP("名前",A1:C9,5,)

と

=HLOOKUP("名前",A1:C9,5,False)

は“野原仁志”を返します。引数の行番号の後ろに「,」を追加する場合は、検索方法のデフォルト値がFalseに設定されます。

引数の行番号が検索範囲にいない実例

1、A9セルをダブルクリックして、数式「=HLOOKUP(“名前”,A1:C9,0,1)」をA9にコピーし、Enterキーを押して「#VALUE!」エラーを返します。また、A9セルをダブルクリックし、0を9に変更し、Enterキーを押して0を返します。A9セルをもう一度ダブルクリックし、9を10に変更し、Enterキーを押して「#REF!」エラーを返します。手順は次の図によって示します。

2、数式の解説：

=HLOOKUP("名前",A1:C9,0,1)

の中で、行番号は0の場合は「#VALUE!」エラーを返します。行番号が小なり1の場合にエラー発生します。0を9に変更すると、9行目の値を返し、つまりこの数式のセルです。0を返します。しかし、9を10に変更すると、行番号が検索範囲の圏外にあるので、エラーが発生します。

検索値が見つからない場合に、小なり検索値の最大値を返す実例です。（検索範囲の先頭行を並び替え必要があります。）

1、仮に売上高589を検索します。 B8セルをダブルクリックして、数式「=HLOOKUP(589,B2:E7,4,TRUE)」をB8セルにコピーし、Enterキーを押して679を返します。C2セルをダブルクリックして、539を639に変更し、B8セルの値が512に変更します。手順は次の図によって示します。

2、数式

=HLOOKUP(589,B2:E7,4,TRUE)

の解説：
B2:E7は検索範囲です。ここの最初の行はB2:E2（テーブルの最初の行ではないです。）と言います。つまり、テーブルの2行目で589を検索します。589が見つからないので、589以下の最大値「551」を返します。次には当列の4行目の「679」を返します。ここの検索方法はTrueに設定されているため、B2:E2を検索する時に、左から右に昇順で並べ替える必要があります。そうしないと、誤った値が返される可能性があります。例えば、数値の539を639に変更した後、結果は512に変わりました。589＜639ので、この時小なり589の値は467しかないため、当列4行目の値「512」を返します。

検索値が見つからず、「#N/A」エラー値を返す実例（検索範囲の最初の行を並び替える必要はありません。）

1、また、売上高589を検索します。 B8セルをダブルクリックし、数式「=HLOOKUP(589,B2:E7,4,FALSE)」をB8セルにコピーしてEnterキーを押して「#N/A」エラーを返します。B2セルをダブルクリックし、639を539に変更し、B8セルの値はまた「#N/A」エラーです。手順は次の図によって示します。

2、数式

=HLOOKUP(589,B2:E7,4,FALSE)

の解説：
最初の実行では、検索範囲のB2:E2 は左から右に昇順で設定していない場合に、「#N/A」エラーを返します。639を539に変更されると、検索範囲のB2:E2 も左から右に昇順で設定した場合に、また「#N/A」エラーを返します。これより、昇順に設定するかどうかに関係なく、検索方法はFalseに設定される場合に検索値が見つかりません。HLOOKUP関数は「#N/A」エラーを返すため、つまり完全一致の場合に昇順に並び替える必要がないです。

検索値にワイルドカード（？）とアスタリスク（*）が含まれる実例

検索値にワイルドカードの疑問符（?）がある例です

1、仮に先頭が2桁の文字で、「半袖」で終わる商品の販売数量を検索したいです。B4セルをダブルクリックし、

=HLOOKUP("??半袖",B1:F3,3,FALSE)

数式をB4セルにコピーし、Enterキーを押して962を返します。手順は次の図によって示します。

2、数式

=HLOOKUP("??半袖",B1:F3,3,FALSE)

では、 “?? 半袖”が検索値であり、疑問符は文字を代表しています。つまり、任意の2桁の文字で始まり、 “半袖”で終わります。検索値にワイルドカードがあるため、検索方法をFalseに設定する必要があります。

検索値にワイルドカードのアスタリスク（*）がある例です

1、仮に任意の文字で始まり、「ズボン」で終わる販売数量を検索します。B4セルをダブルクリックして、

=HLOOKUP("ズボン",B1:F3,3,FALSE)

数式をB4にコピーし、Enterキーを押して640を返します。B4セルをもう一度ダブルクリックして、 ” ズボン”を “黒 “に変更し、Enterキーを押して458を返します。手順は次の図によって示します。

2、数式

=HLOOKUP("ズボン",B1:F3,3,FALSE)

では、 ” ズボン”が検索値です。つまり、任意の1桁以上の文字で始まり、 “ズボン”で終わります。D1、 E1の値は条件に満たしますが、HLookUp関数は条件を満たす最初の値を返すため、640を返します。「* ズボン」を「黒」に変更すると、B1とD1の値が条件に満たします。 最初の値を返すため、458が返されます。

Excelで 1行目ではないHLookUp 関数とMatch関数を組み合わせる実例

1、仮に黒色ズボンの2月の販売数量を検索します。F8セルをダブルクリックし、数式

=HLOOKUP($F$7,A1:D5,MATCH($E$8,A1:A5,0),0)

をF8セルにコピーし、Enterキーを押して543を返します。1行目を右クリックし、「挿入」を選択すると、数式の行数も自動的に下に移動します。F8はF9に変わります。数式の各引数のセル位置も自動的に1を足し、F9の値は変わらぬ543です。手順は次の図によって示します。

2、数式

=HLOOKUP($F$7,A1:D5,MATCH($E$8,A1:A5,0),0)

の解説：

A.、$F$7はF7セルを絶対参照とします。つまり、下にドラッグしても右にドラッグしても、F7はF8、F9、…またはG8、H8、…になりませんが、行を挿入すると、 $F$7は自動的に$F$8になり、この時$E$8と$F$7は同じです。

B、MATCH($E$8,A1:A5,0)は、A1:A5でE8（2月の販売数量）を検索します。0は完全一致で、最終的に4を返します。

C、数式が

=HLOOKUP("黒色ズボン",A1:D5,4,0)

に変更すると、1行目に”黒色ズボン”を検索します。D1セルに見つけ、当列4行目の値「543」を返します 。HLOOKUP数式最後の引数「0」は完全一致の意味です。

D、最初の行に行を挿入すると、数式は

=HLOOKUP($F$8,A2:D6,MATCH($E$9,A2:A6,0),0)

になり、検索範囲もA2:D6になります。 1行目はA2:F2になります。つまり、テーブルの2行目に検索します。

ヒント：下にドラッグしたい場合、数式のA1:D5を絶対参照（$A$1:$A$5）に設定しなければならないです。数式は

=HLOOKUP($F$7,$A$1:$D$5,MATCH($E$8,A1:A5,0),0)

になります。