はじめに
条件が複数設定できるVLOOKUP関数が欲しいという声を時々耳にします。
SUMIF関数にはSUMIFS関数がありますが、VLOOKUP関数にVLOOKUPS関数はありませんものね。
セルに返したいのが数値であればもちろんSUMIFS関数を使えば良いのですが、文字列を返したいのであればそうはいきません。
ということで、今回はExcelでVLOOKUPSっぽいことができる数式を紹介したいと思います！
複数条件で照合して一致するセルと同じ行にある値を返す数式
シノさん、VLOOKUP関数って検索値を複数設定できないんですか？？
キュウさんお困りのようですね。
残念ながら複数条件でVLOOKUP関数のような
照合索引ができる関数は実装されていません。
やっぱりそうですよね。
データの作り的にはVLOOKUP関数に複数検索値が持てると一発みたいなのですが…。
SUMIFS関数なら検索に一致するデータがユニーク（重複なし）なことを前提に
数値限定で複数条件の検索ができますが、その様子だと数値じゃないんですね。
そうなんですバッチリ文字列なんです。
専用の関数はないですが、関数のネスト（入れ子）で対応できますよ。
数式について説明しますので下の図を見て下さい！
<例で使用している数式>
配列数式なので『Ctrl + Shift + Enter』で確定します。
数式バーでの見た目は先頭と末尾に『{』と『}』が付きます。
=OFFSET($D$8,MATCH(F8&G8,$B$8:$B$10&$C$8:$C$10,0)-1,0)大まかに言うと返したい値がある列の一番上のセルを基準として、
MATCH関数で複数検索に合致する行が何行目にあるのかを特定して、
基準セルからその行数ぶん下にあるセルの値を返します。
検索値と検索対象のセルを&で繋いでいますね。
そこが複数検索値で検索するポイントです。
図の一番上の式について説明すると、
検索値はセルF8とG8を結合した”ab”になります。
複数条件ですね。
ここまでは分かりました。
そして検索範囲はB8からB10と、C8からC10を&で結合しています。
こうすると検索対象は上から”ab”、”ac”、”bb”の配列にまります。
セル範囲ではなく配列なんですね…！
そうです！
それでMATCH関数は”ab”と配列の1行目が一致するため1を返します！
ここで『-1』が活きるんですね。
基準セルからの移動が『1-1』で『0』なので
基準セルのままセルD8の”食品”を返す…と。
セル範囲ではなく配列を探すというのが難しいですよね。
シートの上のどこにも”ab”は無いですもんね。
ただこの数式には欠点があって、
結合した検索値が同じになるケースに対応できません。
たとえば”a”と”bc”を&で繋ぐと”abc”になりますが、
これは”ab”と”c”を結合しても同じですよね。
そう言われてみると…そうですね。
こういうことが起こりうる場合には
間に何か記号を挟むなどして対策して下さいね。
“a-bc”と”ab-c”は違う文字列になりますもんね！
そういうことです。
ありがとうございます。
これで今やっている業務が片付きそうです！！
おわりに
正直なところ私自身はVLOOKUP関数で複数条件を設定したいと思ったことがなく、そういった場面に出くわした場合はおそらく文字列を結合した検索キー列を作って対応すると思います。
ただ、作業列を設けるのが困難な場合があることも重々承知しており、そういった場面でお役立て頂ければと思い記事にしてみました。
どうせならVLOOKUP関数を使いたかったですが、入れるところが無かったです。
もっとこうした方が良いよ！こういう時に不具合があるよ！というご意見ありましたら教えて下さい。
本当、このブログも教えてもらったことを書き留めてるみたいなところがあるので…。
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント