【Excel・エクセル】MATCH関数とOFFSET関数で、条件に合うセルを抽出する
MATCH関数とOFFSET関数で、VLOOKUP関数より便利に使えるって聞いたよ？
ちょっと難しいかもだけど、やり方を見てみよう。
- MATCH関数とOFFSET関数を組み合わせると、VLOOKUP関数より柔軟に検索できる
- =OFFSET(参照,MATCH（検査値,検査範囲,0)-1,列番号)
- MATCH関数とINDEX関数でも同じようなことはできる
MATCH関数とOFFSET関数の練習ファイルダウンロード
一緒にやってみませんか？
【Excel・エクセル】MATCH関数とOFFSET関数のおさらい
MATCH関数とは？
MATCH関数は、この値がこの範囲の中の何行目（何列目）にあるか、を調べる関数です。
=MATCH(検査値,検査範囲,照合の種類)
| 検査値 | この値は |
| 検査範囲 | この中の何番目？ |
| 照合の種類 | 0：完全一致、-1：以上、1：以下 |
たとえば「=MATCH(E2,A2:A6,0)」とすれば、セルE2の値がセルA2～A6の中の何行目にあるかが分かります。
詳しくは、こっちのページで解説しているよ。
https://sakusaku-office.com/excel/post-9009/OFFSET関数とは？
OFFSET関数は2通りの使い方がありますが、今回は指定した位置の値を抽出する使い方をします。
=OFFSET(参照,行数,列数)
| 参照 | このセルから |
| 行数 | 縦方向にこのセル分（+なら下、-なら上） |
| 列数 | 横方向にこのセル分（+なら右、-なら左）の値を抽出して |
注意したいのが行数と列数。
- 参照と同じ位置は0
- 行数：正の数は数値分下、負の数は数値分上
- 列数：正の数は数値分右、負の数は数値分左
という感じで指定します。
例えばこの表で「OFFSET(B4,2,0)」と書いた場合。
B4から2つ下にある、B6の値を抽出します。
VLOOKUP関数とは？
VLOOKUP関数は、条件に合うセルを抽出する関数です。
=VLOOKUP(検索値,範囲,列番号,検索方法)
| 検索値 | この値を基準にして |
| 範囲 | この範囲の中の |
| 列番号 | 左から何番目にある値を抽出して |
| 検索方法 | 基本は0（FALSE）でOK |
VLOOKUP関数は便利ですが、範囲の一番左端に検索値の列がないと抽出できません。
また、
それを無視して値を抽出できるのが、MATCH関数とOFFSET関数の組み合わせなんよ。
【Excel・エクセル】MATCH関数とOFFSET関数の組み合わせ方
2つを組み合わせると、こんな書き方になります。
=OFFSET(参照,MATCH（検査値,検査範囲,0)-1,列番号)
OFFSET関数の行番号を、MATCH関数で表すんよ。
あの「-1」がポイントな気がする……！
この表、検索値が表の内側にあり、かつ抽出したい値は検索値の列の左側にあります。
これじゃ、VLOOKUP関数が使えませんね。
これは表を作り直したがいいレベル……。
MATCH関数とOFFSET関数を組み合わせて、この状態でも検索できるようにしてみましょう。
=OFFSET(C1,MATCH(G2,C1:C6,0)-1,-1)で、求められるよ。説明するね。
ステップ1
まずはOFFSET関数から書いていきます。
セルG3に、「=OFFSET(」と入力してください。
ステップ2
OFFSET関数の参照は、検索したい値が書かれた列の一番上を選びます。
今回は型番から分類を検索したので、型番が書かれたC列の一番上、セルC1をクリックします。
「,（カンマ）」も忘れずにね。
ステップ3
次に行番号ですが、ここでMATCH関数の出番です。
「MATCH(」と入力して、MATCH関数を呼び出しましょう。
ステップ4
検査値には検索したい値（セル）を指定します。
今回はセルG3ですね。
そして「,（カンマ）」。
ステップ5
次の検査範囲は、検査値がある列にします。OFFSET関数の参照の列ですね。
今回はセルC1～C6までをドラッグします。
そして「,（カンマ）」。
ステップ6
次の照合の種類ですが、これは深く考えず、「0」でOKです。
これでMATCH関数部分は終わったので、カッコを閉じます。
ステップ7
はい、ここでポイント！「-1」します！
なぜかというと、MATCH関数は何行目かを教えてくれる関数なので、1行目は1になりますが、OFFSET関数は参照したC1（1行目）を0で表すためです！
ほえ？
0から数え始めるか、1から数え始めるかの違いだね。
そう、そしてそのズレを修正するために、「-1」をするのです。
難しく考えず、とりあえず―1するって覚えといてもいいと思うよ！
次の引数に移るので、「,（カンマ）」もお忘れなく。
ステップ8
最後の列番号は、最初の参照で選んだ列（今回はC列）から何列目の値を抽出するかです。
OFFSET関数はVLOOKUP関数と違って、基準の右側は正の数、左側は負の数で表すことができます。
今回抽出したい分類はC列の1つ右側にあるので、「-1」とすれば良さそうですね。
完成！
これで式は完成なので、カッコを閉じて、エンターキーを押してください。
無事、型番から分類を抽出できました！
参照のセルや範囲は、必要に応じて絶対参照にしてね。
これならVLOOKUP関数より柔軟に、必要な値が抽出できそうですね！
でもこれ、MATCH関数とINDEX関数の組み合わせでもよくない？
ぶっちゃけどっちでも同じようなことはできます。
「-1」ってややこしい部分もあるから、INDEXの方が分かりやすいまである。
まぁそれは人それぞれなので、やりやすい方で試してみてくださいね！
これで、VLOOKUP関数ではうまく抽出できない表でも、対応できますね！
それでは、お疲れさまでした！
- MATCH関数とOFFSET関数を組み合わせると、VLOOKUP関数より柔軟に検索できる
- =OFFSET(参照,MATCH（検査値,検査範囲,0)-1,列番号)
- MATCH関数とINDEX関数でも同じようなことはできる
