Excel2021以降およびMicrosoft365からExcelにXLOOKUP関数が追加された。
XLOOKUP関数はVLOOKUP関数の完全上位保管である。
Excel 2021以降のExcelを使用できる環境ならVLOOKUP関数でなくXLOOKUP関数の利用がベター。
VLOOKUPの問題点
Excelで数多くある関数の中でも、使用頻度が高い関数の一つが「VLOOKUP」関数である。
検索値と一致した戻り値を返すことができる。
=VLOOKUP(検索値, 参照値を含む範囲, 戻り値を含む範囲内の列番号,近似一致または完全一致)
検索値：検索する値
参照値を含む範囲：参照値はデータの先頭の列である必要がある。参照値の列から戻り値を含む列全てを選択
戻り値を含む範囲内の列番号： 参照値の列から戻り値の列まで列の数
近似一致または完全一致：近似一致の場合はTURE、完全一致の場合はFALSEを指定する。
VLOOKUPを使用して社員名簿の社員番号から社員名を抽出する例。
この例では、社員番号がA004の「田中 洋平」が社員名として抽出される。
- 参照列より左側の値を取得することができない。
- 一致しなかった場合はエラーになる。
XLOOKUP関数は、上記のVLOOKUP関数の不便な点も解決できる。
XLOOKUP関数の使い方
XLOOKUP関数の書式は下記です。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合の表示], [一致モード], [検索モード])
検索値：検索する値
検索範囲：検査する配列(または範囲)
戻り範囲： 返す値の配列(または範囲)
見つからない場合の表示(省略可)：一致しなかったときの表示方法の指定（デフォルトは#N/A）
一致モード(省略可)： 一致の種類の指定(デフォルトは０)
0 ：完全一致。 見つからない場合は、見つからない場合の表示で指定した値が返される。
-1 ：完全一致。 見つからない場合は、次の小さなアイテムが返される。
1 ：完全一致。 見つからない場合は、次の大きなアイテムが返される。
2 ：ワイルドカード（*、?、〜など）を使用した一致。
検索モード(省略可)：使用する検索モードの指定（デフォルトは1）
1 ：先頭から検索。
-1 ：末尾から検索。
2 ：バイナリー検索（検索値が昇順の時のみ使用可）。
-2 ：バイナリー検索（検索値が降順の時のみ使用可）。
検索値、検索範囲、戻り範囲
上記でVLOOKUP関数を使って社員番号から社員名を抽出したのと同じことをXLOOKUP関数を使って行うとしたの図のようになる。
第１引数の検索値は、VLOOKUPと同じように指定する。
第２引数の検索範囲は、検索列(または範囲）のみを指定する。
VLOOKUPの第2引数は検索する列と戻り列の両方を含む範囲なので違いに注意すること。
第3引数の戻り範囲は、返したい値の列（または範囲）を指定する。
VLOOKUPでは参照列から何番目の列なのかを指定する。
列の数を数えることなく、抽出したい列を指定するだけのため、直感的で操作も楽になっている。
また、表の列の順番を入れ替えても関数の修正がいらないのもメリットである。
（列のカット＆挿入で列を入れ替えたら自動的にとXLOOKUP関数で指定した列が変更される。）
そのため、戻り列を検索列の左側に変更しても抽出できる。
VLOOKUP関数の不便な点の１つ目はXLOOKUP関数では、このように解決できる。
ちなみに、INDEX関数とMATCH関数を組合せて使えば、参照列の左側の列から値を得ることができる。
XLOOKUP関数を使えば、より簡潔で分かりやすい表記ができる。
見つからない場合の表示
XLOOKUP関数では、検索結果が見つからなかった時の表示を第4引数で指定することが出来る。
例えば、第4引数を“見つかりません”として、存在しない社員番号「A011」を検索値にしていすると下の図のように、第４引数で指定した「見つかりません」と表示される。
VLOOKUP関数で「#N/A」エラーとなる。XLOOKUP関数と同じ処理をするにはIFERROR関数と組合せる必要がある。XLOOKUP関数では単体の関数でエラー時の表示を指定できるため、簡潔に表示することができVLOOKUP関数の２つ目の不便な点も解決できる。
一致モード
XLOOKUP関数の第5引数で一致モードの指定ができる。
VLOOKUP関数では、近似一致と完全一致しかなく、ほとんどの場合は「完全一致」を使用する。
近似一致は元データの検索値を昇順しないと正常に動作しないなどの制約があり非常に使用しにくい。
XLOOKUP関数の一致モードは４つある。
- 0 ：完全一致。 見つからない場合は、見つからない場合の表示で指定した値が返される。
- -1 ：完全一致。 見つからない場合は、次の小さなアイテムが返される。
- 1 ：完全一致。 見つからない場合は、次の大きなアイテムが返される。
- 2 ：ワイルドカード（*、?、〜など）を使用した一致。
一致モードは省略可能であり、省略すると「0」の完全一致が適用される。
一致モードに「-1」を設定した時の動作
一致モードに「-1」を設定すると完全一致しなかったときは、次に小さなアイテムが返される。
下の図の例では、一致モードを「-1」にして年齢が35の社員名を返すようにしてある。
35歳の社員は存在しないので、35歳の次に若い31歳の「井口 はるか」さんが抽出される。
一致モードに「1」を設定した時の動作
一致モードに「1」を設定すると完全一致しなかったときは、次に大きなアイテムが返される。
下の図の例では、一致モードを「1」にして年齢が35の社員名を返すようにしてある。
35歳の社員は存在しないので、35歳の次に年を取った36歳の「安藤 隆」さんが抽出される。
一致モードに「2」を設定した時の動作
一致モードに「2」を設定した時の動作を確認する。
一致モード「2」を設定するとワイルドカードを使った検索値の一致ができる。
下の図は、XLOOKUP関数で検索値にワイルドカードを使用し、名字が「田村」という社員の社員番号を抽出した例である。
検索モード
XLOOKUP関数の第6引数で検索モードの指定ができる。
XLOOKUP関数の検索モードは４つある。
- 1 ：先頭から検索。
- -1 ：末尾から検索。
- 2 ：バイナリー検索（検索値が昇順の時のみ使用可）。
- -2 ：バイナリー検索（検索値が降順の時のみ使用可）。
検索モードに「1」を設定もしくは省略した時の動作
検索モードは省略可能であり、省略すると「1」の先頭から検索される。
田村から始まる社員が２名いるが先頭に近い方の「田村 由香」さんの社員番号「A007」が抽出されている。
検索モードに「-1」を設定した時の動作
検索モードに「-1」を設定すると末尾から検索される。
田村から始まる社員が２名いるが末尾に近い方の「田村 沙耶」さんの社員番号「A011」が抽出されている。
検索モードに「2」を設定した時の動作
検索モードに「2」を設定した時の動作は、バイナリー検索となる。
元データを昇順にする必要があるが、検索スピードは高速となる。
検索結果は検索モード「1」と同じになる。
大量のデータを処理をする場合は処理時間を短縮することも可能である。
検索モードに「-2」を設定した時の動作
検索モードに「-2」を設定した時の動作は、バイナリー検索となる。
検索モード「2」との違いは元データが降順にする必要があるところである。
(検索列および戻り列は列全体を指定するとエラーになる。)
検索結果は検索モード「-1」と同じになる。
大量のデータを処理をする場合は処理時間を短縮することも可能である。
XLOOKUP関数の使い方（応用編）
戻り範囲を複数列指定する
XLOOKUP関数は戻りを一列でなく複数列していすることができる。これにより入力の手間を大幅に省くことがかのうとなる。これはスピルという機能を活用している。
数式が複数の値（配列）を返す場合、数式を入力したセルを起点として、セル範囲に自動で値が表示される機能のこと。
社員番号に一致したときに、社員名、性別、年齢のすべてを抽出するのを１つの式で行うことができる。
第三引数の戻り範囲にB列からD列を指定する。
式はH2のセルにしか入力していないが、I2およびJ2のセルにも自動的に抽出した値が表示される。
列方向の検索
XLOOKUP関数は行方向の検索だけでなく、HLOOKUP関数のように列方向の検査をすることもできる。
使い方は、検索範囲および戻り範囲を列を指定するのではなく、行を指定するだけである。
