スプレッドシート関数／INDEX、MATCH

スプレッドシートにて、INDEX、MATCHにて条件に一致するセルの値を返す計算をしたいのですが、
以下例のように、すべて同一の値が算出されてしまいました。

どなたか指定条件の誤りについてご指摘、
ほか方法についてご教示くださいませ。


※以下のようにスプレッドシートAのE列「未完了」に一致する
aa、ab、adの値をスプレッドシートＢに返したく、次のような関数を「スプレッドシートB」のA２に指定したところ、
「未完了」に一致するすべて同一の値が返されてしまいました。。

=INDEX('スプレッドシートA'!$A$2:$E$500,MATCH("未完了",'スプレッドシートA'!$E$2:$E$500,0),MATCH(A$1,'スプレッドシートA'!'!$A$1:$E$1,0))

＜例＞スプレッドシートA
ーーーーーーーーーーーーーーーーーー
A列　　　B列　　C列　　D列　　　E列
aa　　　ab　　　ac　　　ad　　　ae
東京都　××　　△△　　 ▼▼　　未完了
東京都　●●　　◎◎　　××　　未完了
埼玉県　××　　△△　　○○　　　完了
・
・
・
ーーーーーーーーーーーーーーーーーー


＜例＞スプレッドシートB　　→以下のようにすべて同一の値のみ返されてしまった。。
ーーーーーーーーーーーーーーーーーー
A列　　　B列　　C列　　
aa　　　ab　　　ad　　　
東京都　××　　▼▼　　
東京都　××　　▼▼　
東京都　××　　▼▼
東京都　××　　▼▼
東京都　××　　▼▼　　
・
・
・
ーーーーーーーーーーーーーーーーーー

No.1 ベストアンサー 回答者： fujillin 回答日時： 2018/06/05 10:22

こんにちは。

　回答が無いようですので・・・

スプレッドシートはまったく知りませんが、MSエクセルとある程度共通点があるものとして、類推での回答です。
ですので、以下の例示式は正しくないかも知れませんが、考え方が伝われば良いものとして記していますので、適宜修正を願います。

どのような結果を期待なさっているのかハッキリとはわかりませんが、ご提示の式ではほとんどのセル指定は絶対指定ですから、その部分に関してはフィルコピーで変わることは無いものと思います。
変化するのは
INDEX(参照, 行, 列) の列の部分だけのように見受けられます。
　MATCH(A$1,'スプレッドシートA'!'!$A$1:$E$1,0)　の部分がこれに該当します。
これに関しては、ご提示の結果にもきちんと反映されているようですが、行指定に当たる
　MATCH("未完了",'スプレッドシートA'!$E$2:$E$500,0)
の部分は、行が変わっても常に同じ値になるので
　＞以下のようにすべて同一の値のみ返されてしまった
というのは、式の示す通りの結果だと言えます。

もしも、『E列が「未完了」と記されている行を「詰めて抽出」したい』というような意味でしたら、エクセルでは配列数式を利用し、SMALL関数でその順位を指定することで行の参照位置を得る方法が用いられています。
そもそも、スプレッドシートで配列数式のような概念が存在するのかどうかわかりませんが、式を分解して作業列を利用することで「詰めて週出」は可能になると思われます。

一応、その考え方を記しておきますと、
（以下の式はそのままでスプレッドソートで動作するのかはわかりません）
1）シートAに作業列（例えばF列）を設け、以下の式をフィルコピーしておく
　=IF(E2="未完了",COUNTIF(E$2:E2,E2),"")
これによって、未完了に該当する行にだけ、上から順に1、2、3・・・と表示されるはずです。

2）シートBのINDEX関数の行の参照指定を、上記作業列をMATCHで検索する形式に変更する
　MATCH(Row()-1,'スプレッドシートA'!$F$2:$F$500,0)
・・・といった感じです。
このような要領で、「詰めて抽出」が可能になるものと思います。


作業列を使わない方法としては、エクセルの「配列数式」のような考え方がスプレッドシートにも存在するならば、上記の考え方を応用することで可能であろうと想像します。
あるいは、スプレッドシートでもスクリプトが利用できたように記憶していますので、スクリプトで解決しちゃうという方法もありそうですね。