ExcelでNEW＆ADVANCED XLOOKUP関数を使用する方法（10例）

Excelの新しい XLOKUP Excelが提供できる最も強力で最も簡単なルックアップ関数です。 絶え間ない努力により、MicrosoftはついにこのXLOOKUP関数をリリースし、VLOOKUP、HLOOKUP、INDEX + MATCH、およびその他のルックアップ関数を置き換えました。

このチュートリアルでは、 XLOOKUPの利点は何ですか & どうすればそれを入手して適用できますか さまざまなルックアップの問題を解決します。

XLOOKUPを入手するには？

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を入手するには？

Since XLOOKUP関数 is のみ利用可能 in Microsoft 365 の Excel, Excelの2021, Web用Excel、Excelを利用可能なバージョンにアップグレードしてXLOOKUPを取得できます。

XLOOKUP関数の構文

XLOOKUP関数 範囲または配列を検索してから、最初に一致した結果の値を返しますを選択します。 構文 XLOOKUP関数の機能は次のとおりです。

引数：

1. Lookup_value（必須）：あなたが探している価値。 table_array範囲の任意の列に含めることができます。
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_valueを検索します。 これは、lookup_arrayで最後に一致する結果を取得する必要がある場合に役立ちます。
   • 2 =昇順でソートされたlookup_arrayを必要とするバイナリ検索を実行します。 ソートされていない場合、返される結果は無効になります。
   • -2 =降順でソートされたlookup_arrayを必要とするバイナリ検索を実行します。 ソートされていない場合、返される結果は無効になります。

XLOOKUP関数の引数に関する詳細情報、次のようにしてください。

1.次のように入力します 以下の構文 空のセルに入力する必要があるのは、ブラケットの片側だけであることに注意してください。

2。 押す Ctrlキー+をタップし、その後、 プロンプトボックス 表示されるポップアップ 関数の引数。 そして、ブラケットの反対側は自動的に終了します。

3. データパネルをプルダウンします、それからあなたはすべてを見ることができます XNUMXつの関数の引数 XLOOKUPの。

>>>

XLOOKUP関数の例

これで、XLOOKUP関数の基本原則をマスターしたと思います。 すぐに飛び込みましょう 実例 XLOOKUPの。

例1：完全一致

XLOOKUPと完全一致を実行します

VLOOKUPを使用するたびに完全一致モードを指定する必要があったため、イライラしたことはありませんか？ 幸いなことに、驚くべきXLOOKUP関数を試してみると、この問題は発生しなくなりました。 デフォルトでは、XLOOKUPは完全一致を生成します.

ここで、事務用品の在庫のリストがあり、マウスなどのXNUMXつのアイテムの統一価格を知る必要があるとします。次のようにしてください。

タイプ 以下の式 空白のセルF2に挿入し、 入力します 結果を得るための鍵。

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

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

数回クリックするだけで完全に一致します

おそらく、より低いバージョンのExcelを使用していて、Excel2021またはMicrosoft365にアップグレードする予定はまだありません。 この場合、私はお勧めします 便利な機能 - リスト式で値を探す of Kutools for Excel. この機能を使用すると、複雑な数式やXLOOKUPへのアクセスなしで結果を得ることができます。

o付きur Excelアドイン インストすべて、次のようにしてください。

1.セルをクリックして、一致した結果を表示します。

2。 に行く クツール タブをクリックします。 フォーミュラヘルパー、をクリックします。 ドロップダウンリストの数式ヘルパー.

3。 の中に 数式ヘルパーダイアログボックス、次のように構成してください。

• 選択 見上げる セクションに 数式タイプセクション;
• 数式セクションを選択してください選択 リストで値を探す;
• 引数入力セクション、次のようにしてください。
  • Table_arrayボックス、ルックアップ値と結果値を含むデータ範囲を選択します。
  • Lookup_valueボックス、検索する値のセルまたは範囲を選択します。 table_arrayの最初の列にある必要があることに注意してください。
  • 列ボックス、一致した値を返す列を選択します。

4。 クリック OK 結果を取得するためのボタン。

クリックしてKutoolsfor Excelをダウンロードし、30日間の無料トライアルをご利用ください.

---

例2.近似一致

XLOOKUPで近似一致を実行します

を実行するには おおよそのルックアップ、あなたがする必要が 1番目の引数で一致モードを1または-XNUMXに設定します. 完全に一致するものが見つからない場合は、次に大きい値または小さい値を返します.

この場合、あなたはあなたのスタッフの収入の税率を知る必要があります。 スプレッドシートの左側には、2021年の連邦所得税ブラケットがあります。E列のスタッフの税率をどのように取得できますか？ 心配しないでください。 次のようにしてください。

1.次のように入力します 以下の式 空白のセルE2に挿入し、 入力します 結果を得るための鍵。
次に、必要に応じて、返された結果のフォーマットを変更します。

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

>>>

√注：XNUMX番目の引数[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を取得するには;
• 入力します セル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では、 ワイルドカードは、任意の文字を置き換えることができる特殊な種類の文字です。 特に 役立つ 部分一致ルックアップを実行する場合。

ワイルドカードには次の XNUMX つのタイプがあります。 アスタリスク (*), 疑問符（？）, チルダ（〜）.

• アスタリスク（*）は、テキスト内の任意の数の文字を表します。
• 疑問符（？）は、テキスト内の任意のXNUMX文字を表します。
• チルダ（〜）は、ワイルドカード（* 、？〜）をリテラル文字に変換するために使用されます。 この機能を実行するには、ワイルドカードの前にチルダ（〜）を配置します。

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

アメリカの大手企業50社の時価総額のリストがあり、いくつかの企業の時価総額を知りたいが、会社名は略して、これはワイルドカードマッチの完璧なシナリオであるとします。 トリックを行うためにステップバイステップで私に従ってください。

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

1.次のように入力します 以下の式 空白のセルH3に移動し、 入力します 結果を得るための鍵。

=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と連結します アンパサンド（＆）.
• XNUMX番目の引数[If_not_found]はオプションなので、省略します。

例4：左を見てください

XLOOKUPを使用して左を見てください

1 VLOOKUPの欠点 それは ルックアップ列の右側でルックアップを実行するように制約されています。 ルックアップ列に残っている値を検索しようとすると、＃N / Aエラーが発生します。 心配しないでください。 XLOOKUPは、この問題を解決するための完璧なルックアップ関数です。

XLOKUP 値をルックアップするように設計されています 左または右 ルックアップ列の。 制限はなく、Excelユーザーのニーズを満たします。 以下の例では、そのトリックを示します。

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

列Cを検索し、列Aの値を返す必要があります。次のようにしてください。

1.次のように入力します 以下の式 空白のセルG2に。

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

2。 プレス 入力します 結果を得るための鍵。

√注：XLOOKUPの左へのルック機能は、インデックスとマッチを置き換えて、左の値を探すことができます。

数回クリックするだけで値を右から左にルックアップ

数式を覚えたくない人のために、ここで、私はお勧めします 便利な機能 - 右から左へのルックアップ of Kutools for Excel. この機能を使用すると、数秒以内に右から左へのルックアップを実行できます。

o付きur Excelアドイン インストすべて、次のようにしてください。

1。 に行く クツール Excelのタブで検索 スーパールックアップ、クリック 右から左へのルックアップ ドロップダウンリストに表示されます。

2。 の中に 右から左へのルックアップダイアログボックス、次のように構成する必要があります。

• ルックアップ値と出力範囲セクション、指定する ルックアップ範囲 & 出力範囲;
• データ範囲セクション、入力 データ範囲、次に指定します キー列 & 戻り列;

3。 クリック OK 結果を取得するためのボタン。

クリックしてKutoolsfor Excelをダウンロードし、30日間の無料トライアルをご利用ください.

---

例5：垂直または水平ルックアップ

XLOOKUPを使用して垂直または水平ルックアップを実行します

Excelユーザーとして、VLOOKUPおよびHLOOKUP関数に精通している可能性があります。 VLOOKUPは、列を垂直に見ることです & HLOOKUPは横一列に見える.

Now 新しいXLOOKUPはそれらを両方とも組み合わせますこれは、 垂直ルックアップまたは水平ルックアップを実行するには、XNUMXつの構文を使用するだけで済みます。 天才ですね。

以下の例では、XNUMXつのXLOOKUP構文のみを使用して、垂直方向または水平方向にルックアップを実行する方法を示します。

垂直ルックアップを実行するには、を入力します 以下の式 空白のセルE2で、 入力します 結果を得るための鍵。

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

水平ルックアップを実行するには、を入力します 以下の式 空白のセルP2で、 入力します 結果を得るための鍵。

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

あなたが見ることができるように、 構文は同じですを選択します。 違いだけ XNUMXつの式の間にあなたが入力することです コラム 入力中の垂直ルックアップ 行 水平ルックアップで。

例6：双方向ルックアップ

XLOOKUPを使用して双方向ルックアップを実行します

まだ使っていますか INDEXおよびMATCH関数 XNUMX次元範囲の値を検索するには？ 試す 改善されたXLOOKUP あなたの仕事を成し遂げるために より簡単に.

XLOOKUPは実行できます ダブルルックアップ、を見つける 交差点 XNUMXつの値の。 沿って ネスティング XNUMXつのXLOOKUPが別の内部にある場合、内部のXLOOKUPは行または列全体を返すことができ、この返された行または列は、戻り配列として外部のXLOOKUPに入力されます。

さまざまな分野の学生の成績のリストがあり、キムの化学科目の成績を知りたいとします。

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

• 「内部」XLOOKUPを実行して、入力列の値を返します。 XLOOKUP（H2、B1：E1、B2：E10）は、さまざまな化学グレードを取得できます。
• 完全な式のreturn_arrayとして「内部」XLOOKUPを使用することにより、「外部」XLOOKUP内に「内部」XLOOKUPをネストします。
• 次に、最終的な式があります。

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

• タイプ 上記の式 空白のセルH3に、 入力します 結果を取得するためのボタン。

またはあなたがすることができます 逆に、「内部」XLOOKUPを使用して、行全体の値を返します。これらはすべて、Kimのサブジェクトグレードです。 次に、「外部」XLOOKUPを使用して、キムのすべての対象グレードの中から化学グレードを探します。

• タイプ 以下の式 空白のセルH4で、 入力します 結果を取得するためのボタン。

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

XLOOKUPの双方向ルックアップ機能は、垂直および水平ルックアップ機能の完璧な例でもあります。 必要に応じて試してみてください！

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

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

他のルックアップ関数と同じように、XLOOKUP関数の場合 一致するものが見つかりません ＃N / Aエラーメッセージ 返されます。 一部のExcelユーザーにとっては混乱を招く可能性があります。 しかし、良いニュースはそれです エラー処理 で利用可能です XLOOKUP関数のXNUMX番目の引数.

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

たとえば、デンバー市が見つからないため、XLOOKUPは＃N / Aエラーメッセージを返します。 ただし、XNUMX番目の引数を「一致なし」というテキストでカスタマイズすると、数式はエラーメッセージの代わりに「一致なし」のテキストを表示します。

タイプ 以下の式 空白のセルF3で、 入力します 結果を取得するためのボタン。

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

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

＃N / Aエラーをカスタムメッセージですばやく上書きするには、 Kutools for Excel is 完璧なツール あなたを助けるためにExcelで。 そのビルトインで 0または＃N / Aを空白または特定の値の機能に置き換えます、複雑な数式やXLOOKUPへのアクセスなしで、見つからないメッセージを指定できます。

私たちの持つ Excelアドイン インストールされている場合は、次のようにしてください。

1。 に行く クツール Excelのタブで検索 スーパールックアップ、クリック 0または＃N / Aを空白または特定の値に置き換えます ドロップダウンリストに表示されます。

2。 の中に 0または＃N / Aを空白または特定の値に置き換えるダイアログボックス、次のように構成する必要があります。

• ルックアップ値と出力範囲セクション選択 ルックアップ範囲 & 出力範囲;
• その後 [0または＃N / Aを特定の値に置き換える]オプションを選択します, テキストを入力してください あなたが好きです。
• データ範囲セクション、選択 データ範囲、次に指定します キー列 & 返された列.

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

クリックしてKutoolsfor Excelをダウンロードし、30日間の無料トライアルをご利用ください.

---

例8：複数の値

XLOOKUPで複数の値を返す

別の 利点 XLOOKUPの 複数の値を返す 同じ試合で同時に。 XNUMXつの数式を入力して最初の結果を取得し、次に他の戻り値を入力します こぼす 隣接する空白のセルに自動的に挿入されます。

以下の例では、学生ID「FG9940005」に関するすべての情報を取得する必要があります。 秘訣は、単一の列または行ではなく、数式のreturn_arrayとして範囲を指定することです。 この場合、戻り配列の範囲はB2：D9で、XNUMXつの列が含まれます。

タイプ 以下の式 空白のセルG2で、 入力します すべての結果を取得するためのキー。

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

すべての結果セルに同じ式が表示されます。 あなたはできる 編集または変更 式 最初のセルで、 だけど 他のセルでは、数式は編集できません。 数式バーが グレーアウト、つまり、変更を加えることはできません。

全体として、XLOOKUPの複数値関数は 有用な改善 VLOOKUPと比較して。 式ごとにすべての列番号を個別に指定する必要はありません。 いいぞ！

例9.複数の基準

XLOOKUPを使用して多基準ルックアップを実行します

別の 驚くべき新機能 XLOOKUPの 複数の基準によるルックアップ. トリックは 連結する ルックアップ値とルックアップ配列 「＆」演算子 式で個別に。 以下の例で説明しましょう。

ミディアムブルーの花瓶の価格を知る必要があります。 この場合、一致するものを探すにはXNUMXつのルックアップ値（基準）が必要です。 次のように入力します 以下の式 空白のセルI2で、を押します。 入力します 結果を得るための鍵。

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

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

クイックメソッドによるマルチ条件ルックアップ

そこにはありますか？ より速く、より簡単 ExcelでXLOOKUPよりも多基準ルックアップを実行する方法は？ Kutools for Excel は、大阪で 素晴らしい機能 - マルチコンディションルックアップ。 この機能を使用すると、数回クリックするだけで複数の条件のルックアップを実行できます。

私たちの持つ Excelアドイン インストールされている場合は、次のようにしてください。

1。 に行く クツール Excelのタブで検索 スーパールックアップ、クリック マルチコンディションルックアップ ドロップダウンリストに表示されます。

2。 の中に マルチコンディションルックアップダイアログボックス、次のようにしてください。

• ルックアップ値と出力範囲セクション、選択 ルックアップ値の範囲 と 出力範囲;
• データ範囲セクション、次の操作を行ってください。
  • 現在地に最も近い 対応するキー列 を保持することにより、ルックアップ値をXNUMXつずつ含む Ctrlキー キー入力 キーコラムボックス;
  • 特定します の項目に表示されます。 に戻り値が含まれています リターンカラムボックス.

3。 クリック OK 結果を取得するためのボタン。

√注：

• ダイアログボックスでは、[＃N / Aエラー値を指定された値に置き換える]セクションはオプションであり、指定するかどうかを指定できます。
• [キー列]ボックスに入力する列の数は、[ルックアップ値]ボックスに入力する列の数と同じである必要があり、両方のボックスの基準の順序は互いにXNUMX対XNUMXで対応している必要があります。

クリックしてKutoolsfor Excelをダウンロードし、30日間の無料トライアルをご利用ください.

---

例10.最後に一致した値を見つける

XLOOKUPで最後に一致した結果を取得します

最後に一致した値 Excelで、 XNUMX番目の引数 XLOOKUP関数で 逆順で検索.

デフォルトでは、XLOOKUPの検索モードは1に設定されていますボタンの隣にある 最初から最後まで検索。 しかし、良いことは XLOKUP つまり ルックアップの方向を変更できます。 XLOOKUPは オプションの[検索モード]引数 検索順序を制御します。 XNUMX番目の引数の検索モードを次のように設定するだけです。 -1、ルックアップ方向が最後から最初に検索するように変更されます。

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

タイプ 以下の式 空白のセルG2で、 入力します 結果を得るための鍵。

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

√注：1番目とXNUMX番目の引数はオプションであり、この場合は省略されます。 オプションのXNUMX番目の引数のみを-XNUMXに設定します。

すばらしいツールを使用して、最後に一致する値を簡単に検索できます

XLOOKUPにアクセスできず、複雑な数式を覚えたくない場合は、 下から上へのルックアップ機能 of Kutools for Excel それを成し遂げるために 簡単に.

私たちの持つ Excelアドイン インストールされている場合は、次のようにしてください。

1。 に行く クツール Excelのタブで検索 スーパールックアップ、クリック 下から上へのルックアップ ドロップダウンリストに表示されます。

2。 の中に 下から上へのルックアップダイアログボックス、次のように構成する必要があります。

• ルックアップ値と出力範囲セクション、選択 ルックアップ範囲 & 出力範囲;
• データ範囲セクション、選択 データ範囲、次に指定します キー列 & 戻り列.

3。 クリック OK 結果を取得するためのボタン。

√注：ダイアログボックスでは、[＃N / Aエラー値を指定された値に置き換える]セクションはオプションであり、指定するかどうかを指定できます。

クリックしてKutoolsfor Excelをダウンロードし、30日間の無料トライアルをご利用ください.

---

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

XLOOKUPのExamples.xlsx

関連記事：

• ExcelでINDEXとMATCHを一緒に使用する方法は？
• ファジールックアップを適用してExcelで近似一致結果を見つける方法は？
• Excelで双方向ルックアップ数式を使用する方法は？
• Excelで複数の基準を使用して値をルックアップする方法は？
• Excelで値を右から左にルックアップする方法は？