Excelで新しく高度なXLOOKUP関数を使用する方法（10の例）

Excelの新しいXLOOKUPは、Excelが提供できる最も強力で簡単な検索関数です。Microsoftは、VLOOKUP、HLOOKUP、INDEX+MATCH、およびその他の検索関数を置き換えるために、ついにこれをリリースしました。

このチュートリアルでは、XLOOKUPの利点と、それを取得して異なる検索問題を解決する方法を紹介します。

XLOOKUPを取得する方法

構文

例

• 例1: 完全一致
  • XLOOKUPで完全一致を実行する
  • 完全一致を得るための数クリック

• 例2:近似一致
  • XLOOKUPで近似一致を実行する

• 例3: ワイルドカード一致
  • XLOOKUPでワイルドカード一致を実行する

• 例4:右から左への検索
  • XLOOKUPを使用して右から左に検索する
  • 数クリックで右から左に値を検索する

• 例5: 垂直または水平検索
  • XLOOKUPで垂直または水平検索を実行する

• 例6: 双方向検索
  • XLOOKUPで双方向検索を実行する

• 例7: 見つからないメッセージをカスタマイズする
  • XLOOKUPを使用して見つからないメッセージをカスタマイズする
  • 便利な機能で#N/Aエラーをカスタマイズする

• 例8: 複数の値
  • XLOOKUPで複数の値を返す

• 例9: 複数の条件
  • XLOOKUPを使用して複数条件検索を実行する
  • 迅速な方法で複数条件検索を行う

• 例10: 最後の一致で値を見つける
  • XLOOKUPで最後の一致結果を取得する
  • 素晴らしいツールで最後の一致値を簡単に検索する

XLOOKUPサンプルファイルをダウンロードする

XLOOKUPを取得する方法

XLOOKUP関数はMicrosoft365用Excel、Excel2021以降のバージョン、およびExcel for the webでのみ利用可能です。Excel2019以前を使用している場合は、XLOOKUPにアクセスするためにアップグレードを検討してください。

構文

範囲または配列を検索し、最初の一致結果の値を返します。構文は以下の通りです:

引数:

1. Lookup_value (必須): 探している値。テーブル範囲の任意の列に存在する可能性があります。
2. Lookup_array (必須): 検索値を探す配列または範囲。
3. Return_array (必須): 値を取得したい配列または範囲。
4. If_not_found (オプション): 有効な一致が見つからない場合に返す値。[if_not_found]でテキストをカスタマイズして一致がないことを示すことができます。
   そうでない場合、返される値はデフォルトで#N/Aになります。
5. Match_mode (オプション): lookup_valueをlookup_array内の値とどのように一致させるかを指定できます。
   • 0 (デフォルト) = 完全一致。一致が見つからない場合は#N/Aを返します。
   • -1 = 完全一致。一致が見つからない場合は次に小さい値を返します。
   • 1 = 完全一致。一致が見つからない場合は次に大きい値を返します。
   • 2 = 部分一致。ワイルドカード文字（*, ?、~）を使用してワイルドカード一致を実行します。
6. Search_mode (オプション): 実行する検索順序を指定できます。
   • 1 (デフォルト) = lookup_array内の最初の項目から最後の項目までlookup_valueを検索します。
   • -1 = lookup_array内の最後の項目から最初の項目までlookup_valueを検索します。lookup_array内の最後の一致結果を取得する必要がある場合に役立ちます。
   • 2 = 昇順にソートされたlookup_arrayを必要とするバイナリ検索を実行します。ソートされていない場合、返される結果は無効になります。
   • -2 = 降順にソートされたlookup_arrayを必要とするバイナリ検索を実行します。ソートされていない場合、返される結果は無効になります。

引数の詳細情報については、以下の手順を行ってください:

1. 空のセルに以下の構文を入力してください。括弧の片側だけ入力する必要があります。

2. Ctrl+Aを押すと、関数引数を表示するプロンプトボックスが表示されます。そして、括弧のもう一方が自動的に完了します。

3. データパネルを下に引き出すと、XLOOKUPの6つの関数引数がすべて表示されます。

>>>

例

XLOOKUPの基本原則をマスターしたことを確信しています。実際のXLOOKUPの例にすぐに入りましょう。

例1: 完全一致

XLOOKUPで完全一致を実行する

VLOOKUPを使用するたびに完全一致モードを指定しなければならないことにイライラしたことはありませんか？幸運にも、この問題は素晴らしいXLOOKUP関数を試すときにはもう存在しません。デフォルトで、XLOOKUPは完全一致を生成します。

さて、オフィス用品の在庫リストがあり、例えばマウスの単価を知りたい場合は、以下の手順を行ってください。

以下の数式を空のセルF2に入力し、Enterキーを押して結果を取得します。

=XLOOKUP(E2,A2:A10,C2:C10)

高度なXLOOKUP数式でマウスの単価を知ることができました。マッチコードはデフォルトで完全一致に設定されているため、指定する必要はありません。VLOOKUPよりもはるかに簡単で効率的です。

数クリックで完全一致を得る

おそらく、Excelの低いバージョンを使用していて、Excel2021またはMicrosoft365にアップグレードする予定がないかもしれません。この場合、便利な機能「範囲内でデータを検索する」。この機能を使用すると、複雑な数式やXLOOKUPへのアクセスなしで結果を得ることができます。

1. 一致した結果を置くセルをクリックします。

2. 「Kutools」タブに移動し、「関数ヘルパー」をクリックし、ドロップダウンリストで「関数ヘルパー」をクリックします。

3. 関数ヘルパーダイアログボックスで、以下のように設定してください:

• 「関数の種類」セクションで「検索」を選択します;
• 「関数を選択」セクションで「範囲内でデータを検索する」を選択します;
• 「引数の入力」セクションで以下の手順を行ってください:
  • 「テーブル範囲」ボックスで、検索値と結果値を含むデータ範囲を選択します;
  • 「検索値」ボックスで、検索している値のセルまたは範囲を選択します。テーブル範囲の最初の列にある必要がありますので注意してください;
  • 「列」ボックスで、一致した値を返す列を選択します。

4. OKボタンをクリックして結果を取得します。

例2.近似一致

XLOOKUPで近似一致を実行する

近似検索を実行するには、5番目の引数で一致モードを1または-1に設定する必要があります。正確な一致が見つからない場合、次に大きいまたは小さい値を返します。

この場合、スタッフの所得税率を知る必要があります。スプレッドシートの左側には2021年の連邦所得税の階層があります。スタッフの税率を列Eで取得するにはどうすればよいでしょうか？心配しないでください。以下の手順を行ってください:

1. 以下の数式を空のセルE2に入力し、Enterキーを押して結果を取得します。
次に、必要に応じて返された結果の書式を変更します。

=XLOOKUP(D2,B2:B8,A2:A8,,1)

>>>

√ 注: 第4引数[If_not_found]はオプションなので、省略しました。

2. セルD2の税率を知ることができました。残りの結果を得るには、lookup_arrayとreturn_arrayのセル参照を絶対に変換する必要があります。

• セルE2をダブルクリックして数式=XLOOKUP(D2,B2:B8,A2:A8,,1)を表示します;
• 数式内の検索範囲B2:B8を選択し、F4キーを押して$B$2:$B$8を取得します;
• 数式内の返却範囲A2:A8を選択し、F4キーを押して$A$2:$A$8を取得します;
• Enterボタンを押してセルE2の結果を取得します。

>>>

3. 次に、フィルハンドルを下にドラッグしてすべての結果を取得します。

√ 注:

• キーボードのF4キーを押すと、行と列の前にドル記号を追加してセル参照を絶対参照に変更できます。
• 検索と返却範囲に絶対参照を適用した後、セルE2の数式をこのバージョンに変更しました:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

• セルE2からフィルハンドルを下にドラッグすると、列Eの各セルの数式はlookup_valueの側面だけが変更されます。
  例えば、E13の数式は次のように変更されました:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

例3: ワイルドカード一致

XLOOKUPでワイルドカード一致を実行する

XLOOKUPワイルドカード一致関数を調べる前に、まずワイルドカードが何であるかを見てみましょう。

Microsoft Excelでは、ワイルドカードは任意の文字を置き換えることができる特別な種類の文字です。部分一致検索を行いたい場合に特に役立ちます。

ワイルドカードには3種類あります: アスタリスク(*), クエスチョンマーク(?), ティルダ(~)。

• アスタリスク(*)はテキスト内の任意の数の文字を表します;
• クエスチョンマーク(?)はテキスト内の任意の単一文字を表します;
• ティルダ(~)はワイルドカード(*, ? ~)を文字として変換するために使用されます。ワイルドカードの前にティルダ(~)を置いてこの機能を実現します;

ほとんどの場合、XLOOKUPワイルドカード一致関数を実行する際には、アスタリスク(*)文字を使用します。ワイルドカード一致がどのように機能するかを見てみましょう。

例えば、アメリカ最大の50社の株式市場時価総額のリストがあり、いくつかの会社の時価総額を知りたいが、会社名が短縮されている場合、これはワイルドカード一致に最適なシナリオです。ステップバイステップでトリックを行ってください。

√ 注: ワイルドカード一致を実行するために最も重要なのは、5番目の引数[match_mode]を2に設定することです。

1. 以下の数式を空のセルH3に入力し、Enterキーを押して結果を取得します。

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

>>>

2. セルH3の結果を知ることができました。残りの結果を得るには、lookup_arrayとreturn_arrayを固定する必要があります。配列にカーソルを置いてF4キーを押します。次に、H3の数式は次のようになります:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. フィルハンドルを下にドラッグしてすべての結果を取得します。

√ 注:

• セルH3の数式のlookup_valueは"*"&G3&"*"です。アスタリスクワイルドカード(*)を値G3とアンパサンド(&)を使用して連結します。
• 第4引数[If_not_found]はオプションなので、省略しました。

例4: 左を検索する

XLOOKUPを使用して右から左に検索する

VLOOKUPの欠点の一つは、検索列の右側に検索を行うことに制約されていることです。検索列の左側の値を探そうとすると、#N/Aエラーが発生します。心配しないでください。XLOOKUPはこの問題を解決するための完璧な検索関数です。

XLOOKUPは検索列の左または右に値を検索するように設計されています。制限がなく、Excelユーザーのニーズを満たします。以下の例でトリックを紹介します。

国の電話コードのリストがあり、既知の電話コードで国名を検索したいとします。

列Cを検索し、列Aの値を返す必要があります。以下の手順を行ってください:

1. 空のセルG2に以下の数式を入力します。

=XLOOKUP(F2,C2:C11,A2:A11)

2. Enterキーを押して結果を取得します。

√ 注: XLOOKUPの左検索機能は、IndexとMatchを置き換えて左側の値を検索することができます。

数クリックで右から左に値を検索する

数式を覚えたくない人のために、ここで便利な機能「右から左に検索」をお勧めします。この機能を使用すると、数秒で右から左に検索を実行できます。

1. Excelの「Kutools」タブに移動し、「スーパーLOOKUP」を見つけ、ドロップダウンリストで「右から左に検索」をクリックします。

2. 「右から左に検索」ダイアログボックスで、以下のように設定してください:

• 「検索対象値の範囲と出力範囲」セクションで、検索範囲と出力範囲を指定します;
• 「データ範囲」セクションで、データ範囲を入力し、「キーカラム」と「返却列」を指定します;

3. OKボタンをクリックして結果を取得します。

例5: 垂直または水平検索

XLOOKUPで垂直または水平検索を実行する

Excelユーザーとして、VLOOKUPとHLOOKUP関数に慣れているかもしれません。VLOOKUPは列内で垂直に検索し、HLOOKUPは行内で水平に検索します。

新しいXLOOKUPはそれらを両方組み合わせており、垂直検索または水平検索を実行するために1つの構文を使用するだけで済みます。天才的ですね。

以下の例で、垂直または水平に検索を実行するために1つのXLOOKUP構文を使用する方法を説明します。

垂直検索を実行するには、空のセルE2に以下の数式を入力し、Enterキーを押して結果を取得します。

=XLOOKUP(E1,A2:A13,B2:B13)

水平検索を実行するには、空のセルP2に以下の数式を入力し、Enterキーを押して結果を取得します。

=XLOOKUP(P1,B1:M1,B2:M2)

ご覧の通り、構文は同じです。2つの数式の唯一の違いは、垂直検索では列を入力し、水平検索では行を入力することです。

例6: 双方向検索

XLOOKUPで双方向検索を実行する

まだINDEXとMATCH関数を使用して二次元テーブルで値を検索していますか？改善されたXLOOKUPを試して、より簡単に仕事を完了させましょう。

XLOOKUPは二重検索を実行し、2つの値の交差点を見つけることができます。1つのXLOOKUPを別のXLOOKUPの中にネストすることで、内側のXLOOKUPは行または列全体を返し、この返された行または列が外側のXLOOKUPに返却配列として入力されます。

例えば、異なる科目の学生の成績リストがあり、Kimの化学科目の成績を知りたいとします。

魔法のようなXLOOKUPを使用してトリックを行う方法を見てみましょう。

• 「内側」のXLOOKUPを実行して列全体の値を返します。XLOOKUP(H2,B1:E1,B2:E10)は化学の成績範囲を取得できます。
• 「内側」のXLOOKUPを「外側」のXLOOKUPの中にネストし、完全な数式で「内側」のXLOOKUPを返却配列として使用します。
• 次に、最終的な数式が登場します:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

• 上記の数式を空のセルH3に入力し、Enterボタンを押して結果を取得します。

または、逆の方法で行うこともできます。「内側」のXLOOKUPを使用して行全体の値を返し、Kimのすべての科目の成績を取得します。次に「外側」のXLOOKUPを使用して、Kimのすべての科目の成績の中から化学の成績を検索します。

• 以下の数式を空のセルH4に入力し、Enterボタンを押して結果を取得します。

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

XLOOKUPの双方向検索機能は、垂直および水平検索機能の完璧な説明でもあります。試してみたい場合はどうぞ！

例7: 見つからないメッセージをカスタマイズする

XLOOKUPを使用して見つからないメッセージをカスタマイズする

他の検索関数と同様に、一致が見つからない場合、#N/Aエラーメッセージが返されます。これは一部のExcelユーザーにとって混乱を招くかもしれません。しかし、良いニュースは、XLOOKUP関数の第4引数でエラー処理が利用可能であることです。

組み込みの[if_not_found]引数を使用して、#N/A結果を置き換えるカスタムメッセージを指定できます。必要なテキストをオプションの第4引数に入力し、テキストを二重引用符（"）で囲みます。

例えば、都市デンバーが見つからない場合、XLOOKUPは#N/Aエラーメッセージを返します。しかし、第4引数を「一致なし」というテキストでカスタマイズした後、数式はエラーメッセージの代わりに「一致なし」テキストを表示します。

以下の数式を空のセルF3に入力し、Enterボタンを押して結果を取得します。

=XLOOKUP(E2,A2:A11,C2:C11,"一致なし")

便利な機能で#N/Aエラーをカスタマイズする

カスタムメッセージで#N/Aエラーを迅速に上書きするには、Kutools for ExcelはExcelで役立つツールです。組み込みの「空白または特定の値で0または#N/Aを置換」機能を使用すると、複雑な数式やXLOOKUPへのアクセスなしで見つからないメッセージを指定できます。

1. Excelの「Kutools」タブに移動し、「スーパーLOOKUP」を見つけ、ドロップダウンリストで「空白または特定の値で0または#N/Aを置換」をクリックします。

2. 「空白または特定の値で0または#N/Aを置換」ダイアログボックスで、以下のように設定してください:

• 「検索対象値の範囲と出力範囲」セクションで、検索範囲と出力範囲を選択します;
• 次に「空白または特定の値で0または#N/Aを置換」オプションを選択し、好きなテキストを入力します;
• 「データ範囲」セクションで、データ範囲を選択し、「キーカラム」と「返却列」を指定します。

3. OKボタンをクリックして結果を取得します。一致が見つからない場合、カスタマイズされたメッセージが表示されます。

例8: 複数の値

XLOOKUPで複数の値を返す

XLOOKUPのもう一つの利点は、同じ一致に対して複数の値を同時に返す能力です。最初の結果を得るために1つの数式を入力すると、他の返された値が隣接する空のセルに自動的にスピルします。

以下の例では、学生ID「FG9940005」に関するすべての情報を取得したいとします。トリックは、数式の返却配列として単一の列または行ではなく範囲を提供することです。この場合、返却配列範囲はB2:D9で、3つの列が含まれています。

以下の数式を空のセルG2に入力し、Enterキーを押してすべての結果を取得します。

=XLOOKUP(F2,A2:A9,B2:D9)

すべての結果セルは同じ数式を表示します。最初のセルで数式を編集または変更できますが、他のセルでは数式は編集できません。数式バーが灰色になっていることがわかり、変更できないことを意味します。

総じて、XLOOKUPの複数値機能はVLOOKUPと比較して便利な改善です。各数式に対して個別に列番号を指定する必要がなくなります。素晴らしいですね！

例9. 複数の条件

XLOOKUPを使用して複数条件検索を実行する

XLOOKUPのもう一つの素晴らしい新機能は、複数の条件で検索する能力です。トリックは、数式で「&」演算子を使用して検索値と検索配列を個別に連結することです。以下の例で説明しましょう。

中程度の青い花瓶の価格を知る必要があります。この場合、3つの検索値（条件）が一致を探すために必要です。以下の数式を空のセルI2に入力し、Enterキーを押して結果を取得します。

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

√ 注: XLOOKUPは配列を直接処理できます。Control + Shift + Enterで数式を確認する必要はありません。

迅速な方法で複数条件検索を行う

ExcelでXLOOKUPよりも迅速で簡単な方法で複数条件検索を実行する方法はありますか？Kutools for Excelは素晴らしい機能「複数条件検索」を提供します。この機能を使用すると、数クリックで複数条件検索を実行できます！

1. Excelの「Kutools」タブに移動し、「スーパーLOOKUP」を見つけ、ドロップダウンリストで「複数条件検索」をクリックします。

2. 「複数条件検索」ダイアログボックスで、以下の手順を行ってください:

• 「検索対象値の範囲と出力範囲」セクションで、検索値範囲と出力範囲を選択します;
• 「データ範囲」セクションで、以下の操作を行ってください:
  • 「キー」列ボックスで、Ctrlキーを押しながら検索値を含む対応するキーカラムを1つずつ選択します;
  • 「返却」列ボックスで返却値を含む列を指定します。

3. OKボタンをクリックして結果を取得します。

√ 注:

• 指定された値で#N/Aエラー値を置換するセクションはダイアログボックスでオプションです。指定するかどうかは自由です。
• キー列ボックスに入力された列の数は、検索対象値ボックスに入力された列の数と等しくなければならず、両方のボックスの条件の順序は一対一で対応しなければなりません。

例10. 最後の一致で値を見つける

XLOOKUPで最後の一致結果を取得する

Excelで最後の一致値を見つけるには、逆順で検索するために第6引数を設定します。

デフォルトで、XLOOKUPの検索モードは1に設定されており、最初から最後まで検索します。しかし、XLOOKUPの良い点は、検索の方向を変更できることです。XLOOKUPは検索順序を制御するためのオプションの[search mode]引数を提供します。第6引数で検索モードを-1に設定するだけで、検索方向が最後から最初に変更されます。

以下の例をご覧ください。データベースでEmmaの最後の販売を知りたいです。

以下の数式を空のセルG2に入力し、Enterキーを押して結果を取得します。

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

√ 注: 第4および第5引数はオプションであり、この場合省略されています。オプションの第6引数のみを-1に設定しました。

素晴らしいツールで最後の一致値を簡単に検索する

XLOOKUPにアクセスできず、複雑な数式を覚えたくない場合は、「下から上に検索」機能を適用して簡単に実行できます。

1. Excelの「Kutools」タブに移動し、「スーパーLOOKUP」を見つけ、ドロップダウンリストで「下から上に検索」をクリックします。

2. 「下から上に検索」ダイアログボックスで、以下のように設定してください:

• 「検索対象値の範囲と出力範囲」セクションで、検索範囲と出力範囲を選択します;
• 「データ範囲」セクションで、データ範囲を選択し、「キーカラム」と「返却列」を指定します。

3. OKボタンをクリックして結果を取得します。

√ 注: 指定された値で#N/Aエラー値を置換するセクションはダイアログボックスでオプションです。指定するかどうかは自由です。

XLOOKUPサンプルファイルをダウンロードする

XLOOKUPの例.xlsx

関連する記事:

• ExcelでINDEXとMATCHを一緒に使用する方法？
• Excelで近似一致結果を見つけるためにファジーマッチを適用する方法？
• Excelで双方向検索数式を使用する方法？
• Excelで複数の条件でVLOOKUP値を検索する方法？
• Excelで右から左にVLOOKUP値を検索する方法？