ExcelでNEW＆ADVANCED XLOOKUP関数を使用する方法（10例）
Excelの新しい XLOOKUP Excelが提供できる最も強力で最も簡単なルックアップ関数です。 絶え間ない努力により、MicrosoftはついにこのXLOOKUP関数をリリースし、VLOOKUP、HLOOKUP、INDEX + MATCH、およびその他のルックアップ関数を置き換えました。
このチュートリアルでは、 XLOOKUPの利点は何ですか および どうすればそれを入手して適用できますか さまざまなルックアップの問題を解決します。
XLOOKUPを入手するには？
Since XLOOKUP関数 is のみ利用可能 in Microsoft 365 の Excel, Excelの2021および Web用Excel、Excelを利用可能なバージョンにアップグレードしてXLOOKUPを取得できます。
XLOOKUP関数の構文
XLOOKUP関数 範囲または配列を検索してから、最初に一致した結果の値を返しますとして指定することができます。 構文 XLOOKUP関数の機能は次のとおりです。
引数：
- Lookup_value（必須）：あなたが探している価値。 table_array範囲の任意の列に含めることができます。
- Lookup_array（必須）：ルックアップ値を検索する配列または範囲。
- Return_array（必須）：値を取得する配列または範囲。
- If_not_found（オプション）：有効な一致が見つからない場合に返される値。 [if_not_found]のテキストをカスタマイズして、一致するものがないことを示すことができます。
それ以外の場合、戻り値はデフォルトで＃N / Aになります。 - Match_mode（オプション）：ここでは、lookup_valueをlookup_arrayの値と照合する方法を指定できます。
- 0（デフォルト）=完全一致。 一致するものが見つからない場合は、＃N / Aを返します。
- -1 =完全一致。 一致するものが見つからない場合は、次に小さい値を返します。
- 1 =完全一致。 一致するものが見つからない場合は、次に大きい値を返します。
- 2 =部分一致。 * 、？などのワイルドカード文字を使用しますおよび〜はワイルドカード一致を実行します。
- 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：完全一致
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 結果を取得するためのボタン。
例2.近似一致
を実行するには おおよそのルックアップ、あなたがする必要が 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ワイルドカード一致関数、最初に見てみましょう ワイルドカードとは.
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：左を見てください
1 VLOOKUPの欠点 それは ルックアップ列の右側でルックアップを実行するように制約されています。 ルックアップ列に残っている値を検索しようとすると、＃N / Aエラーが発生します。 心配しないでください。 XLOOKUPは、この問題を解決するための完璧なルックアップ関数です。
XLOOKUP 値をルックアップするように設計されています 左または右 ルックアップ列の。 制限はなく、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 結果を取得するためのボタン。
例5：垂直または水平ルックアップ
XLOOKUPを使用して垂直または水平ルックアップを実行します
Excelユーザーとして、VLOOKUPおよびHLOOKUP関数に精通している可能性があります。 VLOOKUPは、列を垂直に見ることです および HLOOKUPは横一列に見える.
今 新しいXLOOKUPはそれらを両方とも組み合わせますこれは、 垂直ルックアップまたは水平ルックアップを実行するには、XNUMXつの構文を使用するだけで済みます。 天才ですね。
以下の例では、XNUMXつのXLOOKUP構文のみを使用して、垂直方向または水平方向にルックアップを実行する方法を示します。
垂直ルックアップを実行するには、を入力します 以下の式 空白のセルE2で、 入力します 結果を得るための鍵。
=XLOOKUP(E1,A2:A13,B2:B13)
水平ルックアップを実行するには、を入力します 以下の式 空白のセルP2で、 入力します 結果を得るための鍵。
=XLOOKUP(P1,B1:M1,B2:M2)
あなたが見ることができるように、 構文は同じですとして指定することができます。 違いだけ XNUMXつの式の間にあなたが入力することです コラム 入力中の垂直ルックアップ 行 水平ルックアップで。
例6：双方向ルックアップ
まだ使っていますか 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エラーをカスタムメッセージですばやく上書きするには、 Kutools for Excel is 完璧なツール あなたを助けるためにExcelで。 そのビルトインで 0または＃N / Aを空白または特定の値の機能に置き換えます、複雑な数式やXLOOKUPへのアクセスなしで、見つからないメッセージを指定できます。
私たちの持つ Excelアドイン インストールされている場合は、次のようにしてください。
1。 に行く クツール Excelのタブで検索 スーパールックアップ、クリック 0または＃N / Aを空白または特定の値に置き換えます ドロップダウンリストに表示されます。
2。 の中に 0または＃N / Aを空白または特定の値に置き換えるダイアログボックス、次のように構成する必要があります。
- メディア ルックアップ値と出力範囲セクション選択 ルックアップ範囲 および 出力範囲;
- その後 [0または＃N / Aを特定の値に置き換える]オプションを選択します, テキストを入力してください あなたが好きです。
- メディア データ範囲セクション、選択 データ範囲、次に指定します キー列 および 返された列.
3。 クリック OK 結果を取得するためのボタン。 一致するものが見つからない場合、カスタマイズされたメッセージが表示されます。
例8：複数の値
別の 利点 XLOOKUPの 複数の値を返す 同じ試合で同時に。 XNUMXつの数式を入力して最初の結果を取得し、次に他の戻り値を入力します こぼす 隣接する空白のセルに自動的に挿入されます。
以下の例では、学生ID「FG9940005」に関するすべての情報を取得する必要があります。 秘訣は、単一の列または行ではなく、数式のreturn_arrayとして範囲を指定することです。 この場合、戻り配列の範囲はB2：D9で、XNUMXつの列が含まれます。
タイプ 以下の式 空白のセルG2で、 入力します すべての結果を取得するためのキー。
=XLOOKUP(F2,A2:A9,B2:D9)
すべての結果セルに同じ式が表示されます。 あなたはできる 編集または変更 式 最初のセルで、 だけど 他のセルでは、数式は編集できません。 数式バーが グレーアウト、つまり、変更を加えることはできません。
全体として、XLOOKUPの複数値関数は 有用な改善 VLOOKUPと比較して。 式ごとにすべての列番号を個別に指定する必要はありません。 いいぞ！
例9.複数の基準
別の 驚くべき新機能 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で対応している必要があります。
例10.最後に一致した値を見つける
検索するには 最後に一致した値 Excelで、 XNUMX番目の引数 XLOOKUP関数で 逆順で検索.
デフォルトでは、XLOOKUPの検索モードは1に設定されています、です 最初から最後まで検索。 しかし、良いことは XLOOKUP つまり ルックアップの方向を変更できます。 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エラー値を指定された値に置き換える]セクションはオプションであり、指定するかどうかを指定できます。
XLOOKUPサンプルファイルをダウンロードする
関連記事：
- ExcelでINDEXとMATCHを一緒に使用する方法は？
- ファジールックアップを適用してExcelで近似一致結果を見つける方法は？
- Excelで双方向ルックアップ数式を使用する方法は？
- Excelで複数の基準を使用して値をルックアップする方法は？
- Excelで値を右から左にルックアップする方法は？
最高のオフィス生産性ツール
Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます
- スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
- セル/行/列をマージする およびデータの保持。 分割セルコンテンツ; 重複する行と合計/平均を組み合わせる...重複セルを防止します。 範囲を比較する...
- [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
- 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
- お気に入りの数式をすばやく挿入する、範囲、チャート、写真; セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
- テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
- スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
- ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
- ピボットテーブルのグループ化 週番号、曜日など... ロック解除された、ロックされたセルを表示する さまざまな色で; 式/名前を持つセルを強調表示する...
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
