INDEX関数で配列数式のセルを参照すると #REF! の値が表示される現象について紹介します。

現象の確認

B2セルに下記の配列数式を記述します。

={"ぺんぎんクッキー","しろくまアイス","らくだキャラメル"}

B3セルに下記の数式を記述します。
INDEX関数でB2セルの配列数式の2番目の値を取得します。

=INDEX(B2,2)

数式の入力を確定すると #REF! エラーが表示されます。

原因・対処法

スピル機能があるExcelでは配列数式を参照する場合は、セルの参照の末尾に#を追記します。何も記述しない場合は指定したセル委に表示されている値の参照となります。 詳しくはこちらの記事を参照してください。

例

B3セルの数式を下記に変更します。

=INDEX(B2#,2)

B2セルに記述した配列数式の2番目の値が、B3セルに表示されることが確認できます。