【Excel】vlookupの逆向き値取得

今回は今までと少し毛色が異なり、EXCELのちょいテクをお伝えしたいと思います。
以前に先輩に今回のやり方を教えていただいて、感動をしましたので、その思いも乗せてお伝えします。

vlookupの逆向き

Excelの魅力のひとつはvlookup関数ではないでしょうか。
なんちゃってDBのような働きをみせてくれますので、事務処理含め業務でも重用している方も多いと思います。

しかし、そんなvlookupにも弱点があります。
それは検索のキーになる項目より前の項目が取得できないことです。

<商品マスタ>参照される側

商品番号	商品名
001	みかん
002	りんご

<売上>参照する側

No	商品番号	商品名
1	001
2	002

上記のようなデータがあった場合、売上の商品名にVLOOKUPを利用することで、
商品マスタから商品名が取得できます。

=VLOOKUP(商品番号,商品マスタ!商品番号:商品名,2,FALSE)

上記のような式を売上の商品名のセルに入れるかと思います。

VLOOKUPで対応できないこと

先程の例を使ってご説明します。

<商品マスタ>

分類	商品番号	商品名
食品	001	みかん
食品	002	りんご

<売上>

No	商品番号	分類
1	001
2	002

上記は売上のレコードにある分類は商品マスタの1列目にある分類を取得しようとするものです。

VLOOKUP(商品番号,商品マスタ!分類:商品名,-1,FALSE)

のようにやりたくなりますが、第3パラメータのデータ取得元はマイナスで前に戻るような動きにはなりません。

解決策１

力業です。

先ほどの例だと商品マスタの分類列をコピーや切り取りをして、商品名の後ろに持っていきます。

後は通常通り、vlookします。

解決策２

こちらが今回の本命です。

解決策１では列データをいじらなければいけないので、スマートではないですし、そもそも列を動かしてはいけないケースもあると思います。

そんなとき下記のような関数を利用するこど、前述の例は対応可能です。

match関数

検索したいどこにあるかを返してくれる関数となります。

パラメータは下記となります。

１．検索値：検索をする値があるセル位置

２．検索範囲：どこからどこまの値を検索しにいくかの範囲指定

３．照合するやり方：一致、以上、以下などの指定できますが、今回は一致の0のみ利用

index関数

指定された行・列の値を返してくれる関数となります。

パラメータは下記となります。

１．対象範囲：参照される側の検索列と取得したい列を含んだ範囲を指定

２．行番号：値を取得して返す行番号

３．列番号：値を取得して返す行番号

matchとindexの組み合わせ

先程のmatch関数とindex関数を繋ぎ合わせるとvlookupのような機能が実現可能となります。

<商品マスタ>

分類	商品番号	商品名
食品	001	みかん
食品	002	りんご

<売上>

No	商品番号	分類
1	001
2	002

index([商品マスタ全範囲],match([売上]の[商品番号],[商品マスタ]の[商品番号],0),1)

実際の式を想定すると下記となります。（<売上>のNo1分類に入る想定）

---

=index([商品マスタ]!$A$2:$C$3,match([売上]!$B2,[商品マスタ]!$B$2:$B$3,0),1)

---

この式は汎用的に利用できるので、参照を行う際はぜひ思い出してご利用いただければと思います。