見出し画像

スプレッドシートでXLOOKUP関数

カワムラ

2022年8月、Googleからスプレッドシート関数に関する新しい発表がいくつかありました。
その中で、Excelで好評だった「XLOOKUP関数」のサポートも発表されました。
自分なりの使い道を想定して記事をまとめます。

XLOOKUP関数の構文

XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)

◇検索キー: 検索する値を指定します。

◇検索範囲: 検索対象の範囲です。単一の行または列を指定する必要があります。

◇結果の範囲: 結果を取り出す対象の範囲を指定します。この範囲の行数または列数は、検索方法に応じて検索範囲の行数または列数と一致する必要があります。

◇見つからない場合の値: [省略可 - デフォルトは #N/A] 一致するものがない場合に返される値です。

◇一致モード: [省略可 - デフォルトは 0] 検索キーの一致とみなす基準を指定します。

◇検索モード: [省略可 - デフォルトは 1] 検索範囲を検索する方法を指定します。

使い方【1】いままでのVLOOKUPのように用いる

いままでのVLOOKUPのように用いるのであれば、書き方がよりシンプルになって良いなと思いました。

以下のシートを例にします。
列A-Eにデータがあり、セルG2に「案件No.」を入力して
セルH2-J2にそれぞれ「案件名/売上額/受注日」を返したい、とします。

まずは従来のVLOOKUPで書くとすると、

【VLOOKUPの場合】
セルH2_案件名を返す
=VLOOKUP(G2,B:E,2,0)

セルI2_売上額を返す
=VLOOKUP(G2,B:E,4,0)

セルJ2_受注日を返す
=VLOOKUP(G2,{B:B,A:A},2,0)

最後の「受注日を返す」だけ少々コツが要りますね。
検索範囲より結果範囲が左にあるため、波カッコ「{}」とコロン「,」を用いて列の順番を入れ替える必要がありました。

これに対し、XLOOKUPで書く場合はこちら。

【XLOOKUPの場合】
セルH2_案件名を返す
=XLOOKUP(G2,B:B,C:C)

セルI2_売上額を返す
=XLOOKUP(G2,B:B,E:E)

セルJ2_受注日を返す
=XLOOKUP(G2,B:B,A:A)

この例の場合、第四引数以降（見つからない場合の値, 一致モード, 検索モード）は不要としましたので簡潔に書くことが出来ました。
「結果の範囲」も列数で指定することになるので直感的に書くことが出来ます。VLOOKUPのように「列数を数える」ことが不要なのでその分ラクですね。

いったん第四～第六の引数の解説をします。

第四引数_見つからない場合の値

第四引数では「見つからない場合の値」を指定することが出来ます。
省略も可で、省略すると「#N/A」が返されます。
指定するとしたら...「0（ゼロ）」や空白を返すとよいかもしれませんね。
いままでVLOOKUPの上位にIFERROR関数を仕込むことも多かったですが、そこはスッキリしますね。

第五引数_一致モード

第五引数は「一致モード」の指定です。

  •  0 _完全一致

  •  1 _完全一致または検索キーより大きい次の値を一致とみなす

  • -1 _完全一致または検索キーより小さい次の値を一致とみなす

  •  2 _ワイルドカードの文字列に一致する値を一致とみなす

省略すると0 =完全一致です。
私はほとんどの場合で完全一致しか使用しないので省略することにします。

第六引数_検索モード

第六引数は「検索モード」の指定です。
こちらも私の場合は検索キーが2つ以上あるケースがほぼないので省略（＝デフォルトは「1」）しようと思います。

  •  1 _先頭から末尾に向かって検索

  • -1 _末尾から先頭に向かって検索

  •   2 _バイナリ検索で指定された範囲を検索。最初に範囲を昇順で並べ替える必要あり。

  •  -2 _バイナリ検索で指定された範囲を検索。最初に範囲を降順で並べ替える必要あり。

バイナリ検索…ピンとこないのでまたの機会に学んでみようと思います。（とりあえず今は必要なさそうなので保留）

使い方【2】複数の結果列を返す

XLOOKUPでは1つの関数式で複数の結果列を返すことが可能になりました。
これは便利そうですね。

返したい結果列の列順番を入れ替えなくてよい場合は
以下のようにシンプルに記述できます。

セルH2-J2_案件名,担当者,売上額を返す
=XLOOKUP(G2,B:B,C:E)
※セルH2に上記関数式を記述するだけでOK

返したい結果列の列順番を入れ替えたい場合でも
波カッコ「{}」とコロン「,」を用いれば実現できます。

セルH2-J2_案件名,受注日,売上額を返す
=XLOOKUP(G2,B:B,{C:C,A:A,E:E})
※セルH2に上記関数式を記述するだけでOK

使い方【3】水平方向に検索する

水平方向に検索する場合、今まではHLOOKUP関数を用いる必要がありましたが、XLOOKUPで済ませることが出来ます。

下の例はセルB4を検索キーとし、１行目を検索範囲、2行目を結果の範囲としてセルB5に担当を返します。

セルB5_担当先生を返す
=XLOOKUP(B4,1:1,2:2)

以上、自分が使用する状況を想定しXLOOKUP関数をまとめてみました。
XLOOKUP関数、非常に便利ですので今後も積極的に使用していこうと思いますが、いっぽうで長年慣れ親しんできたVLOOKUP関数と離れることに寂しさを感じてしまいます…

※VLOOKUP関数については過去に以下の記事を執筆しています。


この記事が気に入ったらサポートをしてみませんか？