どうもマサヤです！
「VLOOKUP関数を使って値を検索したいけど、エラーになって検索出来ない」
もしかして、検索したい値が左側にありませんか？
VLOOKUPは便利な関数ですが、検索値の左側を検索するとエラーになってしまいます。
こういった場合の解決方法としては、検索値の右側に検索したい値を移動させるのが常套手段です。
「どうしてもセル移動ができない場合どうしたらいいの？」
その悩み！OFFSET関数とMATCH関数を組み合わせれば解決できますよ！
VLOOKUPではエラーになる検索方法が可能となります！
関数の組み合わせってちょっと難しそうに感じるかもしれませんが、2・3回触ると感覚が掴めます。
逆に触らないと一生わかりませんので、ぜひ、紹介する方法を試して覚えましょう！
実際の数式【動画】
では、具体的な方法を紹介していきます！
まずは動画で数式を確認
最初にVLOOKUPで検索してます。
ただ、検索値が左側にあるためエラーとなってます。
その後、OFFSET関数とMATCH関数を組み合わせて検索したい値を表示させています。
数式の作り方
動画で使っている数式は下記となります。
まずはMATCH関数で検索したい値の行を取得しています。
この取得した行をOFFSET関数で使い、検索したい値のセルを取得しています。
説明しましたが、それぞれの関数の機能を理解していないとわかりませんよね。
この方法を使いこなすため、関数の機能を一つずつ紹介していきます。
OFFSET関数とは？
OFFSET関数は基準セルから指定した行数と列数を移動し、その位置にあるセルもしくはセル範囲の参照を返してくれます。
◆ 定義 ◆
OFFSET(①基準, ②行数, ③列数, ④[高さ], ⑤[幅])
①基準 ： どのセルから始めるかを指定します。
②行数 ： ①基準のセルから移動したい行数を指定します。
③列数 ： ①基準のセルから移動したい列数を指定します。
④高さ ： 省略可能。移動したセルから参照するセルの高（行）さを指定します。
⑤幅 ： 省略可能。移動したセルから参照するセルの幅（列）を指定します。
今回紹介する方法では、検索したい値はセル範囲ではなく単一のセルになります。
なので、④高さ・⑤幅は使わず、①基準・②行数・③列数を使います。
OFFSET関数の詳細や他の使いこなしを知りたい場合。
MATCH関数とは？
MATCH関数は検索値を検索範囲から検索し、範囲内の相対位置を返してくれます。
◆ 定義 ◆
MATCH(①検査値, ②検査範囲, ③[照合の型])
①検索値 ： 検索したい値、セルを指定します。
②検索範囲 ： 検索するセル範囲を指定します。
③照合の型 ： 省略可能です。1・0・-1のどれかを指定します。省略した場合1となります。
なんか難しいですよね。
簡単に言うと、指定したセル範囲の上から何番目に検索値があるかを返してくれる関数です。
③照合の型も今回使うのは０（完全一致）です。これだけ覚えておけば大丈夫です。
組み合わせることで検索できる！
機能が解ったところで数式を分解して理解してみましょう。
動画を画像を見ながら説明していきます。
最初にMATCH関数を読み解く
検索値はA3です。画像を見ると枝豆ですね。
検索囲はF3:F12になっています。
この中から枝豆を探し、セル範囲の上から何個目にあるかを数えます。
数えると7個目にあるので、この数式の結果として7が返されます。
最後にOFFSET関数を読み解く
MATCH関数の結果が7だったので、MATCH関数の部分を7に変えてみましょう。
基準セルのF3です。
行数が7-1となっているのでF3から下に6行移動します。
移動するとセル位置はF9になります。
MATCH関数で結果として返されたのは7行目ではなく7個目です。
なので、そのまま下に7移動すると8個目のセルを参照することになります。
これを回避するためマイナス1しています。
その後、列数が-1になっているので、左に1列移動します。
セル位置はE9になり、E9セルの中にある値が返されます。
結果として、E9セルの中にあるF1（商品コード）が返されることになります！
まとめ
大事な点は以下の2点です。
・OFFSET関数の基準セルと、MATCH関数の検索範囲の開始セルは同じにする。
・MATCH関数の結果を必ず -１(減算) する。
VLOOKUPでエラーが出る場合に使ってみてください！
コメント