エクセルでINDEX+MATCH関数について

質問

INDEX+MATCH関数について
B1：D7、F1：H7には次のようなデータが入力されていて
J1：M3のようなものを作成したいのですが上手くいきません。

J2にコード番号を入力し、品名を表示させ、数を入力し、価格を表示させる
・・・ということをしたいのですが、上手くいきません。

K2には
　=INDEX((C2:D7,G2:H7),MATCH(J2,B2:B7,0),1,LEFT(J2,1))
という関数が入っていて
K2にはりんご
M2には500　　と表示され上手くいきます。

コード番号100番台は上手くいくのですが、200番台になると＃N/Aとエラー表記になります。
どこをどう修正すれば上手くいくのでしょうか。
教えてください。

回答No.2

tom04 · ベストアンサー

こんばんは！
ご希望の方法と違うかも知れませんが・・・
一例です。

画像通りの配置として、

K2セルに
=IF(J2="","",INDEX($B$1:$H$1000,SUMPRODUCT(($B$1:$H$1000=J2)*ROW($A$1:$A$1000)),SUMPRODUCT(($B$1:$H$1000=J2)*COLUMN($A$1:$G$1))+1))

M2セルに
=IF(J2="","",INDEX($B$1:$H$1000,SUMPRODUCT(($B$1:$H$1000=J2)*ROW($A$1:$A$1000)),SUMPRODUCT(($B$1:$H$1000=J2)*COLUMN($A$1:$G$1))+2))

という数式を入れ、それぞれのセルをオートフィルで下へコピーではどうでしょうか？

尚、上記の方法であれば仮に300台番・400番台・・・とデータが増えても
INDEX関数のデータ範囲を換えるだけで対応できると思います。
的外れならごめんなさいね。m(__)m

お礼

データが増えることを見越しての案、この方法は素晴らしいですね。
ただ、ＲＯＷ関数の範囲を(＄Ａ＄1:＄Ａ＄1000）、
　　　ＣＯＬＵＭＮ関数の範囲を(＄Ａ＄1:＄Ｇ＄1)+1）としているのはなぜですか？
(なぜＡ列を範囲に？)

回答No.6

kagakusuki

回答番号ANo.4です。

>＊２＾０これはどういう意味なのでしょうか？

　2^0を計算すると1となり、*2^0は結局、*1と同じ事ですから、本来であれば無くても構わない部分なのですが、他の部分にある*2^2という部分と、どの様な関係にあるのかを判り易くする目にあえて付け加えた部分です。（ですから、省略しても構いません）
　ANo.4の数式

=IF(AND($C2<>"",ISNUMBER($D2)),VLOOKUP($B2,OFFSET(Sheet1!$B:$D,,(COLUMNS(Sheet1!$B:$J)-1)-(COLUMNS(Sheet1!$B:$F)-1)*INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))),3,FALSE)*$D2,"")

の中の

(COLUMNS(Sheet1!$B:$J)-1)-(COLUMNS(Sheet1!$B:$F)-1)*INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))

の部分は、万が一、重複するコード番号が、異なる列に存在していた場合に、誤った検索結果を出さない様にするために設けた部分です。
　Sheet1のB列、F列、J列の順に優先して検索を行い、左手側にある列の中に検索対象のコード番号が見つかった場合には、それよりも右側にある列中に、同じコード番号が存在していても、より左側の列中にある方のデーターを優先して表示し、右側の列中のデーターを無視するようにしています。
　例えば、C2に入力されているコード番号と同じ番号が、Sheet1のB5セルとJ9セルに存在していた場合、

COUNTIF(Sheet1!$J:$J,$B2)>0　　の判定結果は1
COUNTIF(Sheet1!$F:$F,$B2)>0　　の判定結果は0
COUNTIF(Sheet1!$B:$B,$B2)>0　　の判定結果は1

になりますから、

INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))
=INT(LOG(1*2^0+0*2^1+1*2^2,2))
=INT(LOG(1+0+4,2))
=INT(LOG(5,2))
=INT(2.321928・・・・)
=2

となります。
　ANO.4の例において、コード番号とデーターは、コード番号がB列に記されているグループ、F列に記されているグループ、J列に記されているグループの3つのグループに分けられていますが、この2という値は、最も右側にあるJグループから数えて、"2"グループ分だけ左方向にずれた位置にあるB列上に、検索すべきコード番号が存在している事を表します。
　Sheet1のB列、F列、J列の各々の列に、該当するコード番号が存在しているか否かの判定に、2のn乗を乗じた数の合計値を、2進数で表すと、該当するコード番号が、どの列に存在するのかを表す数値となりますから、その事を利用して、対数を計算するLOG関数と、小数点以下を切り捨てるINT関数を使用して、2進数で表した時の桁数を求める事で、該当するコード番号が存在する列の内、最も左側に位置する列が、どの列なのかを算出している訳です。　
　一方、

COLUMNS(Sheet1!$B:$F)-1

の部分は、Sheet1において、コード番号が入力されている列が、何列ずつの間隔を空けて配置されているのかを算出しています。
　B列からF列までは4列のずれがありますから、この数値は4となります。
　又、

COLUMNS(Sheet1!$B:$J)-1

の部分は、Sheet1において、コード番号が入力されている複数の列の内、最も右側にある列（この場合はJ列）と、最も左側にある列（この場合はB列）との、列番号の差（この場合は8）を算出しています。
　従って、

(COLUMNS(Sheet1!$B:$J)-1)-(COLUMNS(Sheet1!$B:$F)-1)*INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))
=8-4*2
=0

の部分は、「コード番号が入力されている列が配置されている間隔を表す値である4」と、「該当するコード番号が存在する列の内、最も左側にある列は、J列かから左方向に向かって数えて、何グループ分ずれ他グループに当るのかを表す値である2」を掛け合わせた数値を、「コード番号が入力されている列の範囲を表す値である8」から引いた値である0となります。
　この部分は、OFFSET関数の列数（をどれだけずらすか）を指定するための場所に入力されていますから、この部分の計算結果が0であるという事は、結局、

VLOOKUP($B2,OFFSET(Sheet1!$B:$D,,(COLUMNS(Sheet1!$B:$J)-1)-(COLUMNS(Sheet1!$B:$F)-1)*INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))),3,FALSE)

という部分は、OFFSET関数の基準（となるセル範囲）であるSheet1!$B:$Dのセル範囲から、右方向に0列だけずれた位置にあるセル範囲において、VLOOKUP関数による検索を行うという関数になっています。

お礼

解り易い解説ありがとうございます。
なるほど！こういう計算をしていたのですね。

回答No.5

tom04

No.2です！
数式の質問に関してですが・・・

あくまで表内のコード番号に重複がないという前提で前回のような数式にしてみました。

INDEX関数で範囲指定した行番号・列番号が判ればそのセルが表示されますので、
前回の数式ではINDEX関数でB1～H1000の範囲で、
（SUMPRODUCT関数を使っていますので、あまりにデータ量が多すぎるとPCに負担をかけてしまいます。そのためとりあえず1000行目まで対応できる数式にしてみました。）

まず、行を検索する数式
SUMPRODUCT(($B$1:$H$1000=J2)*ROW($A$1:$A$1000)　は
前半部　$B$1:$H$1000=J2　は範囲内でJ2セルのデータがある行だけが「TRUE」→　「1」となり、それ以外は「FALSE」→「0」となります。
それに　ROW($A$1:$A$1000　（1～1000）を掛けているのでデータがある行のみの数値の拾い出しができます。
尚、この　ROW($A$1:$A$1000)　の部分は別にA列に限定しなくても　ROW($B$1:$B$1000)　でも同じ結果になります。

そして列に関しても同様にヒットする列番号を表示させる数式が後半部分になります。
ただ、そのままではヒットするコード番号のセルそのものになってしまいますので、
お示しのデータではその一つ右側セルに「品名」、二つ右側セルに「価格」がありますので、
+1　や　+2　としている訳です。

INDEX関数で範囲指定した1行目及び1列目からの行数・列数が必要ですので、INDEX関数で行検索の場合は
SUMPRODUCT(($B$1:$H$1000=J2)*ROW($A$1:$A$1000)
として範囲の行数とROWの行数（必ず1行目から）を合わせる必要があります。
同様に列検索も
SUMPRODUCT(($B$1:$H$1000=J2)*COLUMN($A$1:$G$1)

この数式も　COLUMN($A$1:$G$1)　部分に関しては
COLUMN($A$100:$G$100)　のように行数はいくつでも関係なく重要なのは列番号です！

としているのは必ず1列目（A列から）INDEX関数で範囲指定した列と同じ列数だけ掛けておきます。
それに列数をいくつプラスするかだけの問題です。

以上、長々と書きましたが
この程度でよろしいでしょうか？m(_ _)m

お礼

解り易い解説ありがとうございます。
理解できました。

回答No.4

kagakusuki

●もし、コード番号の一覧表が、御質問に添付されている画像の様に、2グループのみに分けられている場合には、以下の様な方法となります。

　まず、K2セルに次の数式を入力して下さい。

=IF(COUNTIF($B:$B,$J2)>0,VLOOKUP($J2,$B:$D,2,FALSE),IF(COUNTIF($F:$F,$J2)>0,VLOOKUP($J2,$F:$H,2,FALSE),""))

　次に、M2に次の数式を入力して下さい。

=IF($K2="","",VLOOKUP($J2,IF(COUNTIF($B:$B,$J2)>0,$B:$D,$F:$H),3,FALSE))

　そして、K2～M2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

●尚、コード番号の一覧表が、2グループよりも多くのグループに分けられている場合には、以下の様な方法となります。

　今仮に、下記の添付画像の上半分の様に、3グループに分けられているコード番号の一覧表がSheet1に存在していて、御質問に添付されている画像におけるJ列～M列と同様の表示を、下記の添付画像の下半分の様に、Sheet2のB列～E列に表示させるものとします。

　まず、Sheet2のC2セルに次の数式を入力して下さい。

=IF(OR($B2="",COUNTIF(Sheet1!$B:$B,$B2)+COUNTIF(Sheet1!$F:$F,$B2)+COUNTIF(Sheet1!$J:$J,$B2)=0),"",VLOOKUP($B2,OFFSET(Sheet1!$B:$D,,(COLUMNS(Sheet1!$B:$J)-1)-(COLUMNS(Sheet1!$B:$F)-1)*INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))),2,FALSE))

　次に、Sheet2のE2に次の数式を入力して下さい。

=IF(AND($C2<>"",ISNUMBER($D2)),VLOOKUP($B2,OFFSET(Sheet1!$B:$D,,(COLUMNS(Sheet1!$B:$J)-1)-(COLUMNS(Sheet1!$B:$F)-1)*INT(LOG((COUNTIF(Sheet1!$J:$J,$B2)>0)*2^0+(COUNTIF(Sheet1!$F:$F,$B2)>0)*2^1+(COUNTIF(Sheet1!$B:$B,$B2)>0)*2^2,2))),3,FALSE)*$D2,"")

　そして、Sheet2のC2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

お礼

なんだか見慣れない関数がちらほら見えます。
＊２＾０これはどういう意味なのでしょうか？
自分が以下に勉強不足なのだと痛感しています。

回答No.3

mu2011

>コード番号100番台は上手くいくのですが、200番台になると＃N/Aとエラー表記になります。
　⇒MATCH関数の範囲が１００番台のみだから当たり前です。又、INDEX関数の使用方法も間違っています。
　　修正案としては、１００番台表の直後に２００番台の表を移動し、１表にする事が一番ではないでしょうか、vLOOKUP関数で簡単に抽出できます。

　　仮に出来ない場合はコード入力の左端数字でスイッチキーとして数式を切り替える。
　　一例です、=CHOOSE(LEFT(J2,1)*1,VLOOKUP(J2,B2:D7,2,FALSE),VLOOKUP(J2,F2:H7,2,FALSE))

お礼

そうですね。１表にしてＶＬＯＯＫＵＰ関数で簡単に出来るのは知っていますが、データがおそらく増えていくだろうと、質問のような方法を考えていたのですが、スイッチキーという方法もあるのですね。

回答No.1

mt2008

コード番号が200番台の時のMATCHが考えられていませんから＃N/Aになります
強引にやるんならこんな感じかな？
=INDEX((C2:C7,G2:G7),MATCH(J2,IF(J2<200,B2:B7,F2:F7),0),1,LEFT(J2,1))

お礼

コード番号が200番台の時のMATCHが考えられていないなら当然ですよね。
間違いの指摘、修正案ありがとうございます。