先日OFFSET関数で指定のセルから指定の行数・列数をスライドした先のセルの値を参照する方法について解説しました。
OFFSET関数で指定のセルから指定の行数・列数をスライドした先のセルの値を参照する方法 | Excelを制する者は人生を制す ～No Excel No Life～
今回はOFFSET関数の応用テクニックとして、OFFSET関数とMATCH関数を組み合わせてVLOOKUP関数より柔軟に表から検索値を返す方法について解説していきます。
使用例
今回はF3セルの商品コードからB2:D5の表から該当の商品コードに合致した商品名をG3セルへ返したいとします。
その場合、通常VLOOKUP関数を使うところですが、今回の表の場合、商品コードがC列、商品名がB列とマイナス方向のためVLOOKUP関数を使えませんのでOFFSET関数とMATCH関数の出番です。
VLOOKUP関数については下記記事をご参照ください。
VLOOKUP関数の可変性を持たせる組み合わせまとめ | Excelを制する者は人生を制す ～No Excel No Life～
まず、G3セルにOFFSET関数を入力していきます。
OFFSET関数の[参照]は表の中の検索する列の開始値となるC3セルを指定します。
今回はG3セルだけに返すので相対参照でも良いですが、複数検索をかける場合、ここは絶対参照にしておくと良いです。
続いて、OFFSET関数の[行数]の部分にMATCH関数を使用して可変する値にします。
MATCH関数の[検査値]は検索をかける値を指定するF3セルを指定、[検査範囲]は表中の全商品コードを選択できるようC3:C5を絶対参照で指定、[照合の種類]は絶対値を検索したいので「0」を指定します。
ここで重要なポイントとして、OFFSET関数は[参照]で選択したセルを「0」を起算としますが、MATCH関数は[検査範囲]の起点を「1」から起算するため、調整のためMATCH関数のあとに「-1」を入れて調整します。（下図はイメージ）
MATCH関数のあとに「-1」で調整する方法以外に、OFFSET関数の[参照]の指定セルを予めひとつずらして指定しておく方法（今回のケースであればC2セルにしておく）でも結果はいっしょとなります。お好みで使い分けてください。
最後に、OFFSET関数の[列数]はB列が返り値の候補としたいため、「-1」を指定します。
ここまでで、F3セルで指定した商品コードに該当する商品名がG3セルに返ります。
まとめ
今回はVLOOKUP関数で行いましたが、HLOOKUP関数でも同様のことが可能です。（列数部分にMATCH関数を使用）
Excel関数に慣れている方向けのテクニックのため、初心者の方はもしマイナス方向に検索をかけたい場合は、検索値の作業列を無理やりつくった上でVLOOKUP・HLOOKUP関数を実施した方が手っ取り早いです。（表が若干見にくくなる恐れはありますが）
次回は、OFFSET関数の応用テクニックの第二弾として、SUM関数やAVERAGE関数の計算範囲を可変にする方法について解説していく予定です(・∀・)
初めまして。今、家計簿をエクセルで行っております。カテゴリを２つに分けてるのですが、関数を使った場合、上手い事いかないので困っています。科目に応じてカテゴリを分けたいのですが、次の科目になってしまいその行の科目にならないのですが、どうすればいいですか？説明が下手で申し訳ございませんが、よろしくお願いいたします。
なるなるさん
コメント返信が大変遅くなり申し訳ございません。
希望のカテゴリが1行分ずれることは分かりましたが、詳細を確認したいので、
プロフィールページのコンタクトフォームで別途お問い合わせいただけますでしょうか？
宜しくお願い致します。