はじめに
みなさんはExcelのVLOOKUP関数を利用していて不便を感じた事はございませんか？
この記事ではVLOOKUP関数の引数を工夫して、データが増減しても機能するVLOOKUP関数を紹介します。
※既にVLOOKUP関数の使い方をご存じの方向けの記事となります。
作成したVLOOKUP関数
=VLOOKUP(検索値,OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)),MATCH("表示させたい列の見出し",$1:$1,0),FALSE)
使用するエクセルデータサンプル
今回は以下のように1行目に見出しがあり、1列目に検索したい値のあるデータでの利用を紹介します。
解説
VLOOKUP関数の引数について
まずはVLOOKUP関数の引数についてみてみましょう。
VLOOKUP(検索値,検索範囲,列数,[検索方法])
今回作成したVLOOKUP関数では「検索範囲」と「列数」に「OFFSET関数」、「MATCH関数」を用いてデータの増減に対応できるようにしています。
検索範囲について
検索範囲には以下のようにOFFSET関数を用いています。
OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
OFFSET関数の引数は以下となります。
OFFSET(参照基準, 行数, 列数, [高さ], [幅])
参照基準にはVLOOKUP関数で利用する範囲の起点(左上のセル)を指定して、行数、列数は0を指定します。
高さ、幅の値にはCOUNTA関数を用いて、それぞれA:A、1:1と指定することで高さ(値の入っているセルの最大行数)、幅(値の入っているセルの最大列数)が取得できるのでデータが増減しても範囲が狂う事が無くなるようになります。
列数について
列数には以下のようにMATCH関数を用いています。
MATCH("表示させたい列の見出し",$1:$1,0)
※「表示させたい列の見出し」の文言は使用するデータに合わせて変更してみてください
列数にMATCH関数を用いて見出しを検索させる事で視覚的にも表示させたい列が分かりやすくなり、列数が変更された際でも対応した見出しの列数が指定されるため結果が狂う事が無くなるようになります。
使用してみた
H列2行目に検索したい値を入力し、I列2行目に関数を入力しました。
尚、出力する値の列(見出し)は「単価」の列としています。
データを追加して正しく出力されるか確認してみた
関数の内容は変更していないが、追加したデータに対する結果が正しく出力されました。
おわりに
いかがだったでしょうか。
今回紹介したVLOOKUP関数をテンプレートとして使用して、少しでもみなさんのお役に立てましたら幸いです。
この記事が良かったと感じたらいいねをお願いします。