葛飾でひっそり暮らすIT屋の G Suite メモ

Google Apps で快適業務ライフなノウハウを伝えます

【Google Spreadsheet】列の左側の値をとるには INDEX と MATCH 関数を使おう

例えば、行列の中からメールアドレスを探して、その行の右側の列の値を取りたいといったときには VLOOKUP を使います。

でも、メールアドレスがもし、C 列にあって、メールアドレスをもとに A 列にある氏名をとってきたいというときには VLOOKUP は使えません。

そんなときに役立つのが INDEX 関数。

support.google.com

INDEX 関数自体は、指定した範囲の中から何行目の何列目にあるデータを参照するという関数ですが、他の関数と組み合わせると「左側の値を参照する VLOOKUP」というような使い方ができます。

基準となるセルの左右関係なく値を参照する際、INDEX と組み合わせる関数が MATCH でぇ~す。(古

support.google.com

例えば、下記の画像のような場合。

A1 から C7 までの範囲の中で 3行目かつ2列目のデータを参照するというようになっています。

あくまでも選択された範囲の中で何行目、何列目かということです。

例として、第二引数と、第三引数を変えずに範囲だけ変えてみます。

そうすると、第二引数に従って「A3」から数えて三行目、かつ二列目のセルの値を参照しています。

INDEX 関数の第二、第三引数はあくまでも第一引数で指定した範囲の中での相対位置ということに気をつけましょう。

そしてここから、MATCH 関数を組み合わせていきます。

まずは答えから。

INDEX 関数の第一引数で元になるデータ範囲を選択しています。ちなみに、セルの名前に $ マークがついているのは絶対参照といって、数式をコピーしても参照する範囲が変わらないようにするために付けています。

そして、第二、第三引数で MATCH 関数を使います。

第二引数は参照するデータの行数です。この例では、メールアドレスをキーにデータを探すので、メールアドレスが記載された列の何行目に、指定したメールアドレスがあるかが分かればいいわけです。

なので、MATCH 関数で検索する文字列をメールアドレスにして、それをメールアドレスの一覧がある列から検索します。

MATCH の第三引数はとりあえず 0 にしておきます。そうしないと意図しないデータが入る場合がありますので。

INDEX の第三引数の MATCH では項目名の位置を探します。

欲しいデータが何列目にあるか、数字で指定しても良いのですが、項目数が多くなったとき数式のコピペをした後にいちいち数字を書き換えなければいけません。

なので、MATCH 関数を使って、1列目の中の、指定した項目の位置を自動的に取得してしまおうという感じです。

なお、ところどころで $ マークの使い方が変わっていますが、絶対参照と相対参照を使い分けるとデータの参照が楽になりますので、使い分けを覚えてみて下さい。

こういう話って、入門書があると良いんでしょうけど、Google Apps のやつって良いのが無い気がしますね...

今では Excel と共通の関数も多いので Excel の本で勉強するのも手かもしれません。