INDEX
範囲を可変にして検索を実行するには？
ExcelでXLOOKUPを使用してデータを検索する際、検索範囲を動的に変更したい場合があります。たとえば、検索対象のシートや範囲をユーザーの入力によって切り替えるようなケースです。
このような場合、INDIRECT関数を組み合わせることで、範囲を可変にしながら検索を実行することが可能です。この記事では、XLOOKUPとINDIRECTを活用して、動的にデータを検索する方法について解説します。
可変範囲検索が必要なケース
以下のようなケースで、検索範囲を可変にする必要があります。
- 異なるシートからデータを検索する
- ユーザーが選択したデータ範囲を検索する
- 定期的に変更されるデータ範囲を動的に参照する
このような場合、INDIRECT関数を使うことで、検索範囲を自由に変更できます。
XLOOKUPとINDIRECTを組み合わせた動的検索
通常のXLOOKUPの構文は以下の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲)これをINDIRECTを使って動的に参照できるようにします。
動的範囲検索の実例
以下のようなデータがあり、検索対象のシート名を可変にしたいとします。
| A | B | C | |
|---|---|---|---|
| 1 | 商品コード | 商品名 | 価格 |
| 2 | A001 | りんご | 100 |
| 3 | A002 | バナナ | 150 |
| 4 | A003 | ぶどう | 200 |
| 5 | =XLOOKUP(C6, INDIRECT(A6), INDIRECT(B6)) | ||
| 6 | A2:A4 | B2:B4 | A002 |
ここで、セルA6には検索範囲（例：A2:A4）、セルB6には戻り値範囲（例：B2:B4）を入力します。
設定手順
- セルA6に検索範囲を入力
- セルB6に戻り値範囲を入力
- セルC6に検索したい商品コードを入力
- セルA5に以下の式を入力
=XLOOKUP(C6, INDIRECT(A6), INDIRECT(B6))結果：「バナナ」
異なるシートを動的に検索する
ユーザーが指定したシート名に基づいて検索範囲を変更する場合、次のようにINDIRECTを使用します。
=XLOOKUP("A002", INDIRECT("'"&D1&"'!A2:A4"), INDIRECT("'"&D1&"'!B2:B4"))ここで、D1にシート名（例：”シート2″）を入力すると、”シート2″のA2:A4を検索範囲、B2:B4を戻り値範囲として扱います。
注意点
INDIRECTは参照する範囲を文字列として扱うため、正しいシート名や範囲を入力する必要がある- 外部ブックを参照する場合は、INDIRECT関数が正しく動作しないことがある
- INDIRECTは計算負荷が高いため、大量データの検索には向かない
まとめ
ExcelのXLOOKUPとINDIRECTを組み合わせることで、検索範囲を動的に変更できるようになります。異なるシートやユーザー指定の範囲を参照する際に便利です。ただし、INDIRECTは計算負荷が高いため、使用する場面を考慮することが重要です。
使用した関数について
XLOOKUP関数でデータの柔軟な検索を行う方法についてわかりやすく解説
XLOOKUP関数についてXLOOKUPの概要データの柔軟な検索Excel関数=XLOOKUP( 検索値 , 検索範囲 , 戻り値の範囲 ,,, )概要 XLOOKUP関数は、指定した範囲内で検索値を探し、それに対応する値を別の範囲から返します。従来のVLOOKUPに比べて、より柔軟で使いやすいのが特徴です。 指定した...
programming-cafe.com
INDIRECT関数で数値かどうかを判定する方法についてわかりやすく解説
INDIRECT関数についてINDIRECTの概要文字列で指定された参照を返すExcel関数=INDIRECT( 参照 )概要 INDIRECT関数は、指定された文字列をセル参照に変換し、そのセルの値を返します。間接的にセルや範囲を参照するため、動的な範囲指定に便利です。 INDIRECT関数は、セル参照を動的に扱いた...
programming-cafe.com