XLOOKUP 関数を使用して、表や範囲から行ごとに情報を検索します。 たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。 XLOOKUP を使用すると、1 つの列で検索語を検索し、戻り列がどちら側にあるかに関係なく、別の列の同じ行から結果を返すことができます。
注
XLOOKUP は、Excel 2016 および Excel 2019 では使用できません。 ただし、新しいバージョンの Excel を使用して他のユーザーによって作成された場合は、Excel 2016または Excel 2019 のブックを XLOOKUP 関数で使用する状況が発生する可能性があります。
構文
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
| 引数 | 説明 |
|---|---|
|
検索値 必須* |
検索する値 *省略すると、XLOOKUP は lookup_arrayで見つかる空白のセルを返します。 |
|
検索範囲 必須 |
検索する配列または範囲 |
|
戻り配列 必須 |
返す配列または範囲 |
|
[見つからない場合] オプション |
有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。 有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。 |
|
[一致モード] オプション |
一致の種類を指定します: 0 - 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。 -1 - 完全一致。 見つからない場合は、次の小さなアイテムが返されます。 1 - 完全一致。 見つからない場合は、次の大きなアイテムが返されます。 2 - *、?、および 〜 が特別な意味を持つワイルドカードの一致。 |
|
[検索モード] オプション |
使用する検索モードを指定します。 1 - 先頭の項目から検索を実行します。 これが既定の設定です。 -1 - 末尾の項目から逆方向に検索を実行します。 2 - 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 -2 - 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
使用例
例 1 では、XLOOKUP を使用して範囲の国名を検索し、電話の国番号を返します。 これには、検索値 (セル F2)、検査範囲 (範囲 B2: B11)、および戻り配列 (範囲 D2: D11) の引数が含まれます。 XLOOKUP は既定では完全一致を生成するため、一致モード引数は含まれません。
注
XLOOKUP は検索範囲と戻り配列を使用するのに対し、VLOOKUP は列インデックス番号に続く単一の表の配列を使用します。 この場合、同等の VLOOKUP 数式は次のようになります。=VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
例 2 では、従業員 ID 番号に基づいて従業員情報を検索します。 VLOOKUP とは異なり、XLOOKUP は複数のアイテムを含む配列を返すことができます。これにより、1 つの数式で、セル C5: D14 の従業員名と部署の両方を返すことができます。
———————————————————————————
例 3 では 、前 の例に if_not_found 引数を追加します。
———————————————————————————
例 4 は、セル E2 に入力された個人所得の列 C を調び、列 B で一致する税率を見つけます。何も見つからない場合は、0 (ゼロ) を返すようにif_not_found引数を設定します。 match_mode引数は 1 に設定されます。つまり、関数は完全一致を検索し、見つからない場合は次の大きい項目を返します。 最後に、[検索モード] 引数は 1 に設定されます。つまり、関数は最初のアイテムから最後のアイテムまで検索します。
注
XARRAY の検査範囲の列は戻り配列の列の右にあります。VLOOKUP は、左から右にしか表示されません。
———————————————————————————
例 5 では、入れ子になった XLOOKUP 関数を使用して、垂直方向と水平方向の両方の一致を実行します。 最初に列 B で粗利益を探し、次にテーブルの最上行 （範囲 C5:F5） で Qtr1 を探し、最後に2 つの共通部分の値を返します。 これは、INDEX 関数と MATCH 関数を一緒に使用することと同様です。
ヒント
XLOOKUP を使用して HLOOKUP 機能を置き換えることもできます。
注
セル D3:F3 の数式は、=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) です。
———————————————————————————
例 6 では、 SUM 関数と 2 つの入れ子になった XLOOKUP 関数を使用して、2 つの範囲間のすべての値を合計します。 この場合、ブドウとバナナの値を合計し、2 つの間にある梨を含めます。
セル E3 の数式は次のとおりです。 =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
仕組み XLOOKUP は範囲を返すので、計算すると、数式は次のようになります: =SUM($E$7:$E$9)。 このような XLOOKUP 数式を持つセルを選択し、数式>Formula Auditing>Evaluate Formula を選択し、[評価] を選択して計算をステップ実行することで、この動作を自分で確認できます。
注
この例を提案するために、Microsoft Excel MVP の Bill Jelen に感謝します。
———————————————————————————