x001｜Excelで「XLOOKUP関数」を使用してデータを検索する方法

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! 😊

他にもサポートが必要であれば、どんなことでもお知らせください。