MATCH関数の使い方(相対位置を調べる):Excel関数

スポンサードリンク

2021/06/26

MATCH関数	VLOOKUP とMATCH の複合
VLOOKUP と MATCH およびINDIRECTの複合	VLOOKUP とMATCH の複合

範囲内で値を検索し、見つかった値が何番目に位置するかを数値で返す。　　　topへ
　マッチ
=MATCH(検索値,範囲 [,照合の型])
　　　照合の型：「0」完全に一致する値を検索する
　　　　　　　　「1」一致するものがない場合、検索値以下の最大の値を検索する。（データは昇順に並んでいることが必要）
　　　　　　　　「-1」一致するものがない場合、検索値よりも大きい最小の値を検索する。（データは降順に並んでいることが必要）

MATCH関数は単独で使われることは少なく、INDEX関数やVLOOKUP関数、HLOOKUP関数などの行列を検索する関数と組み合わせて使われることが多くなります。
このページでは VLOOKUP関数との組み合わせを中心に説明しています。
INDEX関数との組み合わせる使い方は　INDEX関数の使い方　をご覧ください。
Excel for Microsoft 365 (サブスクリプション版) でXMATCH関数が使えるようになりました。
XMATCH関数の使い方　をご覧ください。

[A]　MATCH関数で完全に一致する値を検索する。[照合の型 0]

【問題】到着地の名古屋(C9セル)は運賃表の左から何番目ですか?　E9セルに数式を入れなさい。
出発地の熊本(C10セル)は運賃表の上から何番目ですか?　E10セルに数式を入れなさい。

	B	C	D	E	F
2	運賃表
3		東京	名古屋	大阪
4	鹿児島	50,000	40,000	35,000
5	宮崎	45,000	35,000	30,000
6	熊本	40,000	30,000	25,000
7	福岡	30,000	20,000	15,000
8
9	到着地	名古屋	は左から	2	番目です。
10	出発地	熊本	は上から	3	番目です。
11

【解答例】到着地（C9セル)は範囲（C3:E3）からMATCH関数の完全一致で求めます。
MATCH関数の照合の型は「0」を使います。
E9セルの数式は　=MATCH(C9,C3:E3,0)　としました。

出発地（C10セル)は範囲（B4:B7)からMATCH関数の完全一致で求めます。
C10セルの数式は　=MATCH(C10,B4:B7,0)　としました。

(別解)　VLOOKUP関数やINDEX関数とMATCH関数を組み合わせて求める方法

VLOOKUP関数やINDEX関数と組み合わせて運賃を求めることができます。(下の方に説明があります)
(注)VLOOKUP関数、INDEX関数のどちらを使っても同じ結果が得られますが、VLOOKUP関数とINDEX関数とで中に使うMATCH関数の範囲が異なります。
=VLOOKUP(C10,B4:E7,MATCH(C9,B3:E3,0),FALSE)
=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3：E3,0)）
INDEX関数とMATCH関数を組み合わせて使う方法は　INDEX関数　をご覧ください。

MATCH関数で検索値以下の最大値を検索する。[照合の型 1]

検査値以下の最大の値を検索するときには、MATCH関数の照合の型に「1」を使います。
この時は照合の型を省略することができます。
照合の型 1 が既定値です。照合の型を省略すると 1 とみなされます。
金額の表から割引率を求める例です。
金額の ○○以上を検索するために、MATCH関数の照合の型を1とします。ここでは省略しています。
G3セルは　=INDEX(D3:D7,MATCH(F3,B3:B7))　で求めています。
検索範囲（B3:E3)の20～80の中で検索値（B8)の45以下で、最も近い値は（C3)の40 となります。
C3セルの40は検索範囲（B3:E3)の左から2番目にあるので、答えは2となります。

MATCH関数で検索値よりも大きい最小の値を検索する。[照合の型 -1]

MATCH関数の照合の型に「-1」を使います。
検索範囲（B3:E3)の20～80の中で検索値（B8)の85以上で、最も近い値は（B3)の100 となります。
B3セルの100は検索範囲（B3:E3)の左から1番目にあるので、答えは1となります。

VLOOKUP とMATCH の複合　　　topへ
　　　縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。

【問題】VLOOKUP関数とMATCH関数を使ってC11セルに運賃を求めなさい。

	B	C	D	E
2	運賃表
3		東京	名古屋	大阪
4	鹿児島	50,000	40,000	35,000
5	宮崎	45,000	35,000	30,000
6	熊本	40,000	30,000	25,000
7	福岡	30,000	20,000	15,000
8
9	到着地	名古屋
10	出発地	熊本
11	運賃は	30,000

【解答例】出発地熊本（C10セル)の行位置をVLOOKUP関数で求めます。
VLOOKUP関数は左端の列を検索するので、範囲は『B7:E7』となります。
=VLOOKUP(C10,B4:E7,○○,FALSE)
到着地の名古屋（C9セル)が何列目になるのかMATCH関数で求めます。
(注)VLOOKUP関数は検索列(一番左の列）を含む範囲になるので、MATCH関数の範囲は『B3:E3』とします。
MATCH(C9,B3:E3,0）　
この両方の式を合わせて完成です。
=VLOOKUP(C10,B4:E7,MATCH(C9,B3:E3,0),FALSE)

VLOOKUP と MATCH および INDIRECT の複合　　　　topへ
　参照する表が２種類ある場合、切り替えて使いたい時にINDIRECT関数が使えます。
　インダイレクト
=INDIRECT(参照文字列,参照形式)
　　　　　参照文字列で入力したセル、セル範囲名を計算式で参照できるようにする。
　　　　　参照形式：TRUE　または省略した場合、A1形式でのセル参照。
　　　　　　　　　　　FALSE　R1C1形式でのセル参照。

【料金計算の例】

料金表が【大人】と【子供】の２種類あります。計算時にこれらの表を切り替えて使う方法です。
C2セルに出発地、C3セルに到着地、B4セルで「大人」「子供」を選択して、C4セルに人数を入力します。
C5セルでその金額を求めます。

	B	C	D	E	F	G	H
2	出発地	宮崎		大人
3	到着地	名古屋			東京	名古屋	大阪
4	子供	2		鹿児島	50,000	40,000	30,000
5	料金	35,000		宮崎	45,000	35,000	25,000
6				熊本	40,000	30,000	20,000
7				福岡	35,000	25,000	15,000
8
9				子供
10					東京	名古屋	大阪
11				鹿児島	25,000	20,000	15,000
12				宮崎	22,500	17,500	12,500
13				熊本	20,000	15,000	10,000
14				福岡	17,500	12,500	75,000

数式が読みやすくなるように、セル範囲に名前を設定します。
- 大人の料金表のE4:H7の範囲に【大人】と名前をつけます。
  セル範囲 E4:H17 を選択して、名前ボックスに大人　と入力します。
- 大人の料金表の到着地行E3:H3に【大人到着地】と名前をつけます。
- 子供の料金表のE11:H14の範囲に【子供】と名前をつけます。
- 子供の料金表のタイトル行E10:H10に【子供到着地】と名前をつけます。
C5に数式　=VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4　を、入力します。

【説明】数式　=VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4　の説明

INDIRECT(B4&"到着地")：B4セルの文字列が『子供』であれば『子供到着地』を参照します。
=VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4
MATCH(C3,INDIRECT(B4&"到着地"),0)：でC3セルの到着地『名古屋』の列位置を返します。
=VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4
VLOOKUP(C2,INDIRECT(B4)・・・：B4セルの文字列『子供』ならば、セル範囲『子供』からC2セルの出発地『宮崎』を検索し、MATCH関数で求めた列位置の一人分の料金を出します。そして、人数を掛けて料金を計算しています。
=VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4

(ステップアップ)入力規則を使って入力値を制限する

入力ミスを防ぐ＆マウス操作のみにするため、C2,C3,B4セルには入力規則を設定します。
操作手順の詳細は　ドロップダウンリストを設定する(入力規則)　をご覧ください。
1. C2セルを選択して、[データ]タブの[データの入力規則]をクリックします。
2. 入力値の種類で「リスト」を選択します。
  元の値でセル範囲　E4:E7 を指定します。
  [OK]ボタンをクリックします。
3. C2セルを選択すると、ドロップダウンリストから出発地を選択できるようになりました。
同様に、C3には【リスト】で【F3:H3】、B4には【リスト】で【大人，子供】と入力規則を設定します。
すると、リストからの選択となり、入力間違いを心配する必要ななくなります。
更に、C4にも【リスト】で【1，2，3，4，5，6】としておけば、マウス操作のみで料金が表示されます。