使うなら、最新関数 XLOOKUP

この記事は自分用の覚書です。

■MacBook Pro macOS Catalina
バージョン 10.15.3

■Office365 Business サブスクリプション
Excel for Mac バージョン 16.34 (20020900)

字余り……。

M.H さん。

Excel for Mac でXLOOKUP関数が使えるようになりましたね。
（さっき、Windows版 Excel も確認しました。バージョン2001（ビルド 12430.20264）です。）

すごいです。もうVLOOKUP関数で表引きしている時代ではありません。

使い慣れているからといって、いつまでもVLOOKUP関数を使うのは、うちの社内では「懐古主義に毒された老害だ」と言いたくなります。

これからはXLOOKUP関数の時代です！　と、全角のびっくりマークで強調したくなります。

XLOOKUP関数の使い方は、インターネット上でいろんな人が解説をしてくれているので、探せばいくつでも見つかります。

ここでは、私が使ってみて、うちの仕事で役立つと思った点にふれておきます。

マイクロソフトのサイトのヘルプを見ると、XLOOKUP関数には「Office 365」の表示があるので、Office365サブスクライバーだけが使える関数のようです。

・検索/行列関数 (リファレンス)

■引数「列番号」が不要

地味ですが、これがかなり便利です。

VLOOKUP関数の書式は次のようです。

=VLOOKUP(検索値,範囲,列番号,検索方法)

引数「列番号」を指定するのに、元の表の左端から地道に列の数を数えたことってありますよね。
「……9列目だから、9か……」とか。

それがもう不要です。

さらに、入力したVLOOKUP関数の数式を右にコピーするとき、「列位置」は変わりません。
「9 を 10 にして、次の 9 は 11 で、次は 12 で……」と地道な修正をしたこともありますよね？

それももう不要です。

「MATCH関数やCOLUMN関数を使えば列の位置がわかる」なんて話ではないです。
それをしなくてもよいという話ですからね。

XLOOKUP関数では、VLOOKUPの「列数」にあたる引数は、セル範囲で指定します。
書式は次のようです。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合], [一致モード], [検索モード]) 

「戻り範囲」のところがそうです。

必須の引数は「検索値」「検索範囲」「戻り範囲」の3つです。
うちの仕事なら、たまに引数「見つからない場合」を使うときはあるでしょうが、残りの引数を使うことはとても稀だと思います。

■XLOOKUP関数は「スピル」します。

VLOOKUPと同じ使い方をするなら、スピルの元になるセルに数式を入れるだけで、他のセルには自動的に数式が埋まります。
数式のコピーが不要ということですね。

「スピル」を説明するのは難しいです。手元でやってみてください。
「自動的に数式を埋めてくれる」と書きましたが、実際には元になるセル以外のセルには数式は入っていません。

スピルで埋まったセルを見ると、数式がグレーになっています。"ゴースト" というそうです。
ダブルクリックすると、セルの中には何も入っていません。それでも値は表示されます。

■列を抜き出したり、入れ替えたりが簡単

「列数の多い表で、特定の列だけ見えるようにしたい」ということがあります。

ちょっと前に、90列ほどある表から列をいくつか抜き出したことがありました。それも、並びを入れ替えて。
「12列目が一番左で、6列目がきて、23列目で、次が19列目で……」といった感じで。

90列もあると、飛び飛びの列だけが見えている状態にするのは、嫌になるほど面倒です。

そんなときも、XLOOKUP関数なら簡単です。

XLOOKUP関数では、引数「検索値」に表1行目の列見出しにある文字列を指定して、引数「検索範囲」では1行目全部を選ぶだけです。
（MATCH関数の要領ですね。）

で、その範囲から「検索値」が見つかったときに値を戻す「戻り範囲」には、表の2行目から下全部の範囲を選択します。
（引数「検索範囲」と「戻り範囲」は同じ大きさでないといけません。）

■引数「検索範囲」と「戻り範囲」を別々に指定できます。

XLOOKUP関数では、引数「検索範囲」と「戻り範囲」を別々のセル範囲にできます。

ということは、XLOOKUP関数では、表の列のどこであっても「検索範囲」と「戻り範囲」に指定できるということです。
右端でも左端でも途中でもどこの列でも検索値を探せて、どこの列からでも値を戻せます。
（この考え方は "行" に対しても同じです。）

なので、これまではINDEX関数とMATCH関数を組み合わせていた作業ですが、それも不要になるでしょう。
社内での使い方を考えると、全部XLOOKUP関数で対応できると思います。

「同じ結果が出るから、どちらのやり方でもよい」ということではないです。

これまでは2つの関数を組み合わせなければできなかったことが、1つの関数でできます。
効率の良さと、関数で何をしているかの読み解きやすさでいって、XLOOKUP関数を使うべきです。

うちの仕事でXLOOKUP関数が役立つと感じたところは以上のようです。

VLOOKUP関数に限らずINDEX関数も、Office365ユーザーではない人たちとのやり取りで互換のためだけに使うものだといってよいと思います。

ぜひ、お手元でXLOOKUP関数を試してみてください。何か気づいたことがあったら私にも教えてくださいね。