Excelでデータを管理する際、ある値に基づいて別の値を検索することがよくあります。従来はVLOOKUP関数やHLOOKUP関数が使われてきましたが、Excelの新しい関数であるXLOOKUP関数は、それらをより柔軟かつ強力に代替することができます。XLOOKUP関数を使えば、行や列の中からデータを効率的に検索できます。この記事では、XLOOKUP関数の基本から応用まで詳しく解説します。
XLOOKUP関数とは？
XLOOKUP関数は、指定した範囲の中から、特定の値を検索し、その値に対応するデータを返すための関数です。VLOOKUP関数やHLOOKUP関数よりも柔軟で、範囲を超えた検索や前方・後方検索が可能な点が特徴です。
XLOOKUP関数の基本的な構文
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからない場合], [一致モード], [検索モード])
- 検索値: 検索したい値を指定します。
- 検索範囲: 検索値を探す範囲を指定します。
- 戻り値範囲: 検索値に一致するデータが見つかった場合に返す値の範囲を指定します。
- [見つからない場合]（省略可）: 検索値が見つからなかったときに返す値を指定します。指定しない場合、エラーを返します。
- [一致モード]（省略可）: 完全一致、近似一致など、検索の方法を指定します。
- [検索モード]（省略可）: 前から検索、後ろから検索など、検索方向を指定します。
XLOOKUP関数の実際の例
たとえば、次のような商品コードと価格のデータがあるとします。
| 商品コード | 価格 |
| A101 | 500円 |
| A102 | 750円 |
| A103 | 900円 |
この表で商品コード「A102」の価格を検索するには、次のようにXLOOKUP関数を使用します。
=XLOOKUP(“A102”, A2:A4, B2:B4)
結果として、750円が返されます。これは、商品コード「A102」に対応する価格です。
| 商品コード | 価格 | 検索結果 |
| A101 | 500円 | =XLOOKUP(“A102”, A2
, B2 ) → 750円 |
XLOOKUP関数の応用例
データが見つからない場合の対応
XLOOKUP関数では、検索値が見つからない場合に返す値を指定することができます。例えば、商品コード「A104」を検索する場合に、見つからないときは「該当なし」と返すように指定します。
=XLOOKUP(“A104”, A2:A4, B2:B4, “該当なし”)
この場合、商品コード「A104」は存在しないため、「該当なし」が返されます。
完全一致と近似一致の設定
一致モードを使うことで、完全一致や近似一致を指定することができます。例えば、商品コードに類似した値を検索したい場合、次のように入力します。
- 完全一致（デフォルト）：0を指定
- 次に大きい値の近似一致：-1を指定
- 次に小さい値の近似一致：1を指定
=XLOOKUP(“A101”, A2:A4, B2:B4, “該当なし”, 0) // 完全一致
これにより、商品コードが完全に一致するデータのみが検索されます。
前方検索と後方検索
検索モードを使うことで、前方または後方から検索するかを選択できます。例えば、リストの最後から検索したい場合、次のように入力します。
- 前から検索（デフォルト）：1
- 後ろから検索：-1
=XLOOKUP(“A101”, A2:A4, B2:B4, “該当なし”, 0, -1) // 後ろから検索
これにより、リストの後方から検索を開始します。
複数列にわたるデータ検索
XLOOKUP関数は、1つの列に限らず、複数の列にまたがるデータの検索にも対応しています。たとえば、顧客リストの中から氏名に基づいて電話番号と住所を検索する場合、次のようにXLOOKUP関数を使用します。
=XLOOKUP(“鈴木”, A2:A10, B2:C10)
これにより、鈴木さんに対応する電話番号と住所が同時に返されます。
動的範囲を使って柔軟な検索
XLOOKUP関数は、動的な範囲（テーブルや配列）を指定して検索することができ、データが増減しても常に最新の範囲で検索を行うことができます。Excelのテーブル機能を使って範囲を指定すると、自動的に範囲が調整され、柔軟に検索を行うことが可能です。
別シートや別ファイルからの検索
XLOOKUP関数は、別のシートやファイルからデータを検索することも簡単に行えます。たとえば、別のシート「Sheet2」から商品コードに基づいて価格を検索する場合は、次のように入力します。
=XLOOKUP(“A102”, Sheet2!A2:A100, Sheet2!B2:B100)
これにより、別のシートから商品コード「A102」に対応する価格が返されます。
XLOOKUP関数の便利な豆知識
VLOOKUP関数やHLOOKUP関数との違い
XLOOKUP関数は、VLOOKUP関数やHLOOKUP関数の上位互換です。VLOOKUPやHLOOKUPは検索範囲が固定されており、左から右、または上から下への検索しかできませんが、XLOOKUP関数は範囲の左右にかかわらず柔軟に検索が可能です。また、XLOOKUPでは範囲の順序に制約がなく、より効率的にデータを操作できます。
検索モードでデータの並び順に対応
XLOOKUP関数の検索モードを活用することで、昇順や降順に並んだデータの中で、効率的に最適なデータを検索できます。検索モードの設定を調整することで、速度や結果の精度を向上させることが可能です。
配列やテーブルと組み合わせて活用
XLOOKUP関数は、動的配列やExcelのテーブル機能と組み合わせて使用すると、データが変動しても範囲を自動調整して検索できるため、特に大規模なデータを扱う際に強力です。テーブルを使用することで、常に最新のデータに基づいて検索を行うことができます。
XLOOKUP関数のよくあるエラーと対処法
#N/Aエラー
#N/Aエラーは、検索値が範囲内に見つからない場合に発生します。これを防ぐためには、検索値が見つからなかった場合に返す値を指定するオプションを活用しましょう。
=XLOOKUP(“A104”, A2:A4, B2:B4, “該当なし”)
#VALUE!エラー
#VALUE!エラーは、指定した検索範囲や戻り値範囲のサイズが一致していない場合に発生します。検索範囲と戻り値範囲のサイズが一致しているかを確認しましょう。
まとめ
ExcelのXLOOKUP関数は、従来のVLOOKUP関数やHLOOKUP関数よりも柔軟で強力な検索機能を提供します。XLOOKUPを使えば、左右・上下にかかわらず、柔軟なデータ検索やエラー処理が可能です。テーブル機能や動的範囲と組み合わせて使用することで、さらに効率的なデータ処理が行えます。
XLOOKUP関数を活用して、Excelでのデータ検索をさらに便利にしましょう！
