Excelの関数LOOKUP(1,0/FIND(....))の意味

質問

Excelの関数やVBAなどをTips集の本で勉強中です。
その中の「検索値の一部の値を含むリストから抽出したい」という項目があります。例としては、

A15:B20の範囲に、

足立区 100
荒川区 200
板橋区 300
江戸川区 400
大田区 500
葛飾区 600

という区コード表が入力されており、
A1に「東京都板橋区大原町1-1-1」と入力されているとき、B1に上のテーブルから区コードを求めて表示する、とあります。

このときB1に入る式が、
=LOOKUP(1,0/FIND($A$15:$A$20,A1,$B$15:$B$20)
となっているのですが、説明を読んでもなぜこの式で抽出できるのかがわかりません。（というか、この式の説明がないのです。こう入力すると求められます、としか書いてなくて…）

特に、
・LOOKUP関数の最初の1とはなんなのか？（普通ここには検索値が入りますよね）
・0/FIND(...) のところの意味は？
がわかりません。

ネットで検索しても、0/というのは日付などで大量にヒットするため、解説を見つけられません。誰か教えてください。

回答No.1

maron--5 · ベストアンサー

>=LOOKUP(1,0/FIND($A$15:$A$20,A1,$B$15:$B$20)
◆式が違っていませんか？
=LOOKUP(1,0/FIND($A$15:$A$20,A1),$B$15:$B$20)
◆こんなことでは、ないでしょうか？

１）式=LOOKUP(1,0/FIND($A$15:$A$20,A1),$B$15:$B$20)　が入力されているセルを指定します
２）数式バーで、「0/FIND($A$15:$A$20,A1)」を選択して、「F9」を押します
３）表示が、「{#VALUE!;#VALUE!;0;#VALUE!;#VALUE!;#VALUE!}」になります
４）LOOKUP関数は、LOOKUP(検査値,検査範囲,対応範囲)　です
５）検査範囲に、検査値が無い場合は「検査値に一致する値がないと、検査値以下の値の最大値を検査結果とします。」
６）ということで、３番目の０を検索し、対応範囲の３番目の「300」を表示します

◆　0/FIND(...) 　にすることで、検索値の「1」より小さくしています

お礼

なるほど！延々と考えていたのですが、大変わかりやすい
説明で納得できました。それにしてもExcelは奥が深いですね…。

回答No.2

Wendy02

こんばんは。

余計な話なのかもしれませんが、私は、その数式の成立について考えてみました。だから、直接の回答ではありません。

#1さんが書いていらっしゃるから、同じことは書きませんが、その数式を導き出すための、確実な論理や方法が見出せません。この数式を作った人は、偶然の産物か、もしくは、内部的な作成コードを知っている人だと思います。Excelの関数の一般的な仕様に反するように思います。LOOKUPは、他社との共有化を図る関数だそうですから、もしかしたら、他社製の仕様ではないでしょうか？

FIND($A$15:$A$20,A1)
=
{#VALUE!;#VALUE!;4;#VALUE!;#VALUE!;#VALUE!}

分子が0だから、その数字の部分は、0 になるけれど、本来は、FINDの戻り値ですね。
調べてみると、LOOKUPは、VALUEエラー値だけで、他のエラー値は受けないようです。

この場合、私なら、Excelでは、エラー値を、TRUE/FALSE に変換しないといけないと思ってしまいます。エラー値の連続の中で、値のみを見つけ出すような関数は、一般関数にはなかったように思います。エラー値が入れば、再び、同じか別のエラーを出すはずです。その仕様自体は、なぜ、そのようになっているのかは、製作側が提示していないとするれば、後は、偶然に一人の人が見つけたとしか思えません。

例えば、以下のような、私が配列で使うINDEX(ISERRORのINDEXは、過去の人たちが、使っていたワザなのですが、

=INDEX($B$15:$B$20,MATCH(FALSE,INDEX(ISERROR(FIND($A$15:$A$20,A1)),,),0),)

誰かが、見つけたワザです。私は、それを「定石」と呼んではいます。もともと、関数は、本当にMSが一環して作っているとは思えませんから、いろんな仕様があるのだとは思います。ある種、バグのようなもので、それは、自分が見つけようとしても、無理だと思います。こういうものは、ある日突然、神様のいたずらで見つかることはあっても、求めて求められるものではないような気がします。関数は、しょせん、結果論だから、VBAよりも遥かに難しい部分があると思います。

お礼

詳しい考察をありがとうございます。

なるほど、確かにこのTipsに関しては、いくら考えてでもわからなかった理由がわかりました。

他のTipsは、どんなに複雑でも、それぞれの関数の外部仕様を理解してよく考えれば、「なるほど、これがこうだからこうなるのか」と仕組みが理解できました。しかしこのやり方に関してはいくら考えてもわからない。その理由が、仰るようにExcelの一般的な仕様に反した内部的な仕様を利用していたものだから、とすると説明がつきます。

Excelの関数の仕様について、まだ何が一般的かもわからない状態ですが、このように一般的でない内部的な仕様（あるいはバグ？）を利用したやり方は、将来的にExcelがバージョンアップしたときに、FIND()の機能として公開されている仕様に変更がなければ普通に使っている分には問題ないかもしれませんが、内部的な動作が変わってしまうと互換性がなくなって動かなくなるかもしれない、という可能性がありますね。

どうもありがとうございました。