ExcelのXLOOKUP関数は、指定したキーを検索し、対応する値を返すための強力なツールです。従来のVLOOKUP関数やHLOOKUP関数に代わるもので、より柔軟で簡単に使用できるのが特徴です。このブログでは、XLOOKUP関数の基本的な使い方と具体的な例を交えて紹介します。
ExcelでXLOOKUP関数を使用してデータを検索する方法
XLOOKUP関数の基本
XLOOKUP関数は、指定した範囲内で検索値を探し、その結果に基づいて対応する範囲から値を返します。
構文
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])- 検索値: 検索する値
- 検索範囲: 検索する範囲
- 戻り範囲: 値を返す範囲
- 見つからない場合: (オプション) 見つからない場合に返す値
- 一致モード: (オプション) 完全一致または部分一致を指定
- 検索モード: (オプション) 検索の方向を指定
基本例
以下の例では、範囲A2:A10の中で検索値「Apple」を探し、対応するB2:B10の値を返します：
=XLOOKUP("Apple", A2:A10, B2:B10)応用例
例1: 一致モードと検索モードの使用
次の数式では、範囲A2:A10の中で「Banana」を探し、対応するB2:B10の値を返します。見つからない場合は「Not Found」を返し、完全一致を指定し、上から下に検索します：
=XLOOKUP("Banana", A2:A10, B2:B10, "Not Found", 0, 1)一致モード
一致モードは、検索値が検索範囲内でどのように一致するかを指定します。以下のオプションがあります：
- 0 (完全一致): 検索値が検索範囲内で完全に一致する位置を返します。これはデフォルトの設定です。
- -1 (完全一致または次に小さい値): 検索値と完全に一致するか、次に小さい値を返します。検索範囲は昇順に並んでいる必要があります。
- 1 (完全一致または次に大きい値): 検索値と完全に一致するか、次に大きい値を返します。検索範囲は昇順に並んでいる必要があります。
- 2 (ワイルドカード一致): 検索値としてワイルドカード文字（例えば、
*や?）を使用して、一部一致を許可します。
例
以下の数式では、「Banana」を検索範囲A2:A10で完全一致（0）で検索し、B2:B10の値を返します：
=XLOOKUP("Banana", A2:A10, B2:B10, "Not Found", 0)この数式では、一致する値が見つからない場合、「Not Found」を返します。
検索モード
検索モードは、検索がどの方向で行われるかを指定します。以下のオプションがあります：
- 1 (先頭から検索): デフォルトの設定で、上から下へ検索します。
- -1 (末尾から検索): 下から上へ検索します。
- 2 (2進探索昇順): 昇順に並べ替えられた範囲での2進探索を行います。高速な検索を実現します。
- -2 (2進探索降順): 降順に並べ替えられた範囲での2進探索を行います。
例
以下の数式では、「Banana」を検索範囲A2:A10で検索し、見つからない場合に「Not Found」を返し、上から下へ検索します（1）：
=XLOOKUP("Banana", A2:A10, B2:B10, "Not Found", 0, 1)応用例：シートをまたいだ検索
INDIRECT関数を組み合わせることで、他のシートのセルを参照することもできます。例えば、シート「Sheet1」のセルA1に「Sheet2!A2:A10」と入力し、シート「Sheet2」の範囲A2:A10にデータがある場合、次のようにしてシートをまたいだ検索が可能です：
=XLOOKUP("Orange", INDIRECT("Sheet2!A2:A10"), INDIRECT("Sheet2!B2:B10"))サンプルデータ
以下に、XLOOKUP関数の一致モードと検索モードの使用例を示したサンプルデータを提供します。このデータをExcelにコピーして、関数の練習に使用してください。
Excel形式データダウンロード
以下は、Excelデータですので、ダウンロードして練習などに使用してください。
Excel-x001-1.xlsx （ダウンロード）
メインシート（Excel列名との対応）
Sheet1
| 行 | A | B | C | D |
|---|---|---|---|---|
| 1 | 製品名 | 価格 | 在庫数 | 結果 |
| 2 | Apple | 100 | 50 | =XLOOKUP(“Apple”, A2:A10, B2:B10) |
| 3 | Banana | 80 | 30 | =XLOOKUP(“Banana”, A2:A10, B2:B10, “Not Found”, 0, 1) |
| 4 | Orange | 60 | 20 | |
| 5 | Pineapple | 150 | 25 | |
| 6 | Mango | 120 | 40 | |
| 7 | Grapes | 90 | 35 | |
| 8 | Kiwi | 110 | 45 | |
| 9 | Melon | 200 | 15 | |
| 10 | Peach | 140 | 25 |
説明
このサンプルデータでは、特定の製品名に基づいてデータを検索し、価格を表示する方法を示しています。以下の数式を使用して、特定の条件に一致するデータを取得します：
- 基本例: =XLOOKUP(“Apple”, A2:A10, B2:B10) は、A2:A10の中で「Apple」を検索し、対応するB2:B10の値を返します。
- 応用例: =XLOOKUP(“Banana”, A2:A10, B2:B10, “Not Found”, 0, 1) は、「Banana」が見つからない場合に「Not Found」を返し、完全一致を指定します。
CSV形式
以下は、上記のデータをカンマ区切りで記載したものです。このデータをコピーしてExcelに貼り付けて使用してください。
"製品名","価格","在庫数","結果"
"Apple","100","50","=XLOOKUP(""Apple"", A2:A10, B2:B10)"
"Banana","80","30","=XLOOKUP(""Banana"", A2:A10, B2:B10, ""Not Found"", 0, 1)"
"Orange","60","20",""
"Pineapple","150","25",""
"Mango","120","40",""
"Grapes","90","35",""
"Kiwi","110","45",""
"Melon","200","15",""
"Peach","140","25",""まとめ
ExcelでXLOOKUP関数を使用してデータを検索する方法について説明しました。XLOOKUP関数は、指定した検索値を範囲内で検索し、対応する値を返すために非常に便利です。このブログを参考にして、Excelでのデータ検索を効率的に行ってください。
このブログが役に立ちましたら幸いです。さらにサポートが必要なことや質問があれば、お知らせください。Happy Excel-ing! 😊
他にもサポートが必要であれば、どんなことでもお知らせください。
コメント