範囲を可変にして検索を実行するには？
ExcelでXLOOKUP関数を使用する際、検索する範囲を状況に応じて変更したいことがあります。例えば、ユーザーが選択したシートを検索したり、入力された範囲を参照したりするようなケースです。
このような場合は、INDIRECT関数を組み合わせることで、文字列で指定したセル範囲を参照できるようになります。この記事では、XLOOKUPとINDIRECTを組み合わせて検索範囲を動的に変更する方法を解説します。
検索範囲を可変にしたい場面
次のようなケースでは、検索範囲を固定せず変更できるようにすると便利です。
- 複数のシートから検索対象を切り替えたい
- ユーザーが指定した範囲を検索したい
- 検索対象となる表が定期的に変更される
- 同じ数式で異なるデータ範囲を検索したい
INDIRECT関数を利用すると、セルに入力された文字列を実際のセル参照として扱えるため、このような柔軟な検索が可能になります。
XLOOKUPとINDIRECTを組み合わせる基本形
通常のXLOOKUP関数は、検索範囲と戻り値範囲を直接指定します。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲)一方、INDIRECTを利用すると、文字列で入力された範囲を参照できます。
=XLOOKUP(検索値, INDIRECT(検索範囲), 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には検索範囲、セルB6には戻り値範囲を文字列として入力しています。
設定手順
- セルA6に「A2:A4」のような検索範囲を入力する
- セルB6に「B2:B4」のような戻り値範囲を入力する
- セルC6に検索したい商品コードを入力する
- セルA5に次の数式を入力する
=XLOOKUP(C6,INDIRECT(A6),INDIRECT(B6))セルC6に「A002」と入力すると、「バナナ」が表示されます。
シートを切り替えて検索する
シート名も動的に変更したい場合は、シート名を入力したセルとINDIRECTを組み合わせます。
例えば、セルD1に検索したいシート名を入力している場合は、次のように記述します。
=XLOOKUP("A002",INDIRECT("'"&D1&"'!A2:A4"),INDIRECT("'"&D1&"'!B2:B4"))セルD1に「シート2」と入力すると、シート2のA2:A4を検索範囲、B2:B4を戻り値範囲として検索が行われます。
シート名だけを変更するだけで検索対象を切り替えられるため、月別データや部署別データなどを扱う際に便利です。
テーブルを利用する場合との違い
検索範囲が単純に追加・削除されるだけであれば、Excelのテーブル機能を利用する方が適しています。テーブルではデータを追加すると参照範囲が自動的に拡張されるため、INDIRECTを使用する必要はありません。
一方、検索対象そのものを切り替えたい場合や、参照するシートを変更したい場合には、INDIRECTを利用する方法が適しています。
注意点
INDIRECTは文字列をセル参照へ変換するため、シート名や範囲名の入力ミスがあるとエラーになります。- 参照先のブックが閉じられている場合、通常の
INDIRECTでは外部ブックを参照できません。 INDIRECTは揮発性関数のため、ブック内の変更があるたびに再計算されます。大量のデータで多用すると処理速度が低下することがあります。- 検索範囲が頻繁に変わらない場合は、テーブルや名前付き範囲を利用した方が管理しやすい場合もあります。
まとめ
XLOOKUPとINDIRECTを組み合わせることで検索範囲を動的に変更できる。- セルに入力された範囲やシート名をそのまま検索対象として利用できる。
- 複数シートを切り替える帳票や月別データの検索に便利。
- 大量のデータでは
INDIRECTによる再計算負荷に注意する。 - 単純な範囲拡張だけであれば、テーブル機能の利用も検討するとよい。
使用した関数について
