XLOOKUP が究極の Excel ルックアップ ソリューションである理由を発見する

 

Excel をしばらく使用している場合は、VLOOKUP や INDEX/MATCH を何度も利用したことがあるでしょう。 VLOOKUP の制限や INDEX/MATCH の複雑さと戦う時代は終わりました。 XLOOKUP をご紹介します。Excel の最新の検索関数は、両方を置き換えるのに十分な柔軟性、シンプルさ、強力さを備えています。

このチュートリアルでは、XLOOKUP があなたの新しい親友である 3 つの理由を紹介します。これにより、日常の検索タスクがどのように高速化、クリーン化、スマート化されるかがわかります。回避策は必要ありません。

構文:

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

XLOOKUP 関数 は、ある列 (または行) の値を検索し、別の列 (または行) から関連する値を返す Excel の最新の検索関数です。 VLOOKUP と INDEX/MATCH を置き換え、よりシンプルかつ柔軟になるように設計されています。

理由 1:XLOOKUP は任意の方向 (左または右) を見ることができます

VLOOKUP はルックアップ列の右側のみを検索できます。ルックアップ列がテーブル配列の最初の列でない場合、数式は失敗します。 INDEX/MATCH はこれを解決しますが、2 つの関数をネストする必要があるため、読みにくくなり、大きなデータセットでは乱雑になる可能性があります。

XLOOKUP はこれを逆転させます。検索範囲を戻り範囲から切り離すので、手間をかけずに左、右、上、下を調べることができます。

販売データセットがあり、製品名を検索して製品 ID を見つけたいとします。

VLOOKUP:

• セルを選択し、次の数式を挿入します。

=VLOOKUP(H5,A2:F23,1,FALSE)

この数式は #N/A を返します。 VLOOKUP が左側を参照できないためエラーが発生します (シートでは製品 ID が製品名の前に表示されます)。ここで VLOOKUP を使用するには、列を再配置する必要がありますが、データ構造や下流の数式が壊れる可能性があるため、多くの場合、実行不可能です。

インデックス/マッチ:

• セルを選択し、INDEX/MATCH コンボを使用します。

=INDEX(A2:A23, MATCH(H8, B2:B23, 0))

この式は製品 ID (P006) を返します。これは機能しますが、両方の範囲を指定し、MATCH を確保する必要があります。 は正確です (0) )、冗長性が追加されます。

XLOOKUP:
XLOOKUP は列の順序を気にしません。何を探すか、どこから値を返すかを指定するだけです。

• セルを選択し、動的な XLOOKUP 数式を挿入します。

=XLOOKUP(H12, B2:B23, A2:A23)

この数式は製品名を検索し、ID が左側にある場合でも、対応する製品 ID (P006) を返します。よりクリーンな構文。ネストは必要ありません。

実用的な用途:
販売ダッシュボードでユーザーが検索ボックスに製品名を入力できるとします。列を並べ替えることなく、製品 ID や複数の詳細 (価格、在庫、サプライヤー) を即座に取得できます。

=XLOOKUP(H12, B2:B23, A2:E23)

これにより、隣接するセルに「こぼれる」ことによって複数の列が返されるため、すべての製品情報を 1 回のステップで取得できます。

理由 2:組み込みのエラー処理 (#N/A は不要)

ルックアップ値が見つからない場合、Excel は #N/A を返します。 。 VLOOKUP または INDEX/MATCH では、通常、数式を IFERROR で囲みます。 これを適切に処理するには、ネストを追加します。

XLOOKUP には if_not_found が組み込まれています 引数を使用するため、式の中でフレンドリ メッセージやフォールバック値を直接指定できます。

データベースに存在しない可能性がある製品の単価を検索してみましょう (例:「ワイヤレス充電器」)。

VLOOKUP + IFERROR:

• セルを選択し、次の数式を挿入します。

=IFERROR(VLOOKUP(H4,D2:F23,1,FALSE), "Not Found")

これは機能しますが、冗長であり、ネストの層がさらに追加されます。

インデックス/一致 + IFERROR:

• セルを選択し、次の数式を挿入します。

=IFERROR(INDEX(D2:D23, MATCH(H8, B2:B23, 0)), "Not Found")

繰り返しますが、これは機能しますが、追加のラッパーにより数式が長くなります。

XLOOKUP:
XLOOKUP の if_not_found 引数は欠損値をエレガントに処理します。

• セルを選択し、次の数式を挿入します。

=XLOOKUP(H12, B2:B23, D2:D23, "Not Found")

この式は製品名を検索します。見つからない場合は「Not Found」を返します。簡潔であり、余分な機能を避けています。

理由 3:ワイルドカード検索が簡単になる

VLOOKUP はワイルドカード (*) をサポートしています。 と ? ) 完全一致モード (FALSE) ) テキスト検索に使用されますが、構文が必ずしも明らかであるとは限りません。 MATCH のため、INDEX/MATCH はワイルドカードもサポートします。 match_type の場合に認識します。 0です 。 XLOOKUP は、match_mode を使用して意図を明示します。 2 の ワイルドカード マッチングの場合、明確さが向上し、驚きが減ります。

製品名の一部だけを覚えていて (おそらく「Lap」で始まる)、その価格を知りたいと想像してください。

VLOOKUP:

• セルを選択し、次の数式を挿入します。

=VLOOKUP("Lap*", B2:D23, 3, FALSE)

これはワイルドカードを使用した完全一致で機能し、データを並べ替える必要はありません。ただし、「列インデックス」引数は、列が移動すると脆弱になる可能性があります。

インデックス/マッチ:

• セルを選択し、次の数式を挿入します。

=INDEX(D2:D23, MATCH("Lap*", B2:B23, 0))

これは、MATCH によるワイルドカードもサポートします。 。これは信頼性がありますが、2 範囲設定はより冗長になります。

XLOOKUP:
XLOOKUP の match_mode ワイルドカード検索が簡単になります。

• セルを選択し、次の数式を挿入します。

=XLOOKUP("Lap*", B2:B23, D2:D23, "No match", 2)

• 「ラップ*」: 部分一致 (「Lap」で始まる任意のテキストと一致)
• 検索配列: B2:B23
• 戻り配列: D2:D23
• 見つからない場合: 「一致しません」
• 2: ワイルドカード検索を有効にする

この数式は商品価格 (例:$30) を返します。 ) ワイルドカード マッチングを使用します。名前の部分検索、SKU ルックアップ、または正確な値がわからない場合に最適です。

重要な注意: XLOOKUP は、Microsoft 365、Excel 2021 以降のバージョンで利用できます。古いバージョンを使用している場合は、今のところ VLOOKUP または INDEX/MATCH を使用する必要があります。

ワイルドカードのヒント:

<番目>シンボル 意味 例 一致 * 「Lap*」ラップトップ スタンドの文字は何文字でも構いません。 単一文字「P00?」P001、P002 など~ ワイルドカード「Power~*」をエスケープします。文字通り「Power*」を検索します。

練習用ワークブックをダウンロード

最終的な考え

XLOOKUP は単なる検索関数ではなく、Excel での検索の仕組みを再考したものです。このチュートリアルでは、XLOOKUP が新しい親友である 3 つの理由を説明しました。方向の柔軟性、組み込みのエラー処理、および明示的なワイルドカード マッチングを組み合わせることで、時間を節約し、数式エラーを防ぎます。一度試したらもう戻れなくなります。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!