【中級者向け】XLOOKUP関数よりもVLOOKUP関数を使った方が良いケース3選（Googleスプレッドシート）

ExcelやGoogleスプレッドシートで人気の高い VLOOKUP関数、XLOOKUP関数について書きました。

両者の機能比較ではなく、こういったケースは VLOOKUP関数の方が便利だよ～って内容です。

久々のお題あり、関数ネタ回です。

先週は QuickTipsとして、Googleスプレッドシート 説明入りプルダウンを作成する方法 について書いております。

春になるとXLOOKUP推しの記事が増える

なんとなく毎年 春になると「VLOOKUP関数よりXLOOKUP関数を使え！」ってネット記事を目にする気がします。

この時期、新社会人をターゲットにしたExcel使い方ネタが受けるってことなんでしょう。

上で挙げたものは Excelの記事ですが、VLOOKUP関数、XLOOKUP関数は基本的には Googleスプレッドシートでも（ほぼ）同じように利用が可能です。

しかし、本当に XLOOKUP関数があれば VLOOKUP関数は不要なんでしょうか？

否！

もちろん 後発のXLOOKUPは痒いところに手の届く便利な関数であることは間違いありません。

ちなみに Googleスプレッドシートでは、XLOOKUP関数は 2022年9月に Excelから輸入され利用できるようになりました。

VLOOKUPの不便な点が諸々解消されており、関数のわかりやすさ、使い勝手の良さから XLOOKUPを絶賛している記事が多いのもわかります。

しかし、VLOOKUP関数を使った方が良いケースもあります！

このnoteを読んで VLOOKUP関数とXLOOKUP関数 どっちを使ったらいいのーって場面を、キュアっと解決です！

なお、わかりやすいように数式を入れたセル以外に結果が出力されることを「スピル」と表現していますが、本来「スピル」はExcelの機能であり Googleスプレッドシートで結果の配列が複数セルに展開される機能は、正しくは「スピル」とは言いません。

また、今回の記事は VLOOKUPとXLOOKUPを 標準的な使い方で利用できる方を対象とした内容となっています。

VLOOKUP関数を使った方がよいケース 3選

XLOOKUPは非常に便利で使い勝手のいい検索関数です。

ただ、Googleスプレッドシートでの利用を前提としてですが、XLOOKUP関数よりもVLOOKUP関数を使った方がよいケースがあります。

mirが考える VLOOKUP関数を使った方がよいケースは、

1. シンプルな2列のデータを検索対象とするケース

2. 別のスプレッドシート（ブック）のデータを検索対象とするケース

3. 縦横スピルさせたいケース

この3つです。

1. シンプルな2列のデータを検索対象とするケース

まず1つ目は、シンプルな 2列データを対象とするケースです。

D4の値をキーとして、1列目（A2:A21）を検索、一致した行の右側（B2:B21） を結果として出力する。これを式にすると

こうなります。

範囲指定が1回で済むこともあり、個人的にはVLOOKUP関数を使った方が素直な記述かなと思います。

VLOOKUP関数の方は、完全一致検索とする為に 第4引数を FALSE指定する必要があるので、XLOOKUPよりも式は長く見えてしまいますが、FALSEは 0 で代用可能です。

まぁ わざわざ FALSE指定が必要で、省略時はTRUE扱い 完全一致検索にならない点が、 VLOOKUPを複雑にしている一因でもあり、XLOOKUPはこの点が解消されてるんですが・・・。

同じシートの表だと2つの式にあまり差を感じないかもしれませんが、別シート（顧客マスタS）の表を検索対象とする場合は、

このように 検索範囲と結果範囲（戻り範囲）をそれぞれ記述する必要があるXLOOKUPよりも VLOOKUPの方がぐっと式がシンプルになります。

ちなみにVLOOKUPの完全一致を極限まで省略（短く）したい場合は、第4引数を空白にするという方法があります。

=VLOOKUP(A4,'顧客マスタS'!A2:B21,2,)

第4引数を省略だと TRUE扱いになりますが、カンマ（,）だけを入れて第4引数の箇所に何も入れない（空白とする）と、0扱いつまり FALSEと見なされる為です。

ま、わかりづらいのでお勧めはしませんが・・・。

とりあえず 2列データなら、XLOOKUPを使うまでもないって感じですね。

2. 別のスプレッドシート（ブック）のデータを検索対象とするケース

VLOOUPの方がよりシンプルに記述できる、もっとも顕著な例が 別スプレッドシート（別ブック）の表を検索対象とする場面です。

これが2つ目のVLOOKUPを使った方がいいケースです。

Googleスプレッドシートで、別のスプレッドシートのデータを取得する時は IMPORTRANGE関数の出番です。

別のスプレッドシートを呼び出す際に、スプレッドシートのID（SSID）が必要となるんですが、これはIMPORTRANGE関数内に直接記述するよりも

設定シートを用意して、そこで番号、シート名、SSIDと表を作成し 名前付き範囲として設定することをお勧めします。

名前付き範囲を設定しておけば

このように シンプルな式で、他のスプレッドシートから値を取得できます。

これをそのまま出力するのではなく、VLOOKUPやXLOOKUP の第2引数（XLOOKUPの場合は第2、第3引数）に使用して検索し、検索結果だけを出力したいケースが結構あります。

この時、

このように VLOOKUPとXLOOKUPで、式の長さに大きな差が出ます。

式が長くて煩雑というだけでなく、XLOOKUPの方はIMPORTRANGEで別スプレッドシートを2回呼び出している為、処理としても重くなっていると思われます。

細かいことは抜きにしても、このケースだとVLOOKUPはスッキリしていて、XLOOKUPの方は無駄が多い気がしますよね。

さらに IMPORTRANGEはテーブル機能とも相性がよく、テーブル名や構造化参照を文字列で記述することでデータを呼び出せるんですが、

検索対象の表が別スプレッドシートのテーブル（顧客マスタSというテーブル名）だった場合も

このようにVLOOKUPはスッキリ記述出来ます。

※ただし読みやすさ（どんな処理をしているか？）という観点だと、VLOOKUPの式よりも XLOOKUPの式の方が丁寧かもしれません。

IMPORTRANGEを使って他のスプレッドシートを参照するケースを例としていますが、検索対象の表を長い数式の中で生成するようなケースも同様です。

3. 縦横スピルさせたいケース

最後の3点目は、ARRAYFORMULA関数と組み合わせた時の挙動の違いです。

様々な点で VLOOKUPより強化されていたり、VLOOKUPの弱点が解消されているXLOOKUP関数ですが、実は ARRAYFROMRULA関数と組み合わせても 縦横スピルできない（縦横のセルに配列展開されない）という弱点があります。

例をあげると

4列構成の 担当者一覧表から、担当者コードで検索して 結果として 氏名、部門コード、部門名を 出力したい。

こんな時 XLOOKUP関数であれば

とARRAYFORMULA無しで横3列にスピル（配列が出力）されます。

一方、VLOOKUPの場合は 単に 第3引数を {2,3,4} と配列化しただけでは スピルせず、

このように ARRAYFORMULA関数を組み合わせる必要があります。

ここまでは XLOOKUPの方が便利じゃーん！ってなるんですが、

これを F4:F6 の複数の担当者コードに対して、一つの式で 検索で一致した 氏名、部門コード、部門名を出力したいとなると

なぜかXLOOKUPは横方向にはスピルせず、縦方向のみスピルとなります。

ARRAYFORMULA無しだと、そもそも縦スピルが機能しません。

これがVLOOKUP関数とARRAYFORMULAの組み合わせであれば、

このように縦横に配列が展開する（スピルする）んです。

「ARRAYFORMULAでVLOOKUPは縦横スピルできる！」

これが VLOOUP関数の魅力であり、まだまだ出番があると言える大きな理由の一つです。

VLOOKUP使ってまだ列の番号数えてるの？って言われたら

XLOOUP勢がVLOOKUPをディスる際によく出てくるのが、「まだ列の番号数えてるの？」です。

上で挙げた2列データのようなシンプルな表ならいいんですが、確かに数十列におよぶ表だと 取得したい列が、

これ何列目？ ってなるのもわかります。

ただ、目視で数えるなんてことをする必要はなく、基本的に見出しに空白がないのであれば、キー列から対象列までの見出しを選択すれば、

右下のカウントと出た数字が行数（行番号）になります。

見出しがなくて 空白セルが間に存在する場合でも、キー列から取得したい列までを範囲選択して右クリックすれば

このように列の挿入メニューから 列数（列番号）が確認できます。

もちろん、よりフレキシブルで エラーに強い形にしたいのであれば

VLOOKUP + MATCH または VLOOKUP + XMATCH で 取得したいデータの見出しの列番号を検索するという方法も有効です。

※データベースの基本ですが、検索対象の表に 見出しがあること、そして見出しに重複がないことが前提となります

ARRAYFORMULA + VLOOKUPで SQLの JOIN（LEFT JOIN)みたいな処理ができる

VLOOKUP関数の縦横スピルの応用例を見てみましょう。

Googleスプレッドシートにおいて、SQL（データベース）的な処理ができる関数といえば QUERY関数ですが、残念ながらQUERY関数には複数のテーブルを結合する JOIN がありません。

ここで活躍するのが VLOOKUP関数です。

Q1. 左のテーブル（顧客マスタ）に右のテーブル（担当者マスタ）を 担当者コードを条件として統合したい

それではお題いってみましょう！

左側のテーブル「顧客マスタ」の一番右の列「 顧客コード」をキー（条件）として、右側のテーブル「担当者マスタ」の情報を結合して一つの表にしたい

つまり 👆 この2つのテーブルを 担当者コードをキーに

👆こんな風に統合したい場合、どんな式を組めばよいでしょうか？

データはこちらを 👇

顧客コード	氏名	メールアドレス	電話番号	都道府県	担当者コード		担当者コード	氏名	部門コード	部門名
C001	佐藤 健太	sato.k@example.jp	080-1234-5678	東京都	S001		S001	山田 太郎	D01	営業部
C002	鈴木 美咲	suzuki.m@example.com	090-2345-6789	神奈川県	S002		S002	佐藤 花子	D02	企画部
C003	高橋 直樹	takahashi.n@example.net	070-3456-7890	千葉県	S003		S003	鈴木 一郎	D03	総務部
C004	田中 結衣	tanaka.y@example.jp	080-4567-8901	埼玉県	S004		S004	高橋 健二	D01	営業部
C005	伊藤 翔太	ito.s@example.com	090-5678-9012	大阪府	S005		S005	伊藤 結衣	D02	企画部
C006	渡辺 奈々	watanabe.n@example.net	070-6789-0123	兵庫県	S001					
C007	中村 拓也	nakamura.t@example.jp	080-7890-1234	京都府	S002					
C008	小林 莉緒	kobayashi.r@example.com	090-8901-2345	愛知県	S003					
C009	加藤 浩志	kato.h@example.net	070-9012-3456	福岡県	S004					
C010	吉田 愛	yoshida.a@example.jp	080-0123-4567	北海道	S005					
C011	山田 太郎	yamada.t@example.com	090-1234-5678	宮城県	S001					
C012	佐々木 陽子	sasaki.y@example.net	070-2345-6789	広島県	S002					
C013	山口 健一	yamaguchi.k@example.jp	080-3456-7890	静岡県	S003					
C014	松本 恵	matsumoto.m@example.com	090-4567-8901	岡山県	S004					
C015	井上 裕介	inoue.y@example.net	070-5678-9012	熊本県	S005					
C016	木村 沙織	kimura.s@example.jp	080-6789-0123	長野県	S001					
C017	林 亮介	hayashi.r@example.com	090-7890-1234	岐阜県	S002					
C018	斎藤 舞	saito.m@example.net	070-8901-2345	滋賀県	S003					
C019	清水 大輔	shimizu.d@example.jp	080-9012-3456	奈良県	S004					
C020	森 七海	mori.n@example.com	090-0123-4567	三重県	S005					

コピペして左側の表をテーブル化して顧客マスタ、右側の表をテーブル化して担当者マスタとして利用ください。

ARRAYFORMULA ＋ VLOOKUP を使うことを条件とします。また、余裕があれば汎用性の高い式（テーブルの列数が変わっても対応できる式）としてみましょう。

考えてみましょう！

↓↓
回答はここから。
↓↓

A1. 左のテーブル（顧客マスタ）に右のテーブル（担当者マスタ）を 担当者コードを条件として統合する

回答です。こんな式で2つの表の結合が出来ます。

=LET(
  x,顧客マスタ[#ALL],
  y,担当者マスタ[#ALL],
  ARRAYFORMULA(HSTACK(
    x,
    IFNA(VLOOKUP(CHOOSECOLS(x,-1),y,SEQUENCE(1,COLUMNS(y)-1,2),FALSE))
  ))
)

まず どちらのテーブルも式の中で2回登場するので 簡略化のため、LET関数で

x,顧客マスタ[#ALL],
y,担当者マスタ[#ALL],

とします。

データを横に結合するので、HSTAK関数の出番です。左側は xそのままでいいですね。

見栄え的に ARRAYFORMULAはLETの外側、もしくはHSTACKの外側に入れておくとよいでしょう。

VLOOKUPの第1引数 で利用する検索キーですが、

顧客マスタ[[#ALL],[担当者コード]]

としてもいいんですが、式に 汎用性をもたせるたいので 右側のテーブル x の一番右の列を取得するのってことで、

CHOOSECOLS(x,-1)

としています。CHOOSECOLSは第2引数でマイナスの数値を指定すれば、右側から1列目、2列目、3列目と指定出来るのが便利です。

さらに VLOOKUP 第3引数 {2,3,4} の部分を 

SEQUENCE(1,COLUMNS(y)-1,2)

としています。

検索値が見つからなかった時のために IFNA関数を入れておくとよいでしょう。（GoogleスプレッドシートのIFNA関数は 第2引数省略で 空白を返します）

結構実務で結構出番のある処理です。

MAPを使えば XLOOKUPも縦横スピル可能

ここまでは、VLOOKUP アゲで書いてきました。

ただ、XLOOKUPは ARRAYFORMULA関数と組み合わせて縦横スピル出来ないだけであって、実は MAPなどのLAMBDAヘルパー関数と組み合わせれば、XLOOKUPも縦横スピルが可能となります。

記述にやや癖がありますが、式も短いですし結構すっきり書けますね。

じゃあ、縦横スピルさせたいケースは XLOOKUPでもいいじゃない。って思う人もいうでしょう。

確かに、

• ARRAYFORMULA + VLOOKUP は XLOOKUP登場前から利用できた Googleスプレッドシートの縦横スピルの書き方で、非常に汎用性が高いテクニックだった

• MAP関数がGoogleスプレッドシートに登場した当初は、配列のネスト不可で MAP + XLOOKUPによる縦横スピルが出来なかった（MAPが実装された約2ヶ月後にひっそりとアップデートされ、配列ネスト対応になった）

このような歴史的背景から、mirの好みで ARRAYFORMULA + VLOOKUPを推してる部分はあるかと思います。

ただ、出力列を可変対応させた 縦横スピルが必要な検索の場合は、MAP+XLOOKUPだと結構面倒だったりします。これはお題３で実感いただけます。

両方を理解したうえで、自分の使いやすい方を選択するのがおすすめです！

Q2. 左のテーブル（顧客マスタ）に右のテーブル（担当者マスタ）を 担当者コードを条件として統合したい（XLOOKUPで）

では、ここでお題いってみましょう。

上で登場したテーブル結合ではVLOOKUP関数を使いましたが、これをXLOOKUPに置き換えると、どのように書けるでしょうか？

データはそのままQ1のものを利用ください。考えてみましょう！

↓↓
回答はここから。
↓↓

A2. 左のテーブル（顧客マスタ）に右のテーブル（担当者マスタ）を 担当者コードを条件として統合する（XLOOKUPで）

回答です。

=LET(
  x,顧客マスタ[#ALL],
  y,担当者マスタ[#ALL],
  HSTACK(
    x,
    MAP(CHOOSECOLS(x,-1),LAMBDA(
      v,
      XLOOKUP(v,INDEX(y,,1),CHOOSECOLS(y,SEQUENCE(COLUMNS(y)-1,1,2)),)
    ))
  )
)

HSTACKで横に連結する処理は一緒ですね。

担当者コードで引き当てる処理を MAP + XLOOKUP で書けばよいのですが、両方のテーブルに存在する 担当者コードの列を1つにする点がやや面倒だったりします。

Googleスプレッドシートには ExcelのDORP関数に該当する関数がありません。

その為、

FILTER関数＋SEQUENCE関数　または

CHOOSECOLS関数＋SEQUENCE関数

で代替する必要があります。

今回は

こんな式で1列目を除外しています。

やや乱暴ですが 完全に重複する列は存在しないことを考えると UNIQUE関数で横方向のユニーク化（第2引数 TRUE指定）で、 担当者コード列を1つにしてしまうといった

=LET(
  x,顧客マスタ[#ALL],
  y,担当者マスタ[#ALL],
  z,HSTACK(x,MAP(CHOOSECOLS(x,-1),LAMBDA(v,XLOOKUP(v,INDEX(y,,1),y,)))),
  UNIQUE(z,1)
)

こんな式もアリですね。

よっぽどの大量データだと、もしかしたら処理速度に差が出るかもしれませんが、

• ARRAYFORMULA + VLOOKUP

• MAP + XLOOKUP

どちらもGoogleスプレッドシートで 縦横スピル処理させたい時の便利な組み合わせです！

結局 XLOOKUPだけでも色々事足りるけど、このように VLOOKUPを使った方がシンプルに書ける処理もあると覚えておくとよいでしょう。

Q3. 顧客マスタから複数のIDそれぞれに一致する行から、可変で指定した列のデータを取得したい（VLOOKUP または XLOOKUPで）

最後にちょい応用例のお題に挑戦してみましょう。

VLOOKUP、XLOOKUPどちらを使ってもOKです。

左の顧客マスタ（B1:G21）の1列目（顧客コード）をキーとして、右の表の I5:I10で 指定したコードに一致する行の 指定した列（項目）のデータを取得したい。

ただし、

B1:G21 を最初に LET関数で array として、これを利用するものとします。

出力する見出しは J4:L4のプルダウンで可変とし、見出しが空白の場合はその列は空白とするようにするものとします。（エラー処理）

データは 👇こちらをB1に貼って利用ください。式は J5セルに1つ入れるものとします。

顧客コード	氏名	メールアドレス	電話番号	都道府県	担当者コード					
C015	井上 裕介	inoue.y@example.net	070-5678-9012	熊本県	S005					
C016	木村 沙織	kimura.s@example.jp	080-6789-0123	長野県	S001					
C017	林 亮介	hayashi.r@example.com	090-7890-1234	岐阜県	S002		顧客コード	メールアドレス	電話番号	都道府県
C007	中村 拓也	nakamura.t@example.jp	080-7890-1234	京都府	S002		C005			
C019	清水 大輔	shimizu.d@example.jp	080-9012-3456	奈良県	S004		C019			
C009	加藤 浩志	kato.h@example.net	070-9012-3456	福岡県	S004		C016			
C008	小林 莉緒	kobayashi.r@example.com	090-8901-2345	愛知県	S003		C010			
C013	山口 健一	yamaguchi.k@example.jp	080-3456-7890	静岡県	S003		C005			
C014	松本 恵	matsumoto.m@example.com	090-4567-8901	岡山県	S004		C020			
C011	山田 太郎	yamada.t@example.com	090-1234-5678	宮城県	S001					
C002	鈴木 美咲	suzuki.m@example.com	090-2345-6789	神奈川県	S002					
C006	渡辺 奈々	watanabe.n@example.net	070-6789-0123	兵庫県	S001					
C004	田中 結衣	tanaka.y@example.jp	080-4567-8901	埼玉県	S004					
C020	森 七海	mori.n@example.com	090-0123-4567	三重県	S005					
C003	高橋 直樹	takahashi.n@example.net	070-3456-7890	千葉県	S003					
C010	吉田 愛	yoshida.a@example.jp	080-0123-4567	北海道	S005					
C018	斎藤 舞	saito.m@example.net	070-8901-2345	滋賀県	S003					
C005	伊藤 翔太	ito.s@example.com	090-5678-9012	大阪府	S005					
C012	佐々木 陽子	sasaki.y@example.net	070-2345-6789	広島県	S002					
C001	佐藤 健太	sato.k@example.jp	080-1234-5678	東京都	S001					

VLOOKUP、XLOOKUP好きな方を利用ください。可能な人は両方にチャレンジをぜひ！

考えてみましょう！

↓↓
回答はここから。
↓↓

A3. 顧客マスタから複数のIDそれぞれに一致する行から、可変で指定した列のデータを取得したい（VLOOKUP または XLOOKUPで）

回答です。まずはVLOOKUPを利用した場合。

=ARRAYFORMULA(LET(
  array,B1:G21,
  IFNA(VLOOKUP(
    I5:I10,
    array,
    XMATCH(J4:L4,INDEX(array,1)),
    FALSE
  ))
))

XMATCHで arrayの1行目 INDEX(array,1) を対象に見出しで検索した結果を列番号として、VLOOKUPの第3引数に利用しています。

VLOOKUPをIFNAでネストすることで、見出しが空の列だけ空白を返すことが出来ます。

割とシンプルですね。

一方、XLOOKUPを使った回答はちょっと複雑です。

=LET(
  array,B1:G21,
  c,MAP(J4:L4,LAMBDA(v,XLOOKUP(v,INDEX(array,1),array,))),
  IFNA(MAP(I5:I10,LAMBDA(v,XLOOKUP(v,INDEX(array,,1),c,))))
)

対象の列だけの配列を得るために見出し名をキーとして MAP+XLOOKUP（横方向の検索）を実行し、その結果を cと置き今度は 顧客コードをキーとして MAP + XLOOKUP（縦方向の検索）を実行しています。

XLOOKUPなのにわざわざ IFNA関数を付けているのは、

このように見出しがすべて空のケースだと、一つ目の MAP+XLOOKUPの結果 cが空となってしまい、2つ目のXLOOKUPの式の 第2引数と第3引数のサイズ違いが発生してエラーとなってしまう為です。

これはXLOOKUPの第4引数で回避できないので、IFNAでネストしています。

VLOOKUPを使った方がよいケース、実感いただけたでしょうか？

ExcelのXLOOKPやVLOOKUPはGoogleスプレッドシートとちょっと違う仕様

今回はGoogleスプレッドシートの XLOOKUPよりもVLOOKUPを使った方がよいケース3選を紹介しました。

実はこのネタ4月の初旬くらいに書こうと思ったんですが、先に イネさんが noteに書かれたネタと重複する部分もあり、後追いみたいになってしまいました・・・。

アプローチは違うものの、言いたいことは近いかなと思います。

で、冒頭で ExcelのXLOOKUP、VLOOKUP と Googleスプレッドシートの XLOOKUP、VLOOKUPは、ほぼ同じ感覚で使えると書いたんですが・・・

実はExcel版は 微妙に 仕様が違っていて同じようにいかなかったり、Googleスプレッドシート版には無い便利な機能があったりします。（基本的には同じ感覚で使えますが）

マニアックなネタなんで、触れているサイトもあまり見かけませんが、次回は

Googleスプレッドシート職人からみた「Excelの XLOOKUP、VLOOKUPのユニークな仕様」

について書きたいと思います。（GW中でビューは伸びなそうですがｗ）