2020年、エクセルの新しい関数が使えるようになりました。

• XLOOKUP 関数
• XMATCH 関数

私が普段使っているGoogleスプレッドシートではまだ使えなかったので注目していませんでしたが、あれだけたくさんの関数が存在しているのに、まさか新しい関数が発表されるとは…私のエクセル人生で初めてのできごとだったのでかなり驚いたのを覚えています。奥の深い世界です。

そしてついに、GoogleスプレッドシートでもXLOOKUP関数とXMATCH関数が使えるようになりました（同じタイミングでLAMBDA関数と名前付き関数という機能が使えるようになっています）。

今回はXLOOKUP関数をVLOOKUP関数と比較してみたいと思います。

VLOOKUP関数のおさらい

VLOOKUP関数は、ある表の中から条件に合致するデータを取り出すときに使う関数です。

Googleドキュメントヘルプには

VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])

とありますが、私なりに言い換えてみると、

VLOOKUP(表の中から探すデータ,データを探す範囲,探す範囲の左から何番目のデータを取り出したいか,ひとまず何も考えずFALSEを入れる)

という構造になっています。「ひとまず何も考えずFALSEを入れる」の部分は「表の中から探すデータ」と完全一致するデータを取り出したいときは「FALSE」を入れるルールになっているからです。完全一致以外にデータを取り出すことがめったにないので、雑ですが、このように覚えています。実際の表を見てみましょう。

次の表は製品の「JANコード」と「型番」をまとめたリストです。セル：A2〜セル：B21の範囲から「4582269540955」の型番をセル：E3に取り出します。

セル：E3に入力する計算式は、次のようになります。

=VLOOKUP(D3,A:B,2,FALSE)

この計算式をあえて日本語に訳すとしたら「セル：D3に入力されている「4582269540955」を範囲A:BのA列から探して、一番左のA列から数えて2番目のB列に入力されているデータを取り出す」となります。

• 表の中から探すデータ
  • 「4582269540955」ですので、セル：Ｄ3を入れます。
• データを探す範囲
  • セル：A2〜セル：B21の中から「4582269540955」を探し出して、それに対応する型番を取り出したいので、データを探す範囲は「A:B」としました。「A2:B21」でも問題ありません。このとき、データを探す範囲の一番左の列を表の中から探すデータが存在している列に設定することがポイントです。
  • 「4582269540955」は範囲に指定した「A:B」のA列に存在していると思われるので「A:B」としました。もし、B列が「JANコード」の列でC列が「型番」だったら、範囲は「B:C」となります。
  • もし、「JANコード」がA列にあって、「型番」がC列にあったら、データを探す範囲は「A:C」になります。
• 探す範囲の左から何番目のデータを取り出したいか
  • 探す範囲（A:B）のB列にある「型番」を取り出したいので、左から2番目のデータを指定します。
  • 探す範囲が「A:B」のとき、1番目はA列、2番目はB列になります。
  • もし、探す範囲が「B:C」だったら、1番目はB列、2番目はC列になります。
  • もし、「JANコード」がA列にあって、「型番」がC列にあったら、3番目のC列を指定することになります。
• ひとまず何も考えずFALSEを入れる
  • 完全一致のなんのと前述しましたが…何も考えずにFALSEを入れます。

VLOOKUP関数はシンプルな構造でとてもわかりやすく、使いやすいです。ただ、難点をあげるとすれば「探す範囲の左から何番目に取り出したいデータが存在しているかを数えるのが大変なことがある」ということです。今回の例は、A列に「JANコード」、B列に「型番」がある表なので、「JANコード」の列から数えて2番目のデータを取り出せばよいだけでした。ただ、これが、A列に「JANコード」、AH列に「型番」がある表だったら、いったい何番目を指定すればよいのでしょうか。COLUMN関数を使えば、AH列がA列から数えて34番目に当たるのがすぐに分かるのでこの難点を回避することは確かにできます。しかし、計算式にすると、ちょっと複雑な印象になります。

=VLOOKUP(4582269540955,A:AH,COLUMN(AH1),FALSE)

※ 「JANコード」は直接、数値データとして表の中から探すデータに入力することが可能。

※ COLUMN – Google ドキュメント エディタ ヘルプ

COLUMN関数を使わずに書くと、

=VLOOKUP(4582269540955,A:AH,34,FALSE)

となります。トリニティではIY列（A列から数えて259番目）までフル活用している表がありますので、COLUMN関数を使わないと左から何番目を指定すればよいのかがわからないことが日常茶飯事です。

分かる人が見れば分かるのですが…計算式を作った本人も、ぱっと見では判別しづらいので避けたいところです。

それ何列目？　XLOOKUP関数が解決してくれる

ここで登場するのが、XLOOKUP関数です。最大のメリットは「探す範囲の左から何番目のデータを取り出したいか」を数える必要がないところにあります。

XLOOKUP 関数 – Google ドキュメント エディタ ヘルプ

先ほど書いたVLOOKUP関数の計算式をXLOOKUP関数で書くと、このようになります。

VLOOKUP関数の場合

=VLOOKUP(D3,A:B,2,FALSE)

XLOOKUP関数の場合

=XLOOKUP(D3,A:A,B:B)

VLOOKUP関数の場合、表の中から探すデータから取り出したいデータまでを範囲として指定する必要がありました。それがXLOOKUP関数だと、表の中から探すデータの存在しているであろう1列のみを指定すればOKです。そして、「探す範囲の左から何番目のデータを取り出したいか」の代わりに、取り出したいデータが存在してるであろう1列のみを指定すればOKです。先ほどの

=VLOOKUP(4582269540955,A:AH,34,FALSE)

にように34列目を指定しなければならない状況であっても、XLOOKUP関数を使うと、

=XLOOKUP(4582269540955,A:A,AH:AH)

とすればOKです。もし、型番がIY列にある場合でも、

=VLOOKUP(4582269540955,A:AH,259,FALSE)

を

=XLOOKUP(4582269540955,A:A,IY:IY)

とすればOK！　列数が多い表ではかなり重宝します。

今のところ、XLOOKUP関数が勝ちかなと…

使い始めてから2日しか経っていませんが、今のところ、XLOOKUP関数の便利さが使い慣れたVLOOKUP関数をはるかに勝る状況です。使わないことには使いこなせませんので、これからどんどん使っていきたいと思います。Googleスプレッドシートをお使いの方はぜひ活用してみてください！

★

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

▶ VLOOKUP関数で作った計算式をXLOOKUP関数に書き換えてみると　〜GoogleスプレッドシートでもXLOOKUP関数が使えるように〜(本ブログ)
▶ 【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技
▶ 計算式のコピペ漏れを防ぐ！　気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜
▶ VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
▶ Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
▶ IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
▶ 値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
▶ Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう！
▶ 「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜
▶ わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」
▶ 地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則（プルダウン）用のデータリストをつくる方法
▶ Googleスプレッドシートのフィルタ表示機能が便利
▶ Googleスプレッドシートでチェックリストを5分で作ってみる
▶ 営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた