ExcelのINDEXとMATCH関数でスピル機能？が邪魔をする

こんにちは。
皆様お助け下さい！

画像をご覧ください。
E2セルに以下の式を入力しています。
=INDEX(A1:A7,MATCH(C2:C5,A1:A7,0),0)

以前のバージョンののExcelでは、A列とマッチするセルがあれば抽出してこられたのですが（画像の例ですと「H」がE2セルに返ります）、現在のExcelのバージョンでは、画像のように下方へ自動的に式が入力されて、E2に回答が表示されなくなりました。

スピル機能だと思うのですが、これを解除する方法を調べると、先頭に@を入れる方法があったので試してみたのですが、結果エラーになるだけでした。
スピル機能を解除する方法はないと書かれているページもありましが、本当にないものでしょうか？

また以前と同じようにE2セルに回答を出すにはどうしたらよいでしょうか？

詳しい方どうぞ回答よろしくお願い致します！

質問者からの補足コメント

• 関数に詳しい方、スピル機能をご存じの方回答をお願い致します。

    補足日時：2020/11/11 15:33

No.8 ベストアンサー 回答者： fujillin 回答日時： 2020/11/11 16:32

こんにちは

＞以前のバージョンののExcelでは、A列とマッチするセルがあれば抽出してこられたのですが
信じられません！！

スピル機能のないエクセルを用いていますが、そのようなことはできないですね。
通常の計算で、
　＝MATCH(C2:C5,A1:A7,0)
とした場合、第一引数はC2:C5の代表値としてC2が採用されて計算されるので、#N/Aが返されます。

仮に、これを配列化するために
　=INDEX(MATCH(C2:C5,A1:A7,0),)
としても、計算結果は {#N/A, #N/A, 7, #N/A} となるので、この配列から有効な最小値を取得しない限り、結果は#N/Aとなります。
配列から直接「有効な最小値」を抽出しようとしても、MINやSMALL関数では、範囲にエラー値を含むとエラーを返してしまうのでうまくいかないはずです。

配列数式を用いて
　SMALL(IF(COUNTIF(C2:C5,A1:A7)>0,ROW(A1:A7),10),1)
のような方法で計算することでindex値の７を得る手法は、旧いバージョンでよくみられた方法です。
（ですので、ご質問文の方法で抽出できるたとはとても思えません）
上記の他にも、近年のエクセルなら、AGGREGATE関数を用いることで、配列数式化しなくても配列計算をさせることは可能です。
（SUMPRODUCT関数にも似たような効果を期待できます）

新しいバージョンをお使いなのであれば、FILTER関数が使えると思いますので（私の環境では使えませんが）、そちらを利用して抽出する方法をとれば、はるかに簡単に抽出できるものと思います。
https://support.microsoft.com/ja-jp/office/filte …

この回答へのお礼

回答ありがとうございます！

確かにそうですね。。。
以前の式と何か勘違いしてる気がしてきました。

もう一度見直してみます。
AGGREGATE関数は知らなかったので調べてみます！

ありがとうございました！

お礼日時：2020/11/11 17:12

No.7 回答者： tatsumaru77 回答日時： 2020/11/11 16:05

No5の方へ

>e2には、何が出て欲しいんですか？
「H」を表示したいと質問に書かれています。

質問者様へ
こちらでexcel2007で確認しても、同様にE2には＃N/Aが表示されます。
スピル機能はexcel2007ではサポートしていないので、スピル機能が原因とは思えません。
うまくいっていたときのexcelのバージョンはいくつでしょうか。

この回答へのお礼

ありがとうございました！

お礼日時：2020/11/11 17:14

No.6 回答者： banzaiA 回答日時： 2020/11/11 16:03

エラー処理

＝IFERROR(･･･
を入れるのはだめですか？

画像を見る限りA1:A7に存在しない文字Aや空白を検出しています。
で、
=IFERROR(INDEX(A1:A7,MATCH(C2:C5,A1:A7,0),0),"")
というようなものでは解決にならないですか？

この回答へのお礼

ありがとうございました！

お礼日時：2020/11/11 17:17

No.5 回答者： くんこば 回答日時： 2020/11/11 15:56

スピル機能ってのが新しく出来たんですね。

知らなかった。

で、e2には、何が出て欲しいんですか？

No.4 回答者： tatsumaru77 回答日時： 2020/11/11 15:09

＞E4にはちゃんと答えが返っているので、これがE2に返ればいいだけなんですけどね。

。。
C2のAはA列にないと思いますが、いかがでしょうか。
私が解決案を回答できるわけではありませんが
excelのうまくいってるバージョンとうまくいってないバージョンを提示されると良い回答が得られるかもしれません。

この回答へのお礼

回答ありがとうございます！

C2のAは、A列になくて良いのです。
A列とC列を比較して、合致するものを抽出するというものなので。

お礼日時：2020/11/11 15:24

No.3 回答者： くんこば 回答日時： 2020/11/11 14:56

やっぱり、matchの検査値は、配列じゃなくセル単位だと思うんだけど。

c2にしてみたら？

この回答へのお礼

ありがとうございます！

INDEXで配列になるのでmatchはこれで正常に動作します。

c2のセルだけを選択した場合は求めるものが出ません。
そのため配列にしています。

お礼日時：2020/11/11 15:26

No.2 回答者： くんこば 回答日時： 2020/11/11 14:36

match関数の方はちゃんと取れてる？

この回答へのお礼

はい問題ありません。
INDEXと組み合わせて配列になってますので、これでちゃんと値は返ってました。

お礼日時：2020/11/11 14:46

No.1 回答者： くんこば 回答日時： 2020/11/11 14:25

第三引数をゼロから１にしてもダメかな？

この回答へのお礼

回答ありがとうございます！

はい、同じなんです。。。

E4にはちゃんと答えが返っているので、これがE2に返ればいいだけなんですけどね。。。

お礼日時：2020/11/11 14:31