XLOOKUP関数には、さまざまなオプションがあります
XLOOKUP関数には「エラー」を表示しないオプションが追加されました
XLOOKUP関数には6つの引数があります。
しかし、必須の関数は3つ目までで、4つ目以降は省略可能です。
① 検索値
従来のVLOOKUP関数と同じ内容
② 検索範囲
従来のVLOOKUP関数とは異なり、MATCH関数と同様に「検索する範囲だけを指定する」。
この検索範囲は"行方向(縦方向)"だけではなく"列方向(横方向)"にも指定することができる。
③ 戻り配列
検索の結果として、「どこを返すのか」を指定する。
LOOKUP関数と同様に、"検索する範囲"と"結果として得たい値"を別個に指定できる。
④ 見つからない場合
検索値が見つからなかった場合に表示する"文字列"や"計算する数式を指定"する。
この「引数」により、従来は「エラー(#N/A) 」を表示させないためには、IF関数やISERROR関数を使ったり、条件付き書式で消したりしていたが、「引数」を指定するだけでエラー表示を回避できるようになりました。
⑤ 一致モード
" 0" 完全一致(※ 規定値)
"-1" 見つからないときに次に小さい値
" 1" 見つからないときに次に大きい値
" 2" ワイルドカードによる検索(※ あいまい検索"*"や"?"を使う)
⑥ 検索モード
どのように検索するか。
" 1" 先頭から末尾へ検索
"-1" 末尾から先頭へ検索
" 2" バイナリ検索(※ 昇順で並べられているとき)
"-2" バイナリ検索(※ 降順で並べられているとき)
※ "1"と"-1"の違いは、「検索値が複数存在していた時に、どの値を返したいか」で使い分けます。
② 検索の方向が、縦・横両方向に指定できる
従来のVLOOKUP 関数は縦方向(Vertical)にしか検索ができませんでした。
横方向(Horizontal)に検索するためには、HLOOKUP関数を使う必要がありました。
今度のXLOOKUP関数は1つで、両方向への検索に対応しました。
VLOOKUP関数は、縦方向に検索
検索対象は、表(選択範囲)の一番左の "列" に固定されます。
HLOOKUP関数は、横方向に検索
検索対象は、表(選択範囲)の一番上の "行" に固定されます。
XLOOKUP 関数は、縦方向・横方向の両方に検索
XLOOKUP 関数は、"MATCH関数"と同じように「検索する範囲を直接指定」できるので、縦・横両方向に検索することができます。
検索したい範囲が「左端」や「上端」にない場合でも検索できます。
縦方向に検索
横方向に検索
XLOOKUP関数の引数「見つからない場合」を使ってエラーを表示させない
引数「見つからない場合」に、空白「""」を入力することで、検索結果が「該当なし」の場合は「空白」が表示されます。
このように、XLOOKUP関数はVLOOKUP関数とHLOOKUP関数の両方の特徴を持つ優れた関数です。
そして、いままではエラーを表示させないようにするためには、IFERROR関数を組み合わせる必要がありましたが、「引数」を使うことで簡単にエラー表示を回避できます。
次回は、XLOOKUP関数と同じように「スピル配列の特性を持つ他の関数」を組み合わせる方法を説明します。
- XLOOKUP関数とFILTER関数を組み合わせる
- XLOOKUP関数とSORT関数を組み合わせる