VLookupの限界
VLookup
関数にはいくつかの制約(前提条件)があります。
①マスタの左端列がキーとなる項目
になっており、かつ②その左端列でソートされている
場合に正しく機能します。
まず①の制約により、複数列のキー項目を組み合わせた検索ができません。
そして市町村コードの検索においては、C列を左端として、さらにキー重複しないよう県コードで範囲を限定する必要があります。
マスタ範囲を絞り込むための検索が必要となってしまいそうです。
ここらへんがVLookup
関数の限界になってきます。
この限界を超える方法を3つご紹介します。
①「INDEX関数&MATCH関数」
VLookup
関数は単一キーでマスタ検索する場合には便利なのでよく使われています。
しかし上記の前提条件を満たせない場合など、より高度な検索を行いたい場合には、MATCH
関数とINDEX
関数を組み合わせる方法があります。
Vlookup
のような制約もなく、より高度な検索ができるのですが若干敷居が高くなるのも事実です。
※各関数の説明は、MATCH
とINDEX
でググればあちこちで解説されていますので、ここでは割愛させていただきます。
例えば記載シートのE4セルに
=INDEX(マスタ!E$4:E$12, MATCH(B4&C4, INDEX(マスタ!B$4:B$12&C$4:C$12,),))
といった式を記述します。
これは当初aikon_marimoさんが列追加で提案されていた手法を、列追加なしで実現しているような方法になります。
MATCH
関数の検索値を記載シートのB4
+C4
の値とし、検索範囲もB4:B12
+C4:C12
としてしまっています。
初見では「そんな無茶な結合ができるのか!?」と思うでしょうが、ここではできてしまうのですね。
②「INDEX関数&SUMPRODUCT関数」
こちらも複数キー検索ではよく使われる手法です。
aikon_marimoさんのコメント欄で紹介されているページも、この手法だと思います。
難点はSUMPRODUCT
関数の挙動が直感的に掴みにくいというところでしょうか。
記述が難しいのでまず説明からすると、式は以下のような構造になります。
=INDEX([値を取得する範囲], SUMPRODUCT((条件1)*(条件2)*(…条件n)*ROW(行範囲)))
例えば提示頂いたシートの場合、
・値を取得する範囲⇒E1:E12
・条件1⇒マスタ!B4:B12が記載シートのB列の値と一致
・条件2⇒マスタ!C4:C12が記載シートのC列の値と一致
・行範囲⇒マスタ!B4:B12 ※これは検索結果から行番号を取得するためのものなので、列はあまり関係ありません。
式にすると
=INDEX(マスタ!E$1:E$12, SUMPRODUCT((マスタ!B$4:B$12 = B4)*(マスタ!C$4:C$12 = C4) * ROW(マスタ!B$4:B$12)))
こうなります。
条件1と2について、列は違いますが行範囲は同じになっています。これが1つ目のミソです。
そして検索結果を特定するために行番号を使用しているので、マスタ範囲は「マスタ!B2:E12」なのにINDEX
関数でE列の値を範囲指定する際「マスタ!E1:E12」と1行目から対象にしているところが2つ目のミソになります。
ご覧のとおり少し敷居が高いのですが、使い慣れてくると逆にわかりやすいのかもしれません。
③「VLookup関数」でがんばる
Vlookup
関数を使う以上その制約には縛られるのですが、提示いただいたシートのようにきれいにソーティングされているのであればがんばって何とかならないこともありません。
まず式から紹介すると、例えば記載シートのE4セルは以下のようになります。
=VLOOKUP(C4, INDIRECT("マスタ!C"&MATCH(B4, マスタ!B:B, 0)&":E"&MATCH(B4, マスタ!B:B, 1)), 3)
解説すると、まず
MATCH(B4, マスタ!B:B, 0)
とMATCH(B4, マスタ!B:B, 1)
で、県コードの先頭行、最終行をそれぞれみつけています。
例えば1022を検索すると、9と12が返されます。
それを踏まえて、取得した行番号を文字列に結合します。
"マスタ!C" & 9 &":E" & 12
これでできあがる文字列は"マスタ!C9:E12"です。
その文字列をINDIRECT
関数でセル範囲に変換します。
INDIRECT("マスタ!C9:E12")
そのセル範囲をマスタ(検索範囲)としてVLookup
を行い、3列目(E列)の値を取得します。
=VLOOKUP(C4, マスタ!C9:E12, 3)
以上ですが、やはりかなりゴリゴリのやり方になってしまいますね。
とてもオススメできるものではありません。
以上、参考になれば幸いです。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/23 22:39
2019/01/23 23:14
2019/01/23 23:23
2019/01/23 23:30
退会済みユーザー
2019/01/24 10:32