INDEX関数を使用した関数の意味を教えてください。

質問

INDEX(sheet2!C:C,SMALL(INDEX((sheet2!$B$1:$B$100<>$B$18)*10^4+ROW($1:$100),),ROW(A1)))&""
sheet2にある表のB列に検索時に使用する文字列があり、sheet1のB18にその検索したい文字列を入力すると、一致したB列と同じ行のC列のデータを抽出する式です。
表のデータは今後も増加しますが、どの程度増加するは不明です。

INDEX関数やSMALL関数など一つ一つの関数の意味はわかっているつもりですが、なぜ上記のセルが選択されているのかわかりません。
特にSMALLの後の式がわかりません。
エクセル関数にはあまり詳しくないので、この式の意味をできれば詳しく教えていただけないでしょうか？
（式の意味は人から聞いたものですが、違っていたらすいません）

回答No.2

grumpy_the_dwarf · ベストアンサー

要するに=vlookup(B18,Sheet2!B1:C100,FALSE)を小難しく書いてい
るわけですが、まず、small関数の第二引数はrow(A1)ですから常に
1ですね。min関数でいいのに無駄なことをしています。

次に、index((sheet2!$B$1:$B$100<>$B$18)*10^4+row($1:$100),)
ですが、これは(sheet2!$B$1:$B$100<>$B$18)つまりsheet2にある
表のB列がB18と一致しないかの論理値の配列
{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE；....}に10^4をかけてやると、
{10000;10000;10000;0;10000;10000;....}になり、対応する行番号
を加算して、{10001;10002;10003;4;10005;10006;....}という配列
がかえってくるわけです。

この配列の最小値は4、これで最初のindex関数は、sheet2!C:Cの4
番目の値を返すことが決まりました。そして、この表は10000行を
超えると破綻することも判りますね。唯一vlookupより便利なの
は、B18に表に無い値を入力した場合にもエラーにならずsheet2!C1
を返すことでしょうか。

お礼

わかりやすく、説明していただき、ありがとうございました。

回答No.1

mshr1962

sheet2!$B$1:$B$100<>$B$18)*10^4
Sheet2のB列（1～100行）がB18の内容と同じなら0、違うなら1000になります。
ROW($1:$100)
Sheet2のB列の比較した行数になります。
上記の和は一致した場合は行数、一致しない場合は行数+1000になります。
次にINDEX関数を利用しているのはSMALL関数の初めの引数が配列のためです。
SMALL(INDEX関数で配列になった上記の結果,ROW(A1))
で一致する小さい方からの行数が順に表示されます。（一致しない場合は1000以上）

最後の&""は空欄対策と思われます。セル参照元が空欄の場合表示は空欄でなく0になります。
空欄&""とすることで文字列化して""を表示（見た目は空欄）

お礼

ご回答ありがとうございました。
式の意味が理解できました。