XLOOKUP

XLOOKUP 関数

XLOOKUP 関数を使用して、表や範囲から行ごとに情報を検索します。たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。 XLOOKUP を使用すると、1 つの列で検索語を検索し、戻り列がどちら側にあるかに関係なく、別の列の同じ行から結果を返すことができます。

注: XLOOKUP は Excel 2016 および Excel 2019 では使用できませんが、新しいバージョンの Excel を使用して他の人が作成した XLOOKUP 関数を含む Excel 2016 または Excel 2019 のブックを使用する状況に遭遇する可能性があります。

書式

XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

引数	説明
検索値必須*	検索する値は

*省略した場合、XLOOKUPは検索範囲 に空白のセルを返します。
検索範囲 必須 | 検索する配列または範囲
戻り配列 必須 | 返す配列または範囲
[見つからない場合] オプション | 有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。
[一致モード] オプション | 一致の種類を指定します: 0 – 完全一致。見つからない場合は、#N/A が返されます。これが既定の設定です。 -1 – 完全一致。見つからない場合は、次の小さなアイテムが返されます。 1 – 完全一致。見つからない場合は、次の大きなアイテムが返されます。 2 – *、?、および〜が target=”_blank” title=”特別な意味” rel=”noopener”>特別な意味を持つワイルドカードの一致。
[検索モード] オプション | 使用する検索モードを指定します。 1 – 先頭の項目から検索を実行します。これが既定の設定です。 -1 – 末尾の項目から逆方向に検索を実行します。 2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。 -2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

使用例

例 1 は XLOOKUP を使用して範囲内の国名を検索して、その国際電話国/地域コードを返します。これには、検索値 (セル F2)、検査範囲 (範囲 B2: B11)、および戻り配列 (範囲 D2: D11) の引数が含まれます。 XLOOKUP は既定では完全一致を生成するため、一致モード 引数は含まれません。

注: XLOOKUP は検索範囲と戻り配列を使用するのに対し、VLOOKUP は列インデックス番号に続く単一の表の配列を使用します。この場合、同等の VLOOKUP 数式は次のようになります。=VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

例 2 では、従業員 ID 番号に基づいて従業員情報を検索します。 VLOOKUP とは異なり、XLOOKUP は複数のアイテムを含む配列を返すことができます。これにより、1 つの数式で、セル C5: D14 の従業員名と部署の両方を返すことができます。

———————————————————————————

例 3 では [見つからない場合] 引数を前の例に追加します。

———————————————————————————

例 4 では、列 C のセル E2 に入力された個人の収入を調べ、列 B で一致する税率を見つけます。何も見つからない場合には 0 (ゼロ) を返すように [見つからない場合] 引数を設定します。 [一致モード] 引数は 1 に設定されます。つまり、関数は完全一致を検索します。一致が見つからない場合は、次の大きなアイテムが返されます。最後に、[検索モード] 引数は 1 に設定されます。つまり、関数は最初のアイテムから最後のアイテムまで検索します。

注: XARRAY の検査範囲 の列は戻り配列 の列の右にあります。VLOOKUP は、左から右にしか表示されません。

———————————————————————————

例 5 ネストされた XLOOKUP 関数を使用して、縦方向と横方向の両方の一致を実行します。最初に列 B で粗利益 を探し、次にテーブルの最上行（範囲 C5:F5）で Qtr1 を探し、最後に2 つの共通部分の値を返します。これは、 target=”_blank” title=”INDEX” rel=”noopener”>INDEX 関数と target=”_blank” title=”MATCH” rel=”noopener”>MATCH 関数を一緒に使用することと同様です。

ヒント: XLOOKUP を使用して target=”_blank” title=”HLOOKUP” rel=”noopener”>HLOOKUP 機能を置き換えることもできます。

注: セル D3:F3 の数式は、=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) です。

———————————————————————————

例 6 target=”_blank” title=”SUM 関数” rel=”noopener”>SUM 関数と、ネストされた 2 つの XLOOKUP 関数を使用して、2 つの範囲間のすべての値を合計します。この場合、ブドウとバナナの値を合計し、2 つの間にある梨を含めます。

セル E3 の数式: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

動作のしくみ XLOOKUP は範囲を返すため、計算すると、数式は =SUM($E$7:$E$9) のようになります。これに類似した XLOOKUP 式のセルを選択し、[数式]、[ワークシート分析]、[ target=”_blank” title=”数式の検証” rel=”noopener”>数式の検証] の順に選択し、[検証] を選択すると、この数式がどのように動作するかを確認できます。

注: この例を紹介してくれた Microsoft Excel MVP の Bill Jelen 氏に感謝いたします。

———————————————————————————

XLOOKUP 関数

書式

使用例

関連項目