エクセルのINDEX（MATCH)関数

質問

エクセル2010で、INEX関数とMATCH関数を組み合わせて、表の交点のセルの値を求める式を作ったのですが、列方向に使用する器具をNo１、No2、・・・としたのですが、No9までは正しく検索できるのですが、No10以降になると、No１の値を参照してくることに気付きました。
ちなみにNo20、No21はNo2の行を、No30台はNo3の行を参照してくる事が分かりました。
そこでNoを取って単に数字だけにするとちゃんと11、22,、33に該当する行を正しく参照することも分かりました。
原因は何となく分かる（最初の3文字目までで検索する）のですが、対策はどうすれば良いのでしょう？

回答No.6

bunjii · ベストアンサー

>どちらにしても、bunjiiーさんが嘘でしょう、と言われるので確認のために同じ表を作成して確認しましたがやはりNo11、No23、No34等は、No1、No2、No3に相当する行の値を引っ張ってきます。
この結果を添付したかったのですが。。。。
当方で検証した結果を画像で提示します。
あなたが提示された数式では検索範囲が狭すぎますので当方では広げました。
K2=INDEX($C$3:$H$49,MATCH(J3,$B$3:$B$49),MATCH(J4,$C$2:$H$2))
K3=MATCH(J3,$B$3:$B$49)
K4=MATCH(J4,$C$2:$H$2)
No11で検索するとMATCH関数の返り値はあなたが言うように1になります。
しかし、No23で検索するとMATCH関数の返り値は23になり、あなたが言う2にはなりません。
また、No34で検索するとMATCH関数の返り値は34となり、あなたが言う3にはなりません。
あなたが提示された数式の検索範囲にはNo23、No34が存在していませんので1番近い値のNo2やNo3の行を返してきたものと思います。
検索値と同じ値が有ればそれを探して返すはずです。
但し、前の回答で言いましたように昇順でソートしていることが条件になります。

お礼

毎度のことですが当方のウッカリや表現不足でお手数をお掛けしてしまいました。

今回もおかげさまで解決出来ました。

補足

何度もお手数をおかけし恐縮です。
当方も添付と全く同じ表を作って確認しているのですが、検索Noとアルファベットはリストから選択するようにしてあった表なので検索値が無い筈はないと思うのですが・・・・

再度確認しましたがやはりおかしいのですが、検索方法を０（完全一致）にすれば確実に正解が出ることが分かりましたので、申し訳ありませんが本質問はここまでとさせていただきます。

回答No.5

msMike

》 Noを取って単に数字だけにするとちゃんと
》 11、22,、33に該当する行を正しく参照する
》 ことも分かりました。
》 対策はどうすれば良いのでしょう？
「該当する行」の書式を
"No"G/標準
または
"No."G/標準
に設定し、数値 1、2、3、…、11、22、33 を入力したら如何？

お礼

いつもお世話になります。
早々のご回答ありがとうございました。
ご提案を試してみて正常に検索できることを確認しました。
この方法でも解決できること、参考になりました。

但し、当方このような書式設定はあまり使ったことが無い（思いつかない）ので、オーソドックスに検索方法（完全一致）を入れて対応することにします。

回答No.4

bunjii

>列方向に使用する器具をNo１、No2、・・・としたのですが、No9までは正しく検索できるのですが、No10以降になると、No１の値を参照してくることに気付きました。
逆ではないでしょうか？
No2～No9までは正しい位置を返さないのではないでしょうか？
No.01～No.99のように桁数を合わせれば正常に目的のセル位置が返るでしょう。
但し、検索方法を0にすれば正しいセル位置が返って来るはずです。
MATCH関数は検索値が文字列のとき検索方法を省略すると文字コード順に昇順でソートされていない検索範囲からは目的通りの値が得られないようです。

>ちなみにNo20、No21はNo2の行を、No30台はNo3の行を参照してくる事が分かりました。
嘘でしょう。
No1～No99と仮定して列方向に文字列を配置してあるのでしたら「No20、No21はNo2の行を」では辻褄が合いません。「No20、No21はNo2の列を」であれば文言としては矛盾しませんが返って来る値は違います。
No2が検索値のときNo20の1つ手前の列位置を返すはずです。
MATCH関数で検索範囲が文字コード順の配置のときは近似検索が可能です。（検索方法を無指定または1のとき昇順でソートしてください）
検索方法を0に指定すると完全一致のセル位置を返しますが一致する値が無いときは#N/Aエラーが返ります。
A1～W1にNo1、No2、・・・・、No25、No26と配置されている範囲を対象にA2へ検索値としてNo2と入力してB2へ次の数式を設定するとNo19と入力されたS1の列番号である19が返ります。
=MATCH(A2,A1:Z1) → 19
しかし、検索方法の0を指定した次の数式ではNo2と入力されたB1セルの列番号の2が返ります。
=MATCH(A2,A1:Z1,0) → 2
尚、A1～Z1へNo1、No10、No11、・・・、No19、No2、No20、・・・のように昇順で配置されていれば前者の数式でもNo2が入力されているL1セルの列番号の12が返ります。
文字列の大きさは文字コード（数値）と文字列長で判断するようです。

お礼

毎度お手数をおかけしております。

解説非常に良く分かり、勉強になりました。
（当方の現象と少し違っているように思う部分も有りますが）

補足

毎回つたない文書の為にお手数をおかけする事になり恐縮です。
表を添付しようとjpgファイルにしたのですが、先日より何故かファイルが添付されなくなってしまったので文言だけの質問になってしました。

先ずは表の「列方向に・・」という意味は縦方向にB3にNo1、B4にNo2・・です。
行（横）方向には、C2にA、D2にB、E2にC、F2にDと入力してあります。

どちらにしても、bunjiiーさんが嘘でしょう、と言われるので確認のために同じ表を作成して確認しましたがやはりNo11、No23、No34等は、No1、No2、No3に相当する行の値を引っ張ってきます。
この結果を添付したかったのですが。。。。

式は下記の通りです
=INDEX($C$3:$F$15,MATCH(C17,$B$3:$B$15),MATCH(C18,$C$2:$F$2))

尚、皆様ご指摘の「照合の種類」を入れるのが正しいやり方だと思いました。

この関数の組合せは過去に何度か使っているのですが、これまでは「照合の種類」は入れたことが無く、問題も無かったので気付きませんでした。

回答No.3

imogasi

MATCH関数の解説にあるように、第３引数の「照合の型」について
省略すると、「１」の扱いなので、「０」を明確に指定してはどうですか。
ーー
MATCH関数の解説から
1 または省略
MATCH 関数は、検査値以下の最大の値を検索します。検査範囲の引数の値は、昇順の並べ替えでは、1 ～ 9、A ～ Z、あ～ん、FALSE ～ TRUE の順に配置されます。
0
MATCH 関数は、検査値と等しい最初の値を検索します。検査範囲の引数の値は、任意の順序で指定できます。
-1
MATCH 関数は、検査値以上の最小の値を検索します。検査範囲の引数の値は、降順の並べ替えでは、9 ～ 1、Z ～ A、ん～あ、TRUE ～ FALSE の順に配置されます。
本件原因が、この問題でなければ、すみません。

お礼

ご回答、解説ありがとうございます。

本件の原因はまさにご指摘の通りでした。
過去にも検索条件を入れずに混乱した経験が有り、学習能力が低いことを反省しております。

回答No.2

Nobu-W

http://office-qa.com/Excel/ex48.htm
こんなんとか・・・

https://allabout.co.jp/gm/gc/297772/
こんなの・・・

参考になりませんか？　＞＜

お礼

早々のご回答ありがとうございます。

リンク非常に参考になりました。
勉強します。

回答No.1

watabe007

>ちなみにNo20、No21はNo2の行を、No30台はNo3の行を参照してくる
MATCH(検査値, 検査範囲, [照合の型])
[照合の型]を1で設定しているのでは
0で設定し直してみてください。

お礼

早々のご回答ありがとうございました。

ご指摘の通りで解決しました。
過去に何度かこの関数の組あわせは使ており、「照合の型」はとりあえず省略してもうまく行っていたので気付きませんでした。

これまでは全く違う文字列だったので問題はなかっただけですが。