0/COUNTIF(D2,"*"&$A$2:$A$9&"*") は一つの値が返ってくるわけではないよ
分解していくと
"*"&$A$2:$A$9&"*" → {*$A$2*; *$A$3*; *$A$4*; ... ; *$A$8*; *$A$9*}
COUNTIFが終わった時点で(値は適当。D2にA4の文字列だけが含まれていた場合。エラーを#で表す)
0/{0; 0; 1; ... ; 0; 0}
={#; #; 0; ... ; #; #}

なので　LOOKUP(1,0/COUNTIF(D2,"*"&$A$2:$A$17&"*"),$B$2:$B$17) は
LOOKUP(1, {#; #; 0; ... ; #; #}, $B$2:$B$17)　となる
LOOKUP 第一引数の１に最も近い第一引数以下の最大値を選ぶと、 3番目の0が採用されるから B4の値が出る