Excel～MATCH関数で値が範囲内の何番目かを表示しよう～エクセル

指定した範囲において検索値が範囲の上から、もしくは左から何番目にあるかを表示するMATCH関数について説明します。

下の画像の例でセルD2にセルC2の商品がセルA2からセルA6の商品の範囲の中で上から何番目にあるか表示したい場合はセルD2に=MATCH(C2,$A$2:$A$6,0)と入力してEnterを押します。オートフィルした時に範囲がズレないように範囲に絶対参照$を使用しています。

検査値はセルC2の商品を参照したいのでC2になります。

検査範囲はセルA2からセルA6の商品名の範囲を指定したいので$A$2:$A$6になります。

[照合の型]は検査値と等しい値を検索したいので0になります。

つまりセルD2に=MATCH(C2,$A$2:$A$6,0)と入力しEnterを押すとセルC2の商品名"みかん"はセルA2からセルA6の検査範囲の上から3番目なので3が表示されます。

セルD2からセルD4までオートフィルすれば、C列の商品名がセルA2からセルA6の商品名の範囲の中の上から何番目かがD列に表示されます。

任意の文字列を含む事を検査値にする

任意の文字列を含むことを検査値に指定したい場合はワイルドカードの*(アスタリスク)か?(クエスチョンマーク)を使います。*は文字数無制限の文字列を意味します。例えば"あ*"は最初に"あ"を含む文字列を意味します。"あいうえお"でも"あい"でも条件に一致したとみなされます。"*お"は最後に"お"を含む文字列を意味します。"あいうえお"でも"えお"でも条件に一致するとみなされます。?は1文字を意味します。たとえば"あ?"は最初に"あ"を含む2文字を意味します。"あい"や"あう"は条件に一致しますが、"あいう"は条件に一致しません。最初に"あ"を含む3文字を条件にしたい場合は"あ??"とします。

下の画像の例でセルD2に、名前に"Cさん"を含む人がセルA2からセルA7の名前の範囲の上から何番目か表示したい場合はセルD2に=MATCH(C2,A2:A7,0)と入力しEnterを押します。

検査値はセルC2のワイルドカードを含む名前を参照したいのでC2になります。

検査範囲はセルA2からセルA7の名前の範囲を指定したいのでA2:A7になります。

[照合の型]は検査値と等しい値を検索したいので0になります。

つまりセルD2に=MATCH(C2,A2:A7,0)と入力しEnterを押すとセルC2の名前"Cさん*"は文字列の最初に"Cさん"を含む全ての文字列を意味するのでセルA2からセルA7の検査範囲の中で、これに当てはまるのは"Cさん(2班)"なので"Cさん(2班)"はセルA2からセルA7の範囲の上から3番目なので3が表示されます。

検査値をワイルドカードを使わない"Cさん"にすると"Cさん(2班)"と一致しないとみなされ、検査範囲に検査値が含まれないことになるのでエラーになります。

INDEX関数との組み合わせ

下の画像の例でセルG2に、緑の表から"みかん"の単価を抽出したい場合はセルG2に=INDEX(A2:D6,MATCH(F2,A2:A6,0),MATCH(G1,A1:D1,0))と入力しEnterを押します。

INDEX関数はINDEX(配列, 行番号, [列番号])で配列の中の指定した行列の値を返します。

INDEX関数の配列は緑の表のデータ部分を指定するのでA2:D6になります。

INDEX関数の行番号はセルF2の商品名"みかん"を参照して"みかん"が緑の表の商品の範囲セルA2からセルA6の上から何番目かを求めるのでMATCH(F2,A2:A6,0)になります。"みかん"はセルA2からセルA6の範囲の上から3番目なので3となり、これがINDEX関数の行番号に使われます。

INDEX関数の[列番号]はセルG2の見出し"単価"を参照して"単価"が緑の表の見出しの範囲セルA1からセルD1の左から何番目かを求めるのでMATCH(G1,A1:D1,0)になります。"単価"はセルA1からセルD1の範囲の左から2番目なので2となり、これがINDEX関数の[列番号]に使われます。

つまりセルD2に=INDEX(A2:D6,MATCH(F2,A2:A6,0),MATCH(G1,A1:D1,0))と入力しEnterを押すと、この数式は=INDEX(A2:D6,3,2)を意味するのでセルA2からセルD6の範囲の上から3行目、左から2列目の値30が表示されます。

セルG2からセルG3までオートフィルすれば各行のF列の商品の単価がG列に表示されます。

[照合の型]が1の例

下の画像の例でセルC2に、セルB2のAさんのポイントが、オレンジの表の獲得商品のどれに該当するか表示したい場合は=INDEX($F$2:$F$7,MATCH(B2,$E$2:$E$7,1))と入力しEnterを押します。オートフィルした時に範囲がズレないようにINDEX関数の配列とMATCH関数の検査範囲に絶対参照$を使用しています。

INDEX関数はINDEX(配列, 行番号, [列番号])で配列の中の指定した行列の値を返します。

INDEX関数の配列はオレンジの表の獲得商品の範囲セルF2からセルF7を指定するので$F$2:$F$7になります。

INDEX関数の行番号にはMATCH(B2,$E$2:$E$7,1)が入力されています。これはMATCH関数の検査値にセルB2のポイントを参照していて、検査範囲は$E$2:$E$7なのでオレンジの表のポイントの範囲になります。MATCH関数の[照合の型]は1なので検査値以下の最大の値を検索します。つまりオレンジの表のポイントの範囲でセルB2のポイント146以下の最大の値は100になるので100は範囲の中の上から2番目なのでこの式は2を意味し、これがINDEX関数の行番号に使われます。

INDEX関数の[列番号]は配列が1列なので省略しています。

つまりセルC2に=INDEX($F$2:$F$7,MATCH(B2,$E$2:$E$7,1))と入力しEnterを押すと、この数式は=INDEX($F$2:$F$7,2)を意味するのでセルF2からセルF7の範囲の上から2行目の文字列"タオル"が表示されます。

セルC2からセルC7までオートフィルすると各行のB列のポイント数に応じた獲得商品がC列に表示されます。

エラー

下の画像の例でセルD2にセルC2の商品がセルA2からセルA6の商品の範囲の中で上から何番目にあるか表示したい場合にセルD2に=MATCH(C2,$A$2:$A$6,0)と入力してEnterを押すと、セルC2の商品"ぶどう"はセルA2からセルA6の商品の範囲の中に含まれないのでエラー値#N/Aが表示されます。