【GoogleSpreadSheet】XLOOKUPで複数行を扱おうとして色々試した記録

ここ1年ほど、業務でスプレッドシートを使ったデータの集計や加工をする機会があり、日々、試行錯誤しながら触っています。

業務の中では 「一致するデータを抜き出す」 といった検索処理がよく出てきますが

• 1行ではなく複数行まとめて検索したい
• 抽出した値を加工・計算したい

といったケースもあり、どの書き方が良いのか悩むことがありました。

本記事では、XLOOKUP、INDEX・MATCHなどを使用して
複数行に対する検索処理について実際に試してみた結果、
うまくいかなかったことや、最終的にどの形に落ち着いたのかを
体験談としてまとめています。

スプレッドシートで一致する値を抜き出す方法

「一致するデータを抽出する」と言われて
パッと思いつくのは、だいたい次のあたりかと思います。

• VLOOKUP
• XLOOKUP
• INDEX + MATCH

VLOOKUPはXLOOKUPの登場により、現在ではあまり使われなくなってきているため、本記事ではXLOOKUPを中心に扱います。

XLOOKUP

XLOOKUPは一致する行を抜き出す代表的な関数です。

以下の3つを指定します。
=XLOOKUP(検索値, 検索する範囲, 返す範囲)

• 検索値
• 検索する範囲
• 返す範囲

例えば、A2の値をdataシートのA列から検索し、一致する行のB~D列の値を返す場合は以下のように書けます。

=XLOOKUP(A2, data!A2:A, data!B2:D)

dataシート

抽出結果

XLOOKUPは返す範囲に複数列を指定できるため、一致した行の値をまとめて取得することができます。
構文がシンプルで読みやすく、一致する行を 1つ取得したい場合 にはとても使いやすい関数だと感じています。

実務では、上記の検索処理を複数行に対してまとめて行いたいことがほとんどなので、
複数行に展開する方法を試してみます。

XLOOKUPの検索値に範囲を渡してみる

まずは、シンプルに以下の式を試しました。

=XLOOKUP(A2:A, data!A2:A, data!B2:D)

抽出結果

検索範囲に範囲を渡しても、エラーは起きないものの1行分しか結果が表示されませんでした。

おそらく複数行分の結果自体は返っているものの、行方向に展開されていない状態だと考え、次はARRAYFORMULAを試してみます。

ARRAYFORMULA × XLOOKUPで複数行の一致を抽出する

ARRAYFORMULAと組み合わせて、XLOOKUPの検索値に検索したい値の範囲を指定してみました。

=ARRAYFORMULA(XLOOKUP(A2:A, data!A2:A, data!B2:D))

複数行で実行されているものの、結果は1列目しか正しく表示されません。

XLOOKUPは返す範囲に複数列を指定できますが、検索値をA2:Aのように配列で渡した場合、スプレッドシートでは横方向（複数列）に展開されせず、1列目だけが展開される挙動になりました。

そのため、複数の行と列をまとめて取得する用途では、
ARRAYFORMULA × XLOOKUP では期待した形にはなりませんでした。

BYROW ・ LAMBDA × XLOOKUP

次に試したのは BYROW です。
BYROWは、指定した範囲を1行ずつ取り出し、各行に対して LAMBDA で定義した処理を実行します。
=BYROW(配列または範囲, LAMBDA(行の値, 処理))

• 配列または範囲：1行ずつ処理したい範囲
• 行の値：現在処理している行の値（1行分）
• 処理：その行に対して行いたい処理

BYROW は全ての行で実行されるため、
A列に値がない場合は空文字を出力するようにIF関数で分岐させておきます。

=BYROW(A2:A, 
  LAMBDA(id, 
    IF(id = "", "",
      XLOOKUP(id,  data!A2:A, data!B2:D)
  )
)

抽出結果

BYROWを使うと、各行ごとにXLOOKUPが実行されるため、期待通りの結果を得ることができました。

LETを使った加工・並び替え

ここからさらに、

• 列の並びを入れ替えたい
• 計算結果を出力したい

といったケースを考えます。

その場合はLETを使って、計算途中の値を一時的に変数として保持しながら処理を書くと、
式全体の意図が分かりやすくなります。

例えば、

• B列とC列の並びを入れ替えて出力
• E列に税込の金額を出力

したい場合、以下のように記述すればよさそうです。

=BYROW(A2:A, 
  LAMBDA(id, 
    IF(id = "", "",
    　LET(
      row, XLOOKUP(id,  data!A2:A, data!B2:D),
      name, INDEX(row,,1),
      category, INDEX(row,,2),
      price, INDEX(row,,3),
      inTAX, price * 1.1,
      HSTACK(category, name, inTAX)
      )
    )
  )
)

出力結果

XLOOKUPで取得した1行分のデータをrowに格納し
そこから必要な列を取り出して加工しています。

このようにBYROWとXLOOKUPを組み合わせることで
複数行に対する検索処理と、その後の加工をまとめて記述できました。

別の方法としてINDEX・MATCHも試してみる

ここまでは、BYROWとXLOOKUPを組み合わせる方法を見てきましたが
同じような処理はINDEX・MATCHを使って書くこともできます。

INDEX・MATCH は、
「一致する行番号を取得する処理」と
「その行の値を取り出す処理」を
明示的に分けて書ける点が特徴です。

MATCH：一致する値の行番号を返す
MATCH(検索値, 検索する値, 0）
第3引数の0は完全一致を意味するフラグです。

INDEX：指定された行番号の値を返す
INDEX(返す範囲, 行番号）

これらを組み合わせると、BYROW の中で次のように書けます。

=BYROW(
  A2:A,
  LAMBDA(id,
    IF(id = "", "",
      LET(
        row, MATCH(id, data!A2:A, 0),
        name, INDEX(data!B2:B, row),
        category, INDEX(data!C2:C, row),
        price, INDEX(data!D2:D, row),
        HSTACK(category, name, price)
      )
    )
  )
)

このように、INDEX・MATCH を使うと「どの列を参照しているか」を
式の中で明示的に書くことができます。

INDEX・MATCHでも同じ結果を得ることはできますが、
個人的には次の点で XLOOKUP の方が読みやすく感じました。

• 検索処理を1行でまとめて書ける
• 検索値、検索範囲、返す範囲という構造が直感的
• BYROW の中でも処理の流れを追いやすい

現時点ではINDEX・MATCH を使わないといけない
と感じる場面はあまりなく、BYROW × XLOOKUP を使うことが多くなっています。

まとめ

複数行に対して検索処理を行う場合、

• XLOOKUP単体では行方向に展開されない
• ARRAYFORMULA × XLOOKUP では列方向に展開されない
• 行ごとに実行できる BYROW を使うと、行・列のどちらにも展開できる

ということを実際に試してみて、学びました。

BYROWを組み合わせれば、XLOOKUP でも INDEX・MATCH でも同じ結果を得ることはできますが、
個人的には

• 検索処理を1行で書ける
• 検索値・検索範囲・返す範囲という構造が直感的
• BYROW の中でも処理の流れを追いやすい

といった理由から、
現時点では BYROW × XLOOKUPを使うことが多くなっています。