検索・参照入門
XLOOKUP関数を見て理解する
検索値を表の中から探し、同じ位置にある別の範囲の値を返す関数です。
3つのポイント
- 探す値を決める
- 表から同じ値を探す
- 見つかった行から結果を返す
商品コードから単価を検索する
サンプル数式
- 検索値
- 検索範囲
- 戻り範囲
いまの結果：
120
計算結果結果セル：F2
先頭行：
入力・条件：3項目
- 検索値E2：「A002」
- 検索範囲A2:A4（3セル）値の例：「A001」、「A002」、「A003」
- 戻り範囲C2:C4（3セル）値の例：150、120、180
この結果になる理由
A3で一致が確認されました。
サンプルデータ
検索値を変えてみよう
※ Excel と Google スプレッドシートで結果や挙動が異なる場合、Excel の仕様を基準にしています。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 商品コード | 商品名 | 単価 | 検索値 | 結果 | |
| 2 | A001 | りんご | 150 | 120 | ||
| 3 | A002 | みかん | 120 | |||
| 4 | A003 | バナナ | 180 |
①検索値②検索範囲③戻り範囲✓結果セル
①・②・③ 正式引数 / 教材要素1–5 処理の流れ
E2に値を入力すると、結果と処理順が変わります。
3つの引数
色付きの丸数字は、サンプル数式と引数ガイドで同じ引数を示しています。表にも同じ番号があれば、その引数で指定しているセルを示します。
E2表のセル: E2単価を調べたい商品コードです。この例ではE2の「A002」を使います。
A2:A4表のセル: A2:A4検索値と一致する商品コードを探す範囲です。
C2:C4表のセル: C2:C4一致した商品コードと同じ位置から、結果として返す値の範囲です。
この数式を日本語にすると
E2の商品コードをA2:A4から探し、見つかった行と同じ位置にあるC2:C4の単価を返します。
この説明は、最初に表示されているサンプルデータをもとにしています。入力値を変更しても説明文は変わりません。
処理の流れ
手順1〜5を順番に確認しましょう。表内の小さな角丸数字は、対応する手順を示します。
検索値を確認する
E2E2の「A002」を確認します。
検索範囲を確認する
A2:A4A2:A4を検索対象にします。
一致するセルを探す
A3A3が検索値と一致します。
同じ位置の値を取得する
C3C2:C4の同じ位置にあるC3の120を取得します。
結果を表示する
F2F2に120を表示します。
対応アプリ
- Excel
- Google スプレッドシート
関数自体が対応するアプリです。教材例の数式や再計算が、両アプリで同じように動くことを保証するものではありません。
この例の注意点
- 3つの基本引数を使った、完全一致の例です。
- このサンプル表を対象とした完全一致の簡易動作です。
- 検索範囲と戻り範囲は、同じ数のセルを指定します。
- 値が見つからない場合、この例の結果は #N/A です。
一般構文
対応：Excel / Google スプレッドシート ・ 構文：構文差あり
- Google公式の通常Sheets構文の後半3引数には[]がありませんが、本文に省略時の扱いが記載されています。
- 検索値の位置を空にした場合、ExcelとGoogle スプレッドシートで結果が異なります。Google スプレッドシートでは、空白セルを検索する扱いにはなりません。
Excel
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])[] は省略できる引数です。
省略時
lookup_value- 条件付き省略検索範囲の空白セルを対象にする場合は、検索値を省略できます。
if_not_found- 省略して一致する値が見つからない場合は、#N/Aエラーを返します。
match_mode→ 0省略すると、完全一致で検索します。search_mode→ 1省略すると、先頭から末尾へ検索します。
Google スプレッドシート
=XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)[] がない引数も、公式本文で省略時の扱いが説明されている場合があります。
省略時
missing_value- 省略すると、一致が見つからない場合は#N/Aを返します。
match_mode→ 0省略すると、完全一致で検索します。search_mode→ 1省略すると、先頭から末尾の順に検索します。
公式資料・確認範囲
公式資料
- Microsoft Excel 公式構文
- Google スプレッドシート 公式対応状況・構文・省略時の扱い
実機確認
- Excel 2021 / Google スプレッドシート計算結果・入力の扱い・エラーを確認済み
未確認
配列展開
この教材で扱わない範囲
- この教材の再計算は、3つの基本引数を使う完全一致の例に限定しています。
次に学ぶ関数
XMATCH
対応する値を返した次は、同じ探し方で位置を返す方法を学びます。
次に学ぶ