スキップしてメイン コンテンツへ

XLOOKUP 関数

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

使用しているブラウザーではビデオがサポートされていません。 Microsoft Silverlight、Adobe Flash Player、Internet Explorer 9 のいずれかをインストールしてください。

注: 2019 年 11 月 25 日: XLOOKUP は現在、ベータ版の機能であり、Office Insider の一部のみが利用可能です。 今後数か月にわたり、これの最適化を行う予定です。 XLOOKUP の準備が整ったら、すべての Office Insider、Office 365 サブスクリプションをお持ちの方に向けてリリースいたします。

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

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

引数

説明

検索値

必須

参照値

検索範囲

必須

検索する配列または範囲

戻り配列

必須

返す配列または範囲

[見つからない場合]

オプション

有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。

有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。

[一致モード]

オプション

一致の種類を指定します:

0 - 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。

-1 - 完全一致。 見つからない場合は、次の小さなアイテムが返されます。

1 - 完全一致。 見つからない場合は、次の大きなアイテムが返されます。

2 - *、?、および 〜 が特別な意味を持つワイルドカードの一致。

[検索モード]

オプション

使用する検索モードを指定します。

1 - 先頭の項目から検索を実行します。 これが既定の設定です。

1 - 末尾の項目から逆方向に検索を実行します。

2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。

-2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。

使用例

使用例 1

この例は上記のビデオからのものです。簡単な XLOOKUP を使用して国名を検索し、その電話の国コードを返します。 これには、検査値 (セル F2)、検査範囲 (範囲 B2: B11)、および戻り配列 (範囲 D2: D11) の引数のみが含まれます。 XLOOKUP の既定は完全一致であるため、一致モード 引数は含まれません。

従業員 ID に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。 数式: =XLOOKUP(B2,B5:B14,C5:C14)

注: XLOOKUP は VLOOKUP とは異なり、検索配列と戻り配列を個別に使用します。VLOOKUP は単一のテーブル配列とそれに続く列インデックス番号を使用します。 この場合、同等の VLOOKUP 数式は次のようになります。=VLOOKUP(F2,B2:D11,3,FALSE)

使用例 2

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

従業員 IDt に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。 数式: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

使用例 3

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

[見つからない場合] 引数を持つ従業員 ID に基づいて従業員の名前と部署を返すために使用される XLOOKUP 関数の例。 数式: =XLOOKUP(B2,B5:B14,C5:D14,0,1,"従業員が見つかりません")

使用例 4

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

最大収入に基づいて税率を返すために使用される XLOOKUP 関数の画像。 これはおおよその一致です。数式: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

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

使用例 5

次に、ネストされた XLOOKUP 関数を使用して、縦方向と横方向の両方の一致を実行します。 この場合、最初に列 B で粗利益を探し、次にテーブルの最上行 (範囲 C5:F5) で Qtr1 を探し、2 つの共通部分の値を返します。 これは、INDEX 関数と MATCH 関数を併用することと同様です。 XLOOKUP を使用して HLOOKUP 機能を置き換えることもできます。

2 つの XLOOKUP をネストして、表から水平データを返すために使用される XLOOKUP 関数の画像。 数式: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

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

使用例 6

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

XLOOKUP と SUM を使用した 2 つの選択の中間にある値の範囲の合計

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

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

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

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

関連項目

XMATCH 関数

Excel 関数 (アルファベット順)

Excel 関数 (機能別)

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×