Googleスプレッドシートで、部分一致の逆の検索（検索値が検索範囲の値を「含んでいる」かどうか）をして値を表引きする方法についてです。「VLOOKUP関数で検索範囲側にワイルドカードをつけて検索するには」といった形で問題提起されることもあります。ただしVLOOKUP関数で対応するのは面倒なので、ここでは別の方法を紹介します。
- FILTER関数とFIND関数を使って部分一致検索の逆引き（表引き）をすることができます。
手順
次のような注文票と価格表があります（B,C列は手入力で、E列には「=C3*D3」といった掛け算が入力されています）。
注文票の商品名に基づき、価格表の商品名を検索してその価格をD列に転記したいのですが、注文票の商品名の方が（価格表の商品名を含む）長い表記になっているためVLOOKUP関数のワイルドカード検索では対応できません。
そこでD3セルに次のように入力します。
D3セル
=FILTER(H3:H6,FIND(G3:G6,B3))
FILTER関数を使い、価格表の商品名（4種）が注文票の表品名に含まれるかをそれぞれ判定し、含まれるものに対応する価格を返します。
判定条件にFIND関数を使っていることがポイントで、価格表の商品名が注文票の表品名に含まれる場合は1以上の数値（TRUEとみなされる）が、含まれない場合はエラーが返ります。判定にマッチするものは通常1つですので「FIND(G3:G6,B3)」は1つの数値と3つのエラーを返すのですが、FILTER関数はエラーをものともせず対応する値を返してくれます（Excelだとこのままではうまくいかずちょっとした対応が必要です）。
確定すると確かに「王林」の価格である「220」が表示されました。
下方のセルへのコピーについてですが、この式についてはARRAYFORMULA関数で展開しようとしてもうまくいかないので絶対参照を用いることとなります。画像ではD3セルの式を次のようにして下方にコピーしています。
D3セル
=FILTER(H$3:H$6,FIND(G$3:G$6,B3))
ちなみにマッチするものが複数ある場合に最初のものだけ返すようにしたい場合（この例では複数にマッチすることを防ぐべきですが…）は、上記の式は次のようになります。
=INDEX(FILTER(H$3:H$6,FIND(G$3:G$6,B3)),1)
