XLOOKUP¶
種類: MS Excel 互換関数 · ネイティブ導入: Excel 2021
範囲または配列から一致を検索し、2 番目の範囲または配列から対応する項目を返します。
構文¶
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない時], [一致モード], [検索モード])
引数¶
| 引数 | 必須 | 説明 |
|---|---|---|
| 検索値 | 必須 | 検索する値 |
| 検索範囲 | 必須 | 検索する範囲/配列 |
| 戻り範囲 | 必須 | 返す範囲/配列 |
| 見つからない時 | 省略可能 | 一致がない場合に返す値（省略可） |
| 一致モード | 省略可能 | 0 完全(既定)、-1 小さい値、1 大きい値、3 正規表現 |
| 検索モード | 省略可能 | 1 先頭から(既定)、-1 末尾から |
戻り値¶
一致した位置の戻り範囲の行(縦方向の検索)または列(横方向の検索)を配列として返し、動的配列対応の Excel ではスピルします。範囲を省略した場合、配列が空の場合、一致する項目がなく代替値もない場合は #N/A、一致位置が戻り範囲を超える場合は #REF!、内部エラーの場合は #VALUE! を返します。
例¶
| 数式 | 結果 | 説明 |
|---|---|---|
=XLOOKUP("b",{"a";"b";"c"},{10;20;30}) |
20 | 完全一致の検索 |
=XLOOKUP(2,{1;2;3},{10,11;20,21;30,31}) |
{20,21} | 一致した行全体をスピル |
=XLOOKUP(9,{1;2;3},{10;20;30},"none") |
none | 不一致の場合は代替値 |
=XLOOKUP({2;3},{1;2;3},{"a";"b";"c"}) |
{b;c} | 検索値の配列 → 要素ごとの検索 |
=XLOOKUP("^B",{"apple";"Banana";"cherry"},{1;2;3},"none",3) |
2 | 正規表現の一致(match_mode 3) |
=XLOOKUP("(?i)^b",{"apple";"Banana";"cherry"},{1;2;3},"none",3) |
2 | (?i) 接頭辞で大文字小文字を無視 |
解説¶
- match_mode 2(ワイルドカード)と search_mode 2/-2(バイナリ検索)はサポートしていません。
- match_mode 3(正規表現)は検索値を正規表現パターンとして解釈し、パターンがテキストセルの一部に一致する(部分一致、REGEXTEST 方式)項目を探します — Microsoft 365 ネイティブの 2024 年の新機能と同じモード番号です。既定では大文字と小文字を区別し、パターンの先頭に (?i) を付けると区別しません(ネイティブと同じ使い方)。テキスト以外のセルは一致せず、検索値がテキストでない場合、正規表現が不正な場合、search_mode 2/-2(バイナリ検索)と組み合わせた場合は #VALUE! を返します。一致がない場合は見つからない時の値がそのまま適用されます。
- 正規表現の構文は std::wregex の ECMAScript です(ネイティブの 365 の PCRE2 とは一部異なる場合があります)。
- 検索範囲が 1 列の場合は縦方向に検索し、それ以外の場合は先頭行を対象に横方向に検索します。
- 検索値に配列を指定すると要素ごとに検索され、検索値と同じ形状の配列としてスピルされます — 戻り範囲が複数列の場合、各要素の結果は最初の値に降格されます(ネイティブと同一)。エラーの検索値はそのエラーを返します。
- 関連関数: XMATCH、FILTER。
- 対応: Excel 2010+。ネイティブ関数がない旧バージョンでは
XLOOKUPの名前のまま（ドロップイン）、ネイティブ関数がある新しい Excel ではEG.XLOOKUPとして登録されます。