OFFSET関数とMATCH関数を組み合わせて、検索値より左にあるデータを取得する方法を紹介します。
検索値より左のデータを取得する
VLOOKUP関数は=VLOOKUP(検索値,検索範囲,検索範囲の左端から何列目か,検索の型)で、必ず検索値が検索範囲の一番左に位置していなくてはいけません。
上記のように商品Noをキーにコースや単価を取得したい場合、コースはVLOOKUP関数で取得できますが、A表の単価は商品コードの左にあるためVLOOKUP関数では取得できません。このような場合はOFFSET関数とMATCH関数を組み合わせることで解決できます。
OFFSET関数とは
OFFSET関数とは基準のセルから指定し列数と行数分をずらした先のセルを参照する関数です。
＝OFFSET(基準セル,ずらす行数,ずらす列数）
*行数は正の数値を入れると下方向、負（-）の数値を入れると上方向を意味します
*列数は正の数値を入れると右方向、負（-）の数値を入れると左方向を意味します
これを使ってクイズの単価を求めると
=OFFSET(B8,MATCH(E9,B:B,0)-8,-1)
B8セルを基準に、E9（商品No3）がB列で何番目にくるかを算出しB8セルより上（8）をマイナスして行番号を指定、単価はB8セルより一つ左にありますのでｰ1を指定します。
正しい単価を取得できました。
POINT
OFFSET関数とMATCH関数を組み合わせることで、基準となるデータより左にある項目のデータを取得することが可能です。但し、式が少々複雑になります。マスタを作成する際にはキーになりそうな項目を一番左にしておくと、VLOOKUPだけで対応が可能なため、データの作り方も考慮しましょう。
コメント