値を検索するならエクセルVLOOKUP関数、というユーザは多いですが、このVLOOKUP関数にも弱点があります。このような弱点を、エクセルMATCH関数で克服できます。ここでは、VLOOKUP関数の列番号にMATCH関数を使うと、VLOOKUP関数ででるエラーが出ません。こんな方法もあったかと、実感できるでしょう。スキルアップできます！
例えば、VLOOKUP関数では、検索する範囲の一番左側を検索して、値を抽出します。なので、検索列を値を返す列の右側にすると、エラーになってしまいます。VLOOKUP関数で数式を作成した後、検索範囲内の列を削除すると、その場合もエラーがでてしまいます。ですが、VLOOKUP関数とMATCH関数では、エラーになりません！VLOOKUPとMATCHを組み合わせると、列番号を自由に指定できます。VLOOKUP関数で、可変する範囲に対応できるようになります。
目次
VOOKUP関数でエラーになる場合
B列には、VLOOKUPの式が入力されています。
VOOKUP関数では、設定した範囲の中の行を削除すると、エラーになってしまいます。
そこで、F列「注文日」を削除してみましょう。
B列を、見てみましょう。
すると、VLOOKUPの式は、全てエラー値「#REF!」になっています！
「D2:I9」の範囲だと、行番号は「５」までです。行番号「６」はありません。これではエラーになるはずです。
VLOOKUPとMATCHを組み合わせて検索
次は、行番号にMATCH関数を使って、式を作成してみましょう。
B2に、式を入力しましょう。
B2 =VLOOKUP(A2,$D$2:$I$9,MATCH($B$1,$D$1:$I$1,0),FALSE)
B2の式は、「MATCH($B$1,$D$1:$I$1,0)」で行番号を取得しています。
B2の式ドラッグして、下にコピーしましょう。
ここで、F列「注文日」を、削除してみます。
B2の式を、見てみましょう。
B2 =VLOOKUP(A2,$D$2:$I$9,MATCH($B$1,$D$1:$I$1,0),FALSE)
当然ですが、式はそのままで、エラーもでていません。
VOOKUP関数の列番号にMATCH関数を使うと、VOOKUP関数でエラーがでる場合もエラーになりません！
VLOOKUP関数とは(基本的な使い方)
まず、VLOOKUP関数で値を検索する方法を紹介しましょう。基本的な使い方です。
B列の「金額」に、右表の「金額」をVLOOKUP関数で表示させましょう。
B2に「注文№」を検索して、「D2:I9」の範囲から「金額」を抽出する式を入力します。
B2 =VLOOKUP(A2,$D$2:$I$9,6,FALSE)
「13,000」が返されます。
B2の式は、検索値は保証番号です。範囲「D2:I9」の６番目の列「金額」返しています。
B2の式ドラッグして、下にコピーしましょう。
B列に、「金額」が表示されました！
次に、この表を使って、VLOOKUPの弱点と、VLOOKUPとMATCHを組み合わせて、検索する方法を説明していきます。
MATCH関数とは(基本的な使い方)
MATCH関数とはどんな関数なのか、基本的な使い方を復習しましょう。
１月～５月までの科目の担当者を、表にしています。
３月の「加藤 洋子」の、担当している科目を調べます。
C8に、式を入力しましょう。
C8 =MATCH(“加藤 洋子”,D2:D5,0)
「３」が返されました！
C8の式は、照合の型は「0」です。検査値と一致する、一番最初の値を返します。
「加藤 洋子」は、範囲「D2:D5」の上から３番目です。
数学の担当、だということが分かります。
VLOOKUP関数
エクセルVLOOKUP関数は、指定した範囲の左端の列で検索値を探し、同じ行にある、指定した列番号の値を返します。
VLOOKUP関数の書式と引数
=VLOOKUP(検索値, 範囲, 列番号,[ 検索の型])
| 検索値 | 検策する値を指定します。 |
| 範囲 | 検索する範囲を指定します。その場合、検索する列を範囲の一番左にします。検索の型にTRUEを指定、または省略した場合は、正確な値を返すために範囲の左端の列を昇順で並び替えておく必要があります。 |
| 列番号 | 範囲の先頭列から数えた、列番号を指定します。指定した列番号の列の値が返されます。 |
| 検索の型 | FALSEまたは”０”を指定すると、一致する値のみ返されます。一致する値が見つからない場合は、エラー値#N/A が返されます。TLUEまたは”１”を指定すると完全一致の値、または近似値が返されます。省略した場合もTRUEになります。完全一致の値が見つからないと、検索値未満の最大値が返されます。 |
MATCH関数
指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。
MATCH関数の書式と引数
MATCH(検査値, 検査範囲, [照合の型])
| 検査値 | 検索する値を指定します。検査値には、数値、文字列、または論理値、またはこれらの値に対するセル参照を指定できます。 |
| 検査範囲 | 検索するセルの範囲を指定します。 |
| 照合の型 | -1、0、1の数値のいずれかを指定します。省略した場合は自動的に１が選択されます。 |
照合の型
「１」：検査値以下の最大の値を返します。この場合、検査範囲を昇順の並べ替える必要があります。
「０」：検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。
「-1」：検査値以上の最小の値が検索されます。この場合、検査範囲を降順に並べ替える必要があります。
エクセルINDEX関数とMATCH関数を使って検索、抽出
ここでは、エクセルVLOOKUP関数とMATCH関数を組み合わせて、値を検索する方法を紹介しましたが、エクセルINDEX関数とMATCH関数を使って検索、抽出することもできます。INDEX関数とMATCH関数との組み合わせると、VLOOKUP関数とMATCH関数の組み合わせと同じように、VLOOKUP関数でエラーがでる場合でも、エラーになりません。この使い方も、ぜひ覚えましょう。仕事アップできます。
こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って検索、抽出する方法
