エクセル関数 複数条件での抽出について

質問

エクセル関数について質問です。
行と列の交差する位置の値を取り出す方法に困っています。

添付の表があり、表の下の各セル、種類1（セルB15）、種類2（セルB16）、種類3（セルB17）に検索したい番号、記号をそれぞれ入力すると、3つの条件と合致する果物がセルB18に表示されるようにしたいのですが、
例）種類1「1」、種類2「2」、種類3「B」と入力→果物「りんご」
セルB18に=INDEX(C2:F13,MATCH(B15,A2:A13,0),MATCH(B16,B2:B13,0 ),MATCH(B17,C1:F1,0))の数式を入れるとエラー値 #REF! が返ります。
DGET関数も試しました。お手上げです・・

どうか教えていただけますでしょうか。エクセルのバージョンは2010を使用しています。
説明下手で申し訳ありません。よろしくお願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.5

kagakusuki

以下の関数を使いますと、B2～B5に入力されている「種類２」の値と、B6～B9に入力されている「種類２」の値、B10～B13に入力されている「種類２」の値が、それぞれ全く異なっている場合においても、正しい果物名を表示させる事が出来ます。

=IF(OR($B$15="",$B$16="",$B$17=""),"",IF(COUNTIFS($A$2:$A$13,$B$15,$B$2:$B$13,$B$16)*COUNTIF($C$1:$F$1,$B$17),INDEX(INDEX($C$2:$C$13,MATCH($B$15,$A$2:$A$13,0)):$F$13,MATCH($B$16,INDEX($B$2:$B$13,MATCH($B$15,$A$2:$A$13,0)):$B$13,0),MATCH($B$17,$C$1:$F$1,0)),"【該当無し】"))

或いは

=IF(OR($B$15="",$B$16="",$B$17=""),"",IF(SUMPRODUCT(($A$2:$A$13=$B$15)*($B$2:$B$13=$B$16))*COUNTIF($C$1:$F$1,$B$17),INDEX(INDEX($C$2:$C$13,MATCH($B$15,$A$2:$A$13,0)):$F$13,MATCH($B$16,INDEX($B$2:$B$13,MATCH($B$15,$A$2:$A$13,0)):$B$13,0),MATCH($B$17,$C$1:$F$1,0)),"【該当無し】"))

お礼

ご回答ありがとうございました。
勉強になりました。

回答No.4

bunjii

>セルB18に=INDEX(C2:F13,MATCH(B15,A2:A13,0),MATCH(B16,B2:B13,0 ),MATCH(B17,C1:F1,0))の数式を入れるとエラー値 #REF! が返ります。
INDEX関数の引数の扱い方に誤りがあります。

=INDEX((C2:F5,C6:F9,C10:F13),MATCH(B16,B2:B13,0),MATCH(B17,C1:F1,0),MATCH(B15,A2:A13,0))

第1引数の範囲は3つの領域に分割し、カンマ区切りとして括弧で括ります。
第2引数は行番号なのでB列の値を検索します。
第3引数は列番号なので1行目の種類3を検索します。
第4引数は領域番号なのでA列の種類1のグループから領域を検索します。

お礼

ご回答ありがとうございました。
勉強になりました。

回答No.3

MarcoRossiItaly

抽出というか、位置を指定して参照したいということですよね。必要な数式は既に回答が出ているので、ベストアンサーは辞退します。

関数の仕様を無視して勝手に引数の種類数を増やしてもうまく行かないのは、あまりにも当然のことです。

むしろ INDEX 関数の場合、今回の課題では第 4 引数はいらないのに、この関数の別の書式においては第 4 引数を指定する場合もあるので、書き方によってお示しの数式がたまたまエラーを表示しなかったりすると（知識がないとそれも難しいかもしれませんが）、かえって誤りに気付きにくいことにもなり得ると言えます。

まずは、ちゃんと関数の仕様どおりに書くよう努めることです。そのためには、ヘルプなどをきちんと読むことです。

ところで、今回の目的のためには INDEX でいいと思いますが、次式は、（セル範囲形式の）INDEX の代わりに、OFFSET というのを使ってもできるということの紹介です。OFFSET は、ゼロや負の移動方向を指定したり、セル範囲の大きさを変える機能も持っています。関数のヘルプや、参考 URL を参考にしてください。

B18　=offset(c2,4*(b15-1)+b16-1,match(b17,c1:f1,)-1)

※セル範囲の大きさを変えないため OFFSET の第 4・第 5 引数を省略した形

参考URL：

http://excel-ubara.com/excel3/EXCEL015.html

お礼

ご回答ありがとうございました。
勉強になりました。

回答No.2

noname#204879

B18: =INDEX(C2:F13,SUMPRODUCT((A2:A13=B15)*(B2:B13=B16)*ROW(A2:A13))-1,MATCH(B17,C1:F1,0))

お礼

ご回答ありがとうございました。
勉強になりました。

回答No.1

keithin

ご相談に書かれてる例示の通りなのであれば、特に「種類２」が規則正しく１，２，３，４と繰り返し並んでいるならお話はとても簡単で、
=INDEX(C2:F13,MATCH(B15,A2:A13,0)+MATCH(B16,B2:B5,0)-1,MATCH(B17,C1:F1,0))
とでもしておけばＯＫです。
事実に応じて、自力で適切に応用してみて下さい。

＃まぁもっと言えば
=INDEX(C2:F13,B15*4+B16-3,MATCH(B17,C1:F1,0))
でも十分って事になりますけどね。

お礼

ご回答ありがとうございました。
勉強になりました。