Googleスプレッドシートでのデータ検索(indexとmatchとvlookupとqueryと)


**(追記 2022-11-22)**
2022年9月から、XLOOKUPが使えるようになったようで、もうこの記事の意味がなくなってしまったようです。新技術って素晴らしい！ｗ

↓こちらの記事参照
[VLOOKUPはもうオワコン!? 新登場の最強関数XLOOKUPを使ってみた in Googleスプレッドシート](https://qiita.com/CurryPrince/items/65a0cd5bbd2b8e78d6da)




# この記事は何？

![p1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/b97c7cbf-d7a4-a679-c521-5a51c7382672.png)


上記の表において、**「商品名」から「ID」を探したい** ときにどうするか。
**vlookupは「指定した範囲の一番左にキーがある」ことが前提なので、「キーの左側」は検索できないので、どうしようか。**

について解決策を提示します。(他にもあったら教えて！)

* 解決策1: だったらキーを一番左にもってくればいいじゃないか作戦
* 解決策2: index と match を組み合わせる作戦
* 解決策3: vlookupだけで攻める作戦
* 解決策4: Query関数を使う作戦


# 前提知識

## 1) vlookup での検索ができること

![p1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/b97c7cbf-d7a4-a679-c521-5a51c7382672.png)

の表があったときにIDをキーにして、商品名を検索できることを理解していること。

```
=vlookup(100, A:C, 2, false)
```

これで `トマト` が取得できる。


## 2) 配列{}を扱えること

![p6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/8ac267ac-fe01-b95d-da2d-fb18de90a119.png)

↑黄色いところに

```
={A:C}
```

と書かれていますが、これにより `{ }` 内に書かれた範囲が展開されることを理解していること。



# 解決策1: だったらキーを一番左にもってくればいいじゃないか作戦

この作戦では、元データを加工する必要があります。

例えば下記のようにA列を追加して、A2のセルに `={C2:C}` が入っています。これによってC列をA列にコピー(正確には「参照」かな？)しています。


![p3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/47288f76-8222-674c-92a5-96252999ee55.png)

これなら

```
=vlookup("きゅうり", A:D, 2, false)
```

で `200` が取得できますね。

やってることはシンプルですが「元データを加工する」ところに抵抗があるかもしれませんね。
一度きりの使い捨てでいいから急ぎで処理したい、みたいなときはこれで十分かと思います。



# 解決策2: index と match を組み合わせる作戦

この方法の記事は `index match 検索` などで検索するといっぱい出てきます。私のこの記事に書いてあることも同じことですが、自分がわかりやすいように整理してみました。

この作戦のキモとしては **行と列が交差するところ** という探し方ができるところです。
これをやるには `index` と `match` を組み合わせます。

## indexの説明

まず `index` について動きを見てみます。

下記の表があるときに

![p4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/0544eff8-faea-5617-7d28-053745387bd2.png)

```
=index(A:C, 3, 1)
```

と書くと `200` が取得できます。この関数の意味としては

```
A:Cの範囲において、上から3行目、左から1行目の値を取得する
```

です。つまり **行数と列数がわかれば、それが交差するセルの値が取れる** ことになります。
(注意するのは「A列が1列目」ではないということ。「範囲の中の一番左」が1列目です。`B:D`が範囲なら1列目はB列)


* いまは「商品名からIDを検索したい」ので、「ほしい列はID列=A列」です。→ `index` の 第3引数の `1` は固定できます。
* 第3引数にある `3` は「商品名」の列において「きゅうり」がある列数ですが、これをどうやって特定しようか考えます。




## matchの説明

ここでもう一つの `match` 関数が登場します。`match` ではこんなことができます。

```
=match("きゅうり", B:B, 0)
```

これで `3` が取得できます。

意味は下記になります。

```
"きゅうり"という文字列を、B:Bの範囲の中から探して、何個目に存在するかを取得する
```

第3引数の`0`は「完全一致」の0ですが、詳細は [公式リファレンス](https://support.google.com/docs/answer/3093378?hl=ja)を参照ください。

## indexとmatchを合体させる

最初に

```
=index(A:C, 3, 1)
```

と書いていた式の `3` を `match("きゅうり", B:B, 0)` に置き換えることで、

```
=index(A:C, match("きゅうり", B:B, 0), 1)
```

と書けます。これで `きゅうり` のIDである `200` が取得できます。
**vlookupではできなかった「キーより左にある値を検索する」ができました！**



# 解決策3: vlookupだけで攻める作戦

...でもね。`index` とか `match` を見て「ウッ」ってなる人もいるよなーって。正直、私もそっち派です。シンプルな `vlookup` で処理したくないですか？

`vlookup` についてですが、

``` 
=vlookup(100, A:C, 2, false)
```

というのは「`100`という値を、`A:C`の範囲(の一番左の列)から探して、見つかった行にある`2`列目を取得する」です。

ポイントは **「A:Cの範囲」** です。つまり第二引数には「範囲」が指定できるんですよね。

## 「範囲」をつくる

ということは、表を変換(商品名を一番左に来るように)して、

![p7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/1306727e-78e8-6655-1a64-75411bd31bdc.png)

↑これの「下の表」を「範囲」として指定できればいいですよね。


ここで「前提知識」のところにあった「配列」を応用するとこんなことができます。

![p8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/967fd882-12ac-5665-e36e-de1186cbbb2a.png)


黄色いところに下記のように書いてあります。

```
={B:B, A:A, C:C}
```

**「範囲を `,` で区切ると横に並べてくれる」** のです。(ちなみに `;` で区切ると縦に並べてくれます)
これにより、元々の「**ABC**の順で列が並んでる範囲」を「**BAC**の順に並び替えた範囲」をつくることで、一番左に「商品名」が来るようにしました。


## 自分で作った範囲をvlookupに指定する

ポイントは **「ほしい範囲を自分で作って、その範囲をvlookupの中の範囲に設定する」** のです。

こうなります↓

```
=vlookup("きゅうり", {B:B, A:A, C:C}, 2, false)
```


これで `200` が取得できます。

...どうでしょうか。「範囲をつくる」というのがちょっとテクニカルな感じでしょうか。「メモリ上(あるいは頭の中)で範囲を作り変えて、それに対して何番目なのか」を把握しないとならないんですよね。


## どこかに範囲を書き出してもいい

メモリ上ではなく「作り変えた範囲を一度別なところに書き出して、その範囲を指定する」ことも可能です。

上の図だと `{B:B, A:A, C:C}` を `E:G` に書き出しているので、

```
=vlookup("きゅうり", E:G, 2, false)
```

のようにすると表を視認できるので理解しやすくなります。(どこかに書き出すというコスト？はかかってデータ量が多いときに重くなる可能性があるかもしれない)


# 解決策4: Query関数を使う作戦

1～3とはちょっと質が違うかもしれませんが、「ほしい情報を検索する」という意味ではこれでも解決できます。

![p9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/8612/dc291d06-be53-cf93-40ca-e3e16d0fa8f3.png)

```
=query(A:C, "select A where B ='きゅうり'", 1)
```

(Query関数、とっても強力なので、知らなかった人は是非調べてみてください！めちゃめちゃ楽ちんですよ！)


# さいごに

今回は4つの解決策を挙げましたが、どうでしょうか。

スプレッドシートでちょっと複雑なことをやろうとすると、「書いた本人ならわかるけど、将来このスプレッドシートをメンテナンスする人にもわかるようにしておきたいから、なるべく難しいことはしたくない」という気持ちと「いやいや、これくらいできるようになってよ」という葛藤が生まれます。