Excel 検索値に一致するデータを検索する(XLOOKUP関数)※未実装関数

2019年9月15日

XLOOKUP関数はVLOOKUP関数と

HLOOKUP関数を統合し

使用上の問題点を解消した関数です。

2019年後半に実装予定です。

非常に使い勝手が改善しているため

今後はVLOOKUP関数から

XLOOKUP関数に移行していくと予想します。

MicroSoftサポートページ

さらにINDEX関数とMATCH関数の

メリットも取り込んでいるため

そちらからも移行していくでしょう。

仕様

XLOOKUP(検索値,検索範囲,返却範囲,一致モード,検索モード)
<検索値>
検索したい値を指定。
<検索範囲>
検索する列範囲を指定。 
<返却範囲>
検索結果とする列範囲を指定。 
<一致モード>
0:完全一致。見つからない場合は#N/Aエラー。省略した場合、0
-1:完全一致で見つからない場合はより小さい中で最も近い値を結果とする
1:完全一致で見つからない場合はより大きい中で最も近い値を結果とする 
2:ワイルドカードを指定する場合の指定
<検索モード>
1:先頭から順に検索。省略した場合、1
-1:末尾から順に検索
2:検索範囲が昇順で並んでいる前提で二分探索
-2:検索範囲が降順で並んでいる前提で二分探索 

使用例

Office Insiderにしても

新関数を使用できなかったので

仮定で記述します。

XLOOKUP関数を使用した場合に

変わる個所を赤字にしています。

関数の実行例

最初の引数(検索値)の指定は変わりありません。

新関数の改善点

検索範囲と取得項目の指定

二つ目の引数ではこれまで

表全体と取得する列(行)番号を

指定していましたが、

XLOOKUPでは検索する列と結果の列を

セル範囲で指定するようになります。

これにより検索対象(例ではコード)が

左端である必要がないと思われます。

更に取得項目を列番号を指定したため

表の項目が追加された場合、

VLOOKUP関数の数字を

全て変える必要がありましたが

列範囲にすることで

項目追加時に自動的に関数の数式が

更新されるようになります。

これまで通りが良ければ

$で固定すればそのままです。

また結果の返却がセル範囲になるようです。

これによりXLOOKUPの結果を

SUM関数などに組み込んだり

(この場合はSUMIFSでいいですが)

XLOOKUP関数を入れ子にして

クロス検索などが可能になるようです。

なお取得項目が列範囲指定になることで

コピー&ペーストもしやすくなります。

結果としてどちらが良いかは

まだ何とも言えませんが

検索対象の列が複数ある場合、

文字列結合でなくXLOOKUPの

入れ子で行えるかもしれません。

複数の結果を取得したい場合の

対処法がシンプルになるかもしれません。

ここの指定を縦方向にすればVLOOKUPに、

横方向にすればHLOOKUPになります。

検索の型(一致モード)

XLOOKUP関数の究極の改善点です。

検索の型は多くの場合、

FALSE(完全一致)を使いますが、

省略時がTRUE(あいまい検索)という

一番不便な箇所でした。

XLOOKUP関数では省略時、

FALSE(完全一致)になります。

これにより手間が減りますし、

初心者の方が苦戦する

FALSEてなんやねんや

省略して変な検索してしまう

トラブルがなくなります。

替わりに一致モードという指定になり

より使い勝手の良いものになっています。

指定内容
0完全一致。見つからない場合は#N/Aエラー。
省略した場合、この指定になります。
VLOOKUPでのFALSE指定から
ワイルドカード(*や?)が
使用できないなった指定です。
-1完全一致で見つからない場合は
より小さい中で最も近い値を結果とします。
VLOOKUPでのTRUE指定(多分)です
1完全一致で見つからない場合は
より大きい中で最も近い値を結果とします。
2ワイルドカードを指定する場合の指定です。

下記の記事のように

ワイルドカードを使用する場合は

2と明示する必要があるようです。

検索モード(新規追加)

これまでなかった指定で

検索速度をコントロールできます。

指定内容
1先頭から順に検索します。
省略した場合、この指定になります。
これまで通りの検索方法です。
-1末尾から順に検索します。
2検索範囲が昇順で並んでいる前提で二分探索
-2検索範囲が降順で並んでいる前提で二分探索

1はこれまで通りの検索方法です。

-1は後ろから検索する方法です。

この指定だと下記記事の対処が

不要になるかもしれません。

2とー2は表が並び替えが必要ですが

VLOOKUP関数の欠点であった

大きい表へ検索をかけた場合の

スピートの問題を解決します。

表が大きくなるほど

二分探索の方が早くなります。

関連記事

VLOOKUP関数の使い方・活用方法の記事一覧

試用版の機能を使用可能なOffice Insiderプログラムに参加する方法

フォローする