=HLOOKUP("Mar",A1:E3,2,FALSE)は、A1:E3の最初の行でMarを探し、同じ列の2行目の値を返します。VLOOKUPを横に倒したもので、見出しが上に横に並んでいる表に使います。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Mar | |||
| 6 | Sales | 4,800 |
B6は1行目でMarを探してD列に見つけ、その列の2行目4,800を返します。B5でAprを選ぶと5,100に、数式の2を3に変えると費用になります。
HLOOKUP関数の構文
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value(検索値): 表の最初の行で探すもの。table_array(範囲): 表。HLOOKUPはその一番上の行だけを探します。row_index_num(行番号): 返す行。一番上の行を1として数えます。表の高さより大きい数は#REF!、0は#VALUE!になります。range_lookup(検索方法): 完全一致ならFALSE。並べ替えた行での近似一致ならTRUEか省略。
一致は大文字と小文字を区別せず(marでMarが見つかります)、FALSEのときは検索値にワイルドカード*と?を使えます。最初の行にない値は#N/Aを返します。
行方向の近似一致
TRUEを指定すると、HLOOKUPは検索値以下の最大の見出しを探します。最初の行は左から右へ昇順に並べておく必要があります。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | 0 | 2 | 5 | 10 |
| 2 | Cost | $4.50 | $6.00 | $9.50 | $14.00 |
| 3 | |||||
| 4 | Parcel (kg) | 7 | |||
| 5 | Cost | $9.50 |
7 kgは見出しにありません。それを超えない最大の見出しは5なので、B5は$9.50を返します。B4を1.5にすると$4.50、12にすると$14.00です。数式の表はA1:E2ではなくB1:E2です。A1の文字列のラベルが並べ替えた行に含まれないように、最初の重さから始めています。
行方向のXLOOKUP
Excel 2021とMicrosoft 365では、XLOOKUPがHLOOKUPの代わりになります。探す行と返す行を2つの範囲として受け取るので数える行番号がなく、複数行の高さの戻り範囲を指定すれば列全体が返ってきます。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | Profit | 1,600 | 1,400 | 1,900 | 2,100 |
| 5 | |||||
| 6 | Month | Feb | |||
| 7 | Figures | 3,900 | |||
| 8 | 2,500 | ||||
| 9 | 1,400 |
B7の1つの数式が、Febの3つの数値3,900、2,500、1,400をB7:B9の下方向にスピルします。B8かB9に何か入っていると、B7は#SPILL!と表示します。見つからないときのメッセージや最後の一致など、ほかのオプションはXLOOKUPのページで説明しています。
代わりに表の向きを変える: TRANSPOSE
表を縦向きにコピーするほうがよい解決策になることもあります。=TRANSPOSE(A1:D3)は同じセルを行と列を入れ替えて返し、元の表とつながったままです。それに対してVLOOKUP、FILTER、グラフがいつもどおり使えます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 4,200 | 3,900 | 4,800 |
| 3 | Costs | 2,600 | 2,500 | 2,900 |
| 4 | ||||
| 5 | Month | Sales | Costs | |
| 6 | Jan | 4,200 | 2,600 | |
| 7 | Feb | 3,900 | 2,500 | |
| 8 | Mar | 4,800 | 2,900 |
A5は4行3列のブロックをスピルします。月が縦に、SalesとCostsが横に並びます。C2のFebの売上を4100に変えると、コピーも変わります。数式を使わずに1回だけコピーするなら、表を選択してコピーし、ホーム > 貼り付け > 形式を選択して貼り付けで「行列を入れ替える」にチェックを入れます。
練習: 1か月の費用
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Apr | |||
| 6 | Costs |
やってみよう: B6で、HLOOKUPを使ってB5の月の費用を返してください。
よくある質問
VLOOKUPとHLOOKUPの違いは何ですか？
VLOOKUPは表の最初の列を下方向に探し、右側の列の値を返します。HLOOKUPは最初の行を横方向に探し、下の行の値を返します。引数は同じで、列番号の代わりに行番号を指定します。
HLOOKUPの行番号とは何ですか？
返す行の番号で、表の最初の行を1として数えます。=HLOOKUP("Mar",A1:E3,3,FALSE)の3は、A1:E3の3行目という意味です。表の高さより大きい数は#REF!を返します。
XLOOKUPでHLOOKUPの代わりができますか？
できます。XLOOKUPはどちらの方向にも動きます：=XLOOKUP("Mar",B1:E1,B2:E2)は行を探して別の行から値を返します。Excel 2021かMicrosoft 365が必要です。
HLOOKUPが#N/Aを返すのはなぜですか？
検索値が表の最初の行にないからです。打ち間違い、余分なスペース、文字列として保存された数値、別の行にある値などが原因です。最後の引数がTRUEのときは、最初の見出しより小さい値も#N/Aを返します。
