値の検索

検索関数は、スプレッドシート作業で最も一般的な課題の一つを解決します。ある場所にデータがあり、対応する値に基づいて別の場所から関連情報を取得する必要がある場合に使用します。

関数には検索する値、検索範囲、見つかった場合に返す値を指定します。Excelは、データが変更されるたびに、すべての行で自動的に検索を実行します。

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found])

完全一致

完全一致は、検索値が参照テーブル内の値と完全に一致する必要があります。これは XLOOKUP のデフォルト動作です。

近似一致

近似一致は、検索値を超えない範囲で最も近い値を見つけます。税率表や割引基準などの階層システムで使用されます。

XLOOKUP では、近似一致は match_mode 引数（if_not_found の後に続く4番目のオプション引数）で制御されます。これを -1 に設定すると、Excel は「完全一致が見つからない場合、次に小さい値を返す」ように動作します。

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; -1)

VLOOKUP

VLOOKUPは今でも何百万ものスプレッドシートで使用されており、頻繁に目にする関数です。主な制約は構造上のもので、検索値は常にテーブル配列の最初の列に配置する必要があり、返される値は列番号で指定します。検索列と返却列の間に列を挿入すると、その列番号がずれてしまい、VLOOKUPは警告なしに誤ったデータを返します。XLOOKUPは返却列を直接参照するため、この問題が発生しません。

=VLOOKUP(search_key; range; index; [is_sorted])

タスク

1. カテゴリごとの月間予算の取得

サマリーセクションの下に、Category（カテゴリ）とMonthly Budget（月間予算）の列を持つ参照テーブルを作成。

次の値を入力：

• Rent — 1,500.00
• Groceries — 400.00
• Utilities — 250.00
• Transport — 200.00
• Dining — 150.00

これらの値を通貨形式で表示。

Expensesテーブルで、各行の予算を取得するには次の式を使用： =XLOOKUP(B9;I12:I20;J12:J20)

各カテゴリに対応する予算が返される。

2. カテゴリが見つからない場合の対応

カテゴリが見つからない場合にエラーを回避するため、数式を更新： =XLOOKUP(B9;I12:I20;J12:J20;"No budget defined")

シートの可読性を保ち、未対応のマッピングを強調表示。

3. 参照テーブルの範囲を固定

数式をコピーしても範囲がずれないように、F4で参照範囲を固定。

4. 割引階層の近似一致を適用

Spending thresholds（支出閾値）とDiscount values（割引値）の列を持つ新しいテーブルを作成。

次の値を入力：

• 0 — 0
• 500 — 5
• 1000 — 10
• 2000 — 20

割引値はパーセンテージ形式で表示。

各支出に対する割引階層を計算するには次の式を使用： =XLOOKUP(D9;12:M16;N12:N16;"No tier";-1)