Excel で新機能かつ高度な XLOOKUP 関数を使用する方法（10 の例付き）
Excel の新関数 XLOOKUPは、Excel が提供する最も強力で使いやすい検索関数です。マイクロソフト社は長年の努力を重ね、ついに VLOOKUP や HLOOKUP、INDEX＋MATCH といった従来の検索関数に代わる新たな選択肢としてこの関数をリリースしました。
このチュートリアルでは、XLOOKUP のメリットと、その入手方法・活用法を通じてさまざまな検索課題を解決する方法をご紹介します。
XLOOKUP を入手する方法
XLOOKUP 関数は、Microsoft 365 向けExcel、Excel 2021 以降、および Web 版Excel でのみご利用いただけます。Excel 2019 以前のバージョンをお使いの場合は、XLOOKUP をご利用いただけるよう、ぜひアップグレードをご検討ください。
構文
は、指定された範囲または配列を検索し、最初に一致した値を返します。構文は次のとおりです。
引数：
- Lookup_value (required)：検索対象の値。table_array の範囲内であれば、どの列にあってもかまいません。
- Lookup_array (required)：検索値を探すための配列または範囲。
- Return_array (required)：値を取得したい配列または範囲。
- If_not_found (optional)：有効な一致が見つからない場合に返す値です。[if_not_found] をカスタマイズして、検索結果が一致しないことを明確に示せます。
指定しない場合は、デフォルトで #N/A が返されます。 - Match_mode (optional)ここでは、lookup_value を lookup_array 内の値とどのように照合するかを指定できます。
- 0（デフォルト）＝ 完全一致。一致する値が見つからない場合は、#N/A を返します。
- -1 ＝ 完全一致。一致する値が見つからない場合は、次に小さい値を返します。
- 1 ＝ 完全一致。一致する値が見つからない場合は、次に大きい値を返します。
- 2 ＝ 部分一致。ワイルドカード文字（*、？、~）を使ってワイルドカード検索を実行します。
- Search_mode (optional)：ここでは、検索順序を指定できます。
- 1（デフォルト）＝ lookup_array 内の最初の項目から最後の項目まで順に lookup_value を検索します。
- -1 ＝ lookup_array 内で最後の項目から最初の項目に向かって lookup_value を検索します。これにより、lookup_array 内で最後に一致した結果を取得できます。
- 2 ＝ バイナリ検索を実行します。この場合、lookup_array は昇順に並べ替えておく必要があります。並べ替えていないと、返される結果は無効になります。
- -2 ＝ バイナリ検索を実行します。この場合、lookup_array は降順に並べ替えておく必要があります。並べ替えられていないと、返される結果は無効になります。
引数の詳細については、以下の手順をご確認ください。
1。空のセルに以下の構文を入力してください。ただし、かっこは片側だけ入力すれば OK です。
2。Ctrl+A キーを押すと、関数の引数を表示するプロンプトボックスが開き、対応するもう一方のかっこが自動的に補完されます。
3。データパネルを下に引き下げると、XLOOKUP の6 つの関数引数すべてを確認できます。
 | >>> |  |
例
これで、XLOOKUP の基本的な仕組みがしっかり理解できたことでしょう。では、XLOOKUP の実践的な活用例をご紹介します！
例 1：完全一致
VLOOKUP を使うたびに、完全一致モードをいちいち明示指定しなければならなくてイライラしたことはありませんか？ 嬉しいことに、優れた XLOOKUP 関数を使えば、そんな手間はもう不要です。XLOOKUP はデフォルトで完全一致で検索します。
ここでは、オフィス用品の在庫リストをもとに、マウスなどの特定アイテムの単価を調べる方法をご紹介します。以下の手順に従ってください。
空のセル F2 に次の数式を入力し、Enter キーを押して結果を表示してください。
=XLOOKUP(E2,A2:A10,C2:C10)
これで、高度な XLOOKUP 数式を使ってマウスの単価を簡単に取得できました。一致モードはデフォルトで完全一致に設定されているため、明示的に指定する必要はありません。VLOOKUP よりもずっとシンプルで効率的です。
Excel の旧バージョンをご利用中で、まだExcel 2021 や Microsoft 365 へのアップグレードをご検討されていない場合は、「値を検索」という便利な機能がおすすめです。この機能を使えば、複雑な数式や XLOOKUP を使わずに結果を取得できます。
1。一致結果を表示するセルをクリックしてください。
2。「Kutools」タブに移動し、「関数ヘルパー」をクリックして、ドロップダウンリストから再度「関数ヘルパー」を選択します。
3。関数ヘルパーダイアログボックスで、次のように設定してください。
- 「関数の種類」セクションで「検索」を選択してください。
- 「関数を選択」セクションで、「範囲内でデータを検索します」を選択してください。
- 「引数の入力」セクションで、次の操作を行ってください。
- 「テーブル配列」ボックスで、検索値と結果値を含むデータ範囲を指定してください。
- 「検索値」ボックスで、検索対象のセルまたは範囲を選択してください。ただし、これは table_array の最初の列内に含まれている必要があります。
- 「列」ボックスで、一致する値を返す列を選択してください。
4。「OK」ボタンをクリックして、結果を取得してください。
Kutools for Excel— 300 以上の必須ツールでExcel を強化し、作業をより迅速・簡単に。AI 機能を活用して、スマートなデータ処理と生産性の飛躍的な向上を実現します。今すぐ入手
例 2：近似一致
近似検索を実行するには、第5 引数の一致モードを1 または-1 に設定してください。完全一致が見つからない場合、次に大きい値または小さい値が返されます。
ここでは、スタッフの所得に適用される税率を調べる必要があるとします。スプレッドシートの左側には、2021 年度の連邦所得税の課税区分が記載されています。E 列にスタッフの税率をどのように取得すればよいでしょうか？ご安心ください。以下の手順に従ってください。
1。空のセル E2 に以下の数式を入力し、Enter キーを押して結果を表示させましょう。
その後、必要に応じて表示された結果の書式を調整してください。
=XLOOKUP(D2,B2:B8,A2:A8,,1)
 | >>> |  |
※ 注：第4 引数［見つからない場合］は省略可能のため、ここでは省略しています。
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 のワイルドカード一致機能について説明する前に、まずは「ワイルドカード」とは何かを確認しましょう。
Microsoft Excel では、ワイルドカードは一括置換に使える特殊文字で、部分一致検索をしたいときに特に役立ちます。
ワイルドカードには、アスタリスク（*）、疑問符（?）、チルダ（~）の3 種類があります。
- アスタリスク（*）は、テキスト内の任意の文字数を表します。
- 疑問符（?）は、テキスト内の任意の1 文字を表します。
- チルダ（~）は、ワイルドカード（*、？、~）をリテラル文字として扱うために使用します。この機能を利用するには、ワイルドカードの直前にチルダ（~）を置きます。
ほとんどの場合、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：左方向検索
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」ボタンをクリックして、結果を取得してください。
Kutools for Excel— 300 以上の必須ツールでExcel を強化し、作業をより迅速・簡単に。AI 機能を活用して、スマートなデータ処理と生産性の飛躍的な向上を実現します。今すぐ入手
例 5：縦方向または横方向検索
Excel ユーザーの皆さまは、VLOOKUP 関数と HLOOKUP 関数をご存じかと思います。VLOOKUP は列内で縦方向に、HLOOKUP は行内で横方向に検索します。
新しい XLOOKUP はこれら両方を統合しており、縦方向でも横方向でも同じ構文で検索できます。まさに天才的ではありませんか？
以下の例では、1 つの XLOOKUP 関数で縦方向または横方向の検索を実行する方法をご紹介します。
縦方向検索を実行するには、空のセル E2 に次の数式を入力し、Enter キーを押して結果を取得してください。
=XLOOKUP(E1,A2:A13,B2:B13)
横方向検索を実行するには、空のセル P2 に次の数式を入力し、Enter キーを押して結果を表示してください。
=XLOOKUP(P1,B1:M1,B2:M2)
ご覧のとおり、構文はまったく同じです。2 つの数式の唯一の違いは、縦方向検索では列を、横方向検索では行を指定する点です。
例 6：二次元検索
二次元テーブル内の値を検索するのに、まだ INDEX 関数と MATCH 関数を使っていませんか？進化した XLOOKUP を使えば、もっと簡単に作業を完了できますよ！
XLOOKUP は二重検索が可能で、2 つの値の交差点を検索できます。XLOOKUP をネストすると、内側の XLOOKUP が行全体または列全体を返し、その結果が外側の XLOOKUP の戻り配列として使われます。
ここにはさまざまな科目の学生の成績リストがあります。たとえば、キムさんの化学の成績を知りたいとします。
それでは、この魔法のような XLOOKUP を使って、その使い方を確認しましょう。
- 「内側」の XLOOKUP を列全体の返す値として実行します。XLOOKUP(H2, B1:E1, B2:E10) を使うと、化学の成績の範囲を取得できます。
- 「内側」の XLOOKUP を「外側」の XLOOKUP の中にネストし、「内側」の XLOOKUP をその数式全体の return_array として使います。
- 最終的な数式は次のとおりです。
=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 引数に必要なテキストを入力し、そのテキストを二重引用符（"）で囲んでください。
たとえば、都市名「Denver」が見つからない場合、XLOOKUP は通常#N/A エラーを返しますが、第4 引数に「No Match」と指定すれば、エラーメッセージの代わりに「No Match」と表示されるようになります。
空白セル F3 に次の数式を入力し、Enter キーを押して結果を表示してください。
=XLOOKUP(E2,A2:A11,C2:C11,「No Match」)
#N/A エラーを素早くカスタムメッセージに置き換えたいなら、Kutools for ExcelがExcel 上で最適なツールです。「#N/A または0 を空白または特定の値に置換」機能を使えば、複雑な数式や XLOOKUP を使うことなく、「見つかりません」といったメッセージを簡単に設定できます。
1。Excel の「Kutools」タブを開き、「スーパー LOOKUP」をクリックし、「ドロップダウンリスト」から「#N/A または0 を空白または特定の値に置換」を選択してください。
2. 「#N/A または0 を空白または特定の値に置き換える」ダイアログボックスで、次のように設定してください。
- 「検索と出力エリア」セクションで、検索範囲とリストの配置エリアを指定してください。
- 次に、「0 または#N/A を特定の値に置き換える」オプションを選択し、任意のテキストを入力してください。
- 「データ範囲」セクションでデータ範囲を選択し、「キーカラム」と「返すカラム」をそれぞれ指定してください。
3。「OK」ボタンをクリックすると、結果が表示されます。一致する値が見つからない場合は、カスタマイズされたメッセージが表示されます。
Kutools for Excel— 300 以上の必須ツールでExcel を強化し、作業をより迅速・簡単に。AI 機能を活用して、スマートなデータ処理と生産性の飛躍的な向上を実現します。今すぐ入手
例8：複数の値
XLOOKUP のもう一つの利点は、同じ一致条件に対して複数の値を同時に返せることです。数式を1 つ入力するだけで最初の結果が表示され、他の該当する値は自動的に隣接する空白セルにスピル（連動表示）されます。
以下の例では、学生 ID「FG9940005」に関するすべての情報を取得したいとします。ポイントは、数式内で単一の列や行ではなく、範囲を return_array として指定することです。この場合、戻り値の範囲は B2:D9 で、3 列を含みます。
空白セル G2 に次の数式を入力し、Enter キーを押してすべての結果を取得してください。
=XLOOKUP(F2,A2:A9,B2:D9)
すべての結果セルには同じ数式が表示されます。最初のセルでは数式を編集・変更できますが、他のセルでは編集できません。数式バーがグレーアウトしていることからも、その変更不可な状態が一目でわかります。
総合的に見ると、XLOOKUP の複数値取得機能は VLOOKUP と比べて非常に便利な進化です。各数式で個別に列番号を指定する手間がなくなりました。素晴らしい機能です！
例9：複数条件
XLOOKUP のもう一つの驚くべき新機能は、複数条件での検索が可能になったことです。そのコツは、数式内で「&」演算子を使って、検索値の範囲と検索配列をそれぞれ連結することです。以下の例で詳しく説明します。
中サイズの青い花瓶の価格を調べるには、3 つの検索条件（サイズ、色、商品）に一致する値を検索する必要があります。空白セル I2 に以下の数式を入力し、Enter キーを押して結果を取得してください。
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ 注記：XLOOKUP は配列を直接処理できます。Ctrl + Shift + Enter で数式を確定する必要はありません。
Excel で
1。Excel の「Kutools」タブを開き、「スーパー LOOKUP」からドロップダウンリスト内の「検索 - 複数条件検索」をクリックしてください。
2。「検索 - 複数条件検索」ダイアログボックスで、以下の手順に従って操作してください。
- 「検索と出力エリア」セクションで、検索値の範囲とリストの配置エリアを指定してください。
- 「データ範囲」セクションで、次の操作を行ってください。
- 「主キーカラム」ボックスで、Ctrl キーを押しながら、検索値の範囲を含む対応するキーカラムを1 つずつ選択してください。
- 「返すカラム」ボックスで、取得したい値が含まれる列を指定します。
3。「OK」ボタンをクリックして、結果を取得してください。
√ 注：
- ダイアログボックスが見つからない場合は「#N/A」に置き換え、指定された値を返します。このセクションは省略可能です。必要に応じて指定してください。
- キーカラムボックスに入力された列数は、検索値の範囲ボックスに入力された列数と一致している必要があり、さらに両方のボックス内の条件は、それぞれが一対一に対応する順序で指定しなければなりません。
Kutools for Excel— 300 以上の必須ツールでExcel を強化し、作業をより迅速・簡単に。AI 機能を活用して、スマートなデータ処理と生産性の飛躍的な向上を実現します。今すぐ入手
例10：最後に一致した値を検索
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』と置き換えて指定された値を返します」セクションはオプションです。必要に応じて指定するか、省略できます。
Kutools for Excel— 300 以上の必須ツールでExcel を強化し、作業をより迅速・簡単に。AI 機能を活用して、スマートなデータ処理と生産性の飛躍的な向上を実現します。今すぐ入手
XLOOKUP サンプルファイルをダウンロード
関連記事：
- Excel で INDEX 関数と MATCH 関数を組み合わせるには、どうすればよいですか？
- Excel でファジーマッチを使って近似一致の結果を取得するには、どうすればよいですか？
- Excel で二方向検索の数式を使用する方法は？
- Excel で複数条件で VLOOKUP を行う方法は？
- Excel で右から左に値を VLOOKUP する方法は？
最高の Office 業務効率化ツール
| 🤖 | KUTOOLS AI アシスタント：次に基づいてデータ分析を革新します：インテリジェント実行 ｜ コード生成｜ カスタム数式作成 ｜ データ分析とチャート生成｜ 拡張機能呼び出し… |
| 人気の機能：検索・ハイライト、または重複をマーキング ｜ 空白行を削除する ｜ データを失うことなく列の結合またはセルを ｜ 数式を使用しない四捨五入... | |
| スーパー LOOKUP：複数条件 VLookup ｜ 複数値 VLookup ｜ 複数シート間 VLookup ｜ ファジーマッチ.... | |
| 高度なドロップダウンリスト：ドロップダウンリストをすばやく作成 ｜ 連動型ドロップダウンリスト ｜ 複数選択可能なドロップダウンリスト.... | |
| 列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜範囲および列の比較... | |
| 注目の機能：グリッドフォーカス ｜ デザインビュー ｜強化された数式バー ｜ ワークブックとシートマネージャー ｜ リソースライブラリ（オートテキスト）｜ 日付ピッカー ｜ ワークシートの統合 ｜ 暗号化／セルの復号化 ｜ リストからメール送信 ｜ スーパーフィルター ｜ 特殊フィルタ（太字のフォントを持つセルをフィルタリング／斜体／取り消し線。。。） 。。。 | |
| トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除、...）｜ 50+チャートタイプ（ガントチャート、...）｜ 40+実用的関数（誕生日に基づいて年齢を計算します、...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入、...）｜ 12 変換ツール（単語に変換する、為替レートの変換、...）｜ 7 結合と分割ツール（高度な行のマージ、セルの分割、...）｜さらに多数 |
Kutools for Excel でExcel スキルを強化し、これまでにない効率を体験しましょう。Kutools for Excel は、生産性を高め、時間を大幅に節約できる高度な機能を300 以上提供します。最も必要な機能を今すぐ入手するにはこちらをクリック。。。
Office Tab は Office にタブインターフェースをもたらし、作業を大幅に簡単にします
- Word、Excel、PowerPoint でタブを使った編集と閲覧を有効にします。Publisher、Access、Visio、Project でもご利用いただけます。
- 複数のドキュメントを、新しいウィンドウではなく、同じウィンドウ内の新しいタブで開いたり作成したりできます。
- 日々の生産性を50％も向上させ、毎日数百回ものマウスクリックを削減します！
すべてのKutools アドインが、たった1 つのインストーラーで完結。
Kutools for Officeスイートには、Excel ・Word ・Outlook ・PowerPoint 用のアドインと Office Tab Pro が含まれており、複数の Office アプリを横断して作業するチームに最適です。
- オールインワンスイート— Excel、Word、Outlook、PowerPoint 用アドイン＋Office Tab Pro
- インストーラー1 つ、ライセンス1 つ— 数分でセットアップ可能（MSI 対応）
- 連携してさらにパワーアップ— Office アプリ全体で生産性が向上
- 30 日間のフル機能トライアル— 登録不要、クレジットカード不要
- 最高のお得感— 個別アドイン購入よりお得