事務職の方はExcelで計算したり、データ入力することが多いですよね。
そのため、さまざまな関数をご存知かと思います。
その中でも、HLOOKUP関数は表を横方向に検索して検索値を抽出する大変便利な関数です。しかし、検索値が２つ以上あると一番左の項目の値が返されるため、見つけたいデータが表示されない場合があります。
今回の記事では、HLOOKUP関数の基本的な使い方や、複数の項目を検索値にするHLOOKUP関数の複数条件の設定方法を解説します。ぜひ参考にしてくださいね。
目次
HLOOKUP関数とは？
HLOOKUP関数とは、Excelの表示の上端行または配列内の特定の値を検索し、テーブルまたは配列内の指定した行から同じ列の値を返す関数です。
HLOOKUP関数は比較する値がデータテーブルの上端行にあり、指定した行数分だけ下を参照する場合に使用します。 比較する値が検索データの左側の列にある場合は、VLOOKUP関数を使用してください。VLOOKUP関数も後述致します。
なお、HLOOKUP関数の “H” とは、英語の横方向 (horizontal) からきています。構文は次の通りです。
| HLOOKUP(検索値, 範囲, 行番号, [検索の型]) |
HLOOKUP関数の構成要素には、次の引数があります。
検索値
検索値は必ず指定します。
テーブルの上端行で検索する値を指定し、検索値には値、参照、または文字列を指定します。
範囲
範囲は必ず指定します。
データを検索する情報のテーブルです。セル範囲への参照またはセル範囲名を使用するようにしてください。
また、リストの範囲における上端行の列のデータは、文字列、数値、論理値のいずれでもかまいません。
また、検索の型にTRUEを指定した場合、範囲の上端行の列のデータは、昇順で配置しておく必要があります。
つまり
| ～-2、-1、0、1、2～、A～Z、FALSEからTRUE |
の順となるのです。
その他の場合、HLOOKUP関数では正しい値を得られない場合があります。
また、検索の型にFALSEを指定した場合、範囲を並べ替える必要はありません。
英字は大文字と小文字は区別されませんが、半角であることに注意しましょう。
そして、値は昇順に左から右に並べ替えます。
行番号
行番号は必ず指定します。
一致する値を返す、範囲内の行番号。 行番号に 1 を指定すると、範囲の最初の行の値が返され、行番号に 2 を指定すると、範囲の 2 番目の行の値が返され、以降同様に処理されます。 行番号が 1 より小さい場合、エラー値 #VALUE! が返され、行番号が範囲の行数より大きい場合は、エラー値 #REF! が返されます。
検索の型
検索の型は任意のため省略可能です。
HLOOKUP関数を使用して検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを指定する論理値です。TRUEを指定するか省略した場合は近似値が返されます。つまり、完全に一致する値が見つからない場合は、検索値未満の最大値が使用されます。 FALSEを指定した場合、HLOOKUP関数では完全に一致する値が検索されます。
完全に一致する値が見つからない場合は、エラー値 #N/A が返されます。
HLOOKUP関数の使い方
HLOOKUP関数は、前述したとおり検索の型にTRUE を指定します。
もし検索値が見つからない場合は、検索値未満で最も大きい値が使用されます。
また、検索値が範囲の上端行の最小値より小さい場合、エラー値 #N/A が返されます。
検索の型が FALSEで検索値が文字列の場合、検索値で疑問符 （?） またはアスタリスク （*） をワイルドカード文字として使用できます。
ワイルドカード文字の疑問符は任意の 1 文字を表し、アスタリスクは1文字以上の任意の文字列を表します。
ワイルドカード文字ではなく、通常の文字として疑問符やアスタリスクを検索する場合は、その文字の前に半角のチルダ （~）を付ければ問題ありません。
HLOOKUP関数以外でExcelで条件に合うデータを抽出する5つの方法
何万行もある膨大なデータの中から条件に合うデータを抽出するのは大変ですが、営業事務やデータ分析のような業務ではよくあることです。
ですが、Excelならどれだけたくさんデータがあっても、一瞬で目的のデータを抽出できます。続いては、HLOOKUP関数以外のExcelで条件に合うデータを抽出する関数をご紹介します。これを機にぜひマスターしましょう。
1.VLOOKUP関数
条件を検索したい基本的な関数は、VLOOKUP関数です。すでに活用されている方も多いと思いますが、おさらいしてみましょう。
VLOOKUP関数とは、範囲の1列目で値を検索し、一致するセルと同じ行にある値を返す関数です。
VLOOKUP関数の構文は次の通りです。
| VLOOKUP（検査値, 範囲, 列番号, 検索の型） |
VLOOKUP関数の構成要素には、次の4つの引数が必要になります。
1.検索する値または検索値です。
2.検索値が含まれるセル範囲。 VLOOKUP関数が正常に機能するために、検索値は範囲の最初の列に必ず位置している必要があるので注意してください。
たとえば、検索値がセルC2にある場合、範囲はC列から始まる必要があります。
3.戻り値を含む範囲内の列番号。たとえば、「B2:D11」を範囲として指定した場合、Bを最初の列、Cを2番目というように数えます。
4.必要に応じて戻り値として近似一致を検索する場合は「TRUE」、完全一致を検索する場合は「FALSE」を指定できます。何も指定しない場合、既定値は「TRUE」つまり近似一致を常に返します。
2.XLOOKUP関数
条件が入力された列より左の列、または条件が入力された行より上の行のデータを抽出したい場合は、XLOOKUP関数を使う方法がおすすめです。ただし、XLOOKUP関数はMicrosoft 365やOffice 2021でなければ使用できません。ご自身のExcelのバージョンを確認しましょう。XLOOKUP関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、XLOOKUP関数は最も近い （近似） 一致を返します。XLOOKUP関数の構文は次の通りです。
| XLOOKUP(検索値, 検索範囲, 戻り範囲) |
1つめの引数「検索値」には、検索キーを指定します。
たとえば、商品IDや商品名など、マスター表の中で重複値がない項目を指定してください。
単価は他の商品に同じ価格のものが存在する可能性があるので、検索に使うキーとしては不適切です。
2つめの引数「検索範囲」には、「マスターデータの中で検索対象となる範囲」を指定します。
3つめの引数「戻り範囲」には、「抽出したい値の範囲」を指定します。2つめの引数「検索範囲」と、3つのめの引数「戻り範囲」が、XLOOKUP関数を使いこなすうえで重要なポイントです。
参考：XLOOKUPとはどんな関数か？ わかりやすく図解、「VLOOKUPもう不要」は本当？
3.INDEX関数
列番号と行番号が交差する位置にあるデータを抽出したい場合は、INDEX関数を使う方法がおすすめです。INDEX関数（インデックス関数）とは、指定された行と列が交差する位置にあるセルの値を返す関数で、構文は次の通りです。
| INDEX（参照, 行番号, 列番号, 領域番号） |
INDEX 関数の構成要素には次の引数があります。
配列
配列は必須で、セル範囲または配列定数を指定します。配列が 1 行または 1 列のみの場合、それぞれ行番号または列番号を省略することができます。
配列が複数行および複数列で構成され、行番号または列番号のどちらか一方しか指定されていない場合、配列の中にある行または列全体の配列が返されます。
行番号
行番号は列番号が存在する場合を除き、必須です。配列の中にあり、値を返す行を数値で指定します。 行番号を省略する場合は、列番号が必要になります。
列番号
列番号は任意です。配列の中にあり、値を返す列を数値で指定します。
ただし、列番号を省略する場合は行番号が必要になるので注意してください。
なお、INDEX関数の主な仕組みは次の3つです。
1.行番号と列番号の両方の引数を使用している場合は、行番号と列番号の共通部分にあるセルの値がINDEX関数によって返されます。
2.行番号と列番号には、配列内のセルを指定する必要があります。配列の範囲外のセルを指定すると、INDEX関数は #REF! を返します。 エラーが表示されます。
3.行番号または列番号を 0（ゼロ）に設定すると、列全体または行全体の値の配列がそれぞれ INDEX によって返されます。 配列として返される値を使用するには、INDEX関数を配列数式として入力します。
| 注意現在のバージョンの Microsoft 365 を使用している場合は、出力範囲の左上のセルに式を入力し ［Enter］ キーを押すことで、式を動的な配列の数式として確定することができます。 それ以外の場合は最初に出力範囲を選択し、出力範囲の左上のセルに数式を入力し、「Ctrl + Shift + Enter」を押して確定し、従来の配列数式として数式を入力する必要があります。その操作によって数式の先頭と末尾に、中かっこを挿入してくれます。 |
4.FILTER関数
条件に合うデータを行ごと抽出したい場合は、FILTER関数を使う方法がおすすめです。
FILTER関数とは、条件に一致する行を抽出する関数ですが、FILTER関数はMicrosoft 365やOffice 2021でなければ使用できないのでご自身のバージョンを確認してくださいね。
従来のフィルター機能と同様、指定した条件に合うデータを抽出できるだけでなく
- 複数条件を指定した抽出が可能
- 別の関数と組み合わせる応用が可能
- 一度関数を組んでしまえば都度の条件の絞り込みが不要になる
など、フィルター機能より汎用性が高い特徴があります。FILTER関数の構文は下記の通りです。
| FILTER(配列,含む,[空の場合]) |
選択した配列またはセル範囲から、指定した条件にあうデータを抽出します。
配列
フィルター処理するセル範囲または配列を必ず指定します。
含む
抽出する条件を必ず指定します。
空の場合
指定した条件にあうデータが空のときに返す値を指定できますが、省略すると「#CALC!」が指定されます。
#CALC! エラーとはあまり聞きなれないかもしれませんが、Excel の計算エンジンで、配列に対して指定されていない計算エラーが発生したときに発生します。 これを解決するには、数式を書き直してみてください。
5.MATCH関数
MATCH関数とは、検索する項目自体ではなく、その項目の位置を調べる場合は、HLOOKUP関数やVLOOKUP 関数の代わりに MATCH関数を使用するのがおすすめです。
たとえば、MATCH関数を活用することでINDEX 関数の行番号引数の値を指定することができます。MATCH関数の構文は次の通りです。
| MATCH(検査値, 検査範囲, [照合の型]) |
MATCH関数の構成要素には次の引数があります。
検索値
必ず指定します。 検査範囲の中で照合する値を指定します。
たとえば、電話番号帳を使ってある人の電話番号を調べるとき、検査値としてその人の氏名を指定しますが、実際に検索するのは電話番号です。検査値には、値 (数値、文字列、または論理値)、またはこれらの値に対するセル参照を指定できます。
検索範囲
必ず検索するセルの範囲を指定します。
照合の型
照合の方は省略可能ですが、必要に応じて「 -1、0、1 」の数値のいずれかを指定してください。照合の型には検査範囲の中で検査値を探す方法を指定するため、引数の既定値は 1 を選んでください。
まとめ
いかがでしたでしょうか。
今回はHLOOKUP関数の使い方や、似たような関数を4つご紹介してきました。
特に、Excelの関数でよく知られているVLOOKUP関数は縦方向に検索しますが、横方向に検索したいときは本記事で紹介したHLOOKUP関数を使用します。
HLOOKUP関数とVLOOKUP関数の違いを理解して、目的に応じて使い分けできるようにしましょう。
