こちらの記事では、エクセルの行列検索に焦点を当てて、VLOOKUP、HLOOKUP、XLOOKUP関数の活用方法について詳しく解説します。行列検索のためにこれらの関数を使用すれば、指定された範囲内で条件に合致するデータを検索することができます。
これらの方法を習得しておけば、大規模なデータをあつかう際に効率的な方法で業務にあたることができます。この機会に基本的な使い方をおさえておきましょう。
VLOOKUP関数｜縦方向の検索
VLOOKUP関数は、垂直方向にデータを検索するための関数です。指定した範囲の中から、検索条件に一致するデータを見つけ、他の列から該当行の値を返します。
VLOOKUP関数の構文はこちらです。
=VLOOKUP(検索値, 範囲, 列番号, 検索の型)
- 検索値： 検索する値
- 範囲： 検索するセル範囲
- 列番号： 範囲内の戻り値が存在する列番号
- 検索の型： 検索する値が一致する場合はFALSE、近似値の場合はTRUE（省略した場合はTRUE）
例えば、品名をもとに商品の売上数を検索する場合、以下のように使用します。
=VLOOKUP(B11,B1:G6,6,FALSE)
こちらの例では、セルB11に入力された品名をもとに、B列からG列までの範囲で検索して、6列目（G列）にある合計売上数を表示します。
スポンサーリンク
HLOOKUP関数｜横方向の検索
HLOOKUP関数は、水平（横方向）にデータを検索するための関数です。指定された範囲の中から、検索条件に一致するデータを見つけ、他の行から該当列の値を返します。
HLOOKUP関数の構文はこちらです。
=HLOOKUP(検索値, 範囲, 行番号, 検索の型)
- 検索値： 検索する値
- 範囲： 検索するセル範囲
- 行番号： 範囲内の戻り値が存在する行番号
- 検索の型： 検索する値が一致する場合はFALSE、近似値の場合はTRUE（省略した場合はTRUE）
例えば、売上月をもとに商品の売上数を検索する場合、以下のように使用します。
=HLOOKUP(B11,D1:F7,7,FALSE)
こちらの例では、セルB11に入力された列名をもとに、D列からF列までの範囲で検索して、7行目にある合計数を表示します。
スポンサーリンク
XLOOKUP関数｜縦横方向の検索
XLOOKUP関数は、VLOOKUP関数、HLOOKUP関数の上位互換とも言える関数です。検索方向を垂直・水平ともに指定でき、より柔軟にデータを検索することができます。また、VLOOKUP関数、HLOOKUP関数の弱点とも言える「検索値を左端や上端に設定しなくてはならない制約」に対しての解決策としても活用することができます。
ただし、XLOOKUP関数は、Excel2021以降から使用できる関数ですので注意が必要です。作成したエクセルファイルを送付する場合にも、バージョンによっては相手方に表示されないケースがありますので留意しておきましょう。
XLOOKUP関数の構文はこちらです。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, 見つからない場合の値, 検索モード, 一致モード)
- 検索値： 検索する値
- 検索範囲： 検索するセル範囲
- 戻り値範囲： 表示する配列または範囲
- 見つからない場合の値： テキストの指定（省略した場合は「#N/A」が表示）
- 一致モード： 0：完全一致。-1：完全一致（見つからない場合、検索値の次に小さい値）。1：完全一致（見つからない場合、検索値の次に大きい値）。2：ワイルドカードの一致。（省略した場合は「0」が自動設定）
- 検索モード： 1：先頭項目から検索。-1：末尾項目から検索。2：昇順で検索。-2：降順で検索。（省略した場合は「1」が自動設定）
例えば、品名をもとに、検索値の左側にあたる列で検索をしてNoを表示します。
=XLOOKUP(B11,B2:B6,A2:A6)
こちらの例では、セルB11に入力された品名をもとに、B列で検索をして、該当する値をもとにA列で値を検索してNoを表示させています。
スポンサーリンク
INDEX関数とMATCH関数｜XLOOKUP関数の代替
使用されているエクセルがExcel2021よりも前のバージョンの場合、XLOOKUP関数を使用することができません。しかし、同じ目的を達成するための代替方法として、INDEX関数とMATCH関数を組み合わせることによって、XLOOKUPと同様の範囲検索をおこなうことが可能です。
以下にて使用方法をご説明します。
まず、INDEX関数の基本構文を確認しましょう。
=INDEX(範囲, 行番号, 列番号)
- 範囲： データの範囲
- 行番号： 戻り値が存在する行番号
- 列番号： 戻り値が存在する列番号（省略可能）
つぎに、MATCH関数の基本構文を確認します。
=MATCH(検索値, 検索範囲, 一致の種類)
- 検索値： 検索する値
- 検索範囲： 検索するセル範囲
- 一致の種類： 0=完全一致。1=以下。-1=以上。（通常は0を使用）
これらの関数を使用して、範囲検索をおこないます。
例えば、品名をもとに、検索値の左側にあたる列で検索をしてNoを表示します。
=INDEX(A:A,MATCH(B11,B:B,0))
こちらの例では、まずMATCH関数を使用してセルB11に入力された品名をもとに、行番号「6」を特定します。そして、その行番号「6」をつかって、INDEX関数でA列から6行目を検索することによってNoを表示させています。
INDEX関数の設定です。
MATCH関数の設定です。
まとめ
特に大規模なデータをあつかう際に、今回ご紹介した関数をうまく活用することで日々の業務効率を向上させることができます。まだこれらの関数を取り入れていないようでしたら、ぜひ、この機会にVLOOKUP関数、HLOOKUP関数、XLOOKUP関数を活用してみてください。
