VLOOKUP関数は非常に便利な関数ですが、弱点もあります。それは、”検索した列の、左側にある列のデータ”を表示できないこと。
一つの解決策として、「OFFSET関数＆MATCH関数」の組み合わせを使う方法があります。
VLOOKUP関数の弱点
VLOOKUP関数は、データが入力されたリストの中から、
（証券コード、社名、決算期、取引所、事業内容、のリスト）
探したいデータを指定すると、
（ここでは「社名（ブスケツ不動産）」を探したいデータにします。）
指定した範囲の、一番左の列を、縦に検索して、
探したいデータ（ブスケツ不動産）が見つかったら、
指定した数だけ、そのセルから右に進んだセルを、表示します。
（”３”と指定して、右に3つ進んだセルを表示させる）
すると、
表示させたいデータ（ブスケツ不動産の取引所）を表示させることができます。
膨大なデータを含む資料の中から、必要なデータを一瞬で表示させることができるので、財務データだけでなく、在庫データ、人事データなどをExcelで処理する場合にも重宝します。
ただ、VLOOKUP関数にも弱点があります。
それは、「探したいデータ」の左側の列に、表示させたいデータがある場合です。
ここまでの、VLOOKUP関数の動きを見ていただいて、何となくお分かりいただけたかと思いますが、
VLOOKUP関数は、範囲を指定すると、指定範囲の一番左の列が自動的に、検索する列になってしまいます。
ですので、VLOOKUP関数で表示させるセルは、必ず、検索する列の右側に来ることになり、
検索列の左側を表示させることはできません。
このVLOOKUP関数の弱点を克服する方法の一つとして、「”OFFSET関数＆MATCH関数”の組み合わせ」があります。
「”OFFSET関数＆MATCH関数”の組み合わせ」で、左側の列も表示できます
OFFSET関数
まず、OFFSET関数の特長をおさえます。
OFFSET関数の”動き”が理解できると、「左側の列」を表示できる理由が何となく分かります。
OFFSET関数は、「基準となるセル」を指定して、
（青で色づけしたB1セルを「基準となるセル」に指定）
「基準となるセル」から移動する距離を、セルの数で指定します。
順番は、”行”→”列”の順です。
縦にセル3つ分、横にセル1つ分移動させるとすると、
=OFFSET(B1, 3 , -1)
になります。
列に”-1”と、マイナスが着いているのは、
右に移動するときはプラス、
左に移動するときはマイナス、
の指示が必要だからです。
すると、
「基準となるセル＝B1セル」から、3行と-1列進んだ「A4」セルのデータを、表示することができました。
これが、OFFSET関数の動きです。
まとめると、
１．基準となるセルを指定
２．「基準となるセル」から移動する距離を、「行→列」の順に指定
３．２．に従って移動したセルのデータを表示
こうなります。
これを踏まえると、
「探したいデータ」が何行目にあるか
が分かれば、あとは、表示したいデータのセルが、
・右の列ならプラス
・左の列ならマイナス
で指定すれば、自在に表示したいデータを指定できることになります。
ということで、次は、
「探したいデータ」が何行目にあるか
を考えます。
MATCH関数がその答えです。
MATCH関数
MATCH関数の動きを見てみましょう。
MATCH関数は、使い勝手の良い便利な関数です。
探したいデータを指定して、
（ここでは「マキシマム ザ ホルモン」を指定）
さらに、検索範囲を指定すると、
（A1セルからA8セルまでを指定）
探したいデータが、指定した範囲の先頭のセルから、何番目にあるかを表示してくれます。
（探したいデータ（＝マキシマムザホルモン）は、A5セルにあり、検索範囲の先頭セル（A1セル）から数えて5番目）
これがMATCH関数の動きです。
まとめると、
１．探したいデータを指定
２．検索範囲を指定
３．検索範囲の先頭のセルから何番目に、探したいデータがあるかを表示
こうなります。
このMATCH関数の動きを踏まえると、OFFSET関数の”行”の移動距離の指定のところ、つまり、
「探したいデータ」が何行目にあるか
に組み込むことができるので、両方を組み合わせることで、
「検索列の左側にあるデータ」を表示することが可能になります。
「OFFSET関数＆MATCH関数」を組み合わせる
元にリストに戻って、社名から証券コードを表示させることを考えます。
証券コードは、社名の左側の列にあるので、VLOOKUP関数では表示させることができないデータでした。
OFFSET関数からスタートです。
「基準となるセル」を指定します。
「社名」の列の先頭行のセルを「基準となるセル」にします。
次に、「基準となるセル」から移動する距離です。
先に”行”の移動距離を入力しますが、ここで、MATCH関数です。
「探したいデータ」がリストの先頭行のセルから、何番目にあるかを表示させるようにします。
探したいデータは、C12セルに指定。
C12セルには「ブスケツ不動産」と入力されています。
検索範囲は、社名が入力されているC14セルからC20セルまで。
先頭行のセルはC14セルなので、
C14セルから数えて何番目のセルに「ブスケツ不動産」が入力されているか
を表示してくれます。
なお、MATCH関数で表示される番号は、検索範囲の先頭のセルを「１」と数えるのに対して、
OFFSET関数の移動距離の指定は、「基準となるセル」を「0」と数えるので、OFFSET関数の指定方法に合わせて、
＝OFFSET(C14,MATCH(C12,$C$14:$C$20,0) -1
「-1」を付け足します
最後に、「基準となるセル」から移動する距離の”列”です。
表示したいデータは「証券コード」ですが、「証券コード」の列は、「基準となるセル」のC列より、1列左です。
「左側の列へ移動」を指示するためには、マイナスをつければいいので、「-1」と入力
すると、
VLOOKUP関数では不可能だった、「探したいデータ」の左側にある列のデータを表示することができました。
まとめ
VLOOKUP関数の弱点は、「探したいデータ」の左側の列に、表示させたいデータがある場合に、それを表示させられないことです。
この弱点は「”OFFSET関数＆MATCH関数”の組み合わせ」で克服することができます。
なお、この方法以外にも「”INDEX関数＆MATCH関数”の組み合わせ」で可能ですので、そちらも参考にしてみて下さい。
おまけ
ホルモン見たくなってきました。
首、鍛えよう。