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 を選んでください。
