エクセルVLOOKUPで検査値以上の最小の値をもとめるには？

質問

検索範囲A1：A31
検査値B1で

=VLOOKUP(B1,A1:A31,TRUE)
では、検査値以下の最大の値しか取得できなかったのですが、検査値以上の最小の値をもとめるにはどうすればいいのでしょう？
(VLOOKUP関数に限定するわけではありませんが、VLOOKUPで出来るよと聞いたものですから・・・・)

回答No.1

134 · ベストアンサー

=min(if(a1:a31>=b1,a1:a31,""))
と入力して、ShiftとCtrlを押しながら、Enterで確定する…という関数では、いかがでしょうか？

お礼

ありがとうございます。
出来ました。

これって検査範囲の並び順を問わないんですね？

同様に、
=MAX(IF(A1:A31<=B1,A1:A31,""))
で、検査値以下の最大の値が取得できるんですね？

以上の理解でよろしいのでしょうか？

回答No.4

Wendy02

merlionXX　さんへ
#1のレス:
＞同様に、
＞=MAX(IF(A1:A31<=B1,A1:A31,""))
＞で、検査値以下の最大の値が取得できるんですね？
できると思います。

これは、シミュレートしてみればいいのです。
数式バーやセルの数式F2で開いて、式の一部の
　　IF(A1:A31<=B1,A1:A31,"")
をマスウ選択して、F9 を押せば、その内容が実体化しますから、そこから、Max や Min をつけられることが分かりますね。

例:
{1;2;3;4;5;6;7;8;8;9;9;9;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

だから、MAX側は、
=MAX((A1:A31<=B1)*(A1:A31))
「0」は、影響を与えないから、乗算も可能ですね。

{　} 付きが、配列です。「;」が、縦で、「,」が横ですね。
{1,10;2,3}
縦横混在

４角形の配列では、縦よりも、横が優先されます。Z字状にデータを拾ってきます。

お礼

Wendy02さん、いつもありがとうございます。

まだ配列数式をよく理解できていないのでおっしゃることの半分もわかりませんでしたが、F9 を押せば、その内容が実体化するというのは新鮮な驚きでした。

ありがとうございます。

回答No.3

Wendy02

Minを使った配列数式は、発表されていますので、それ以外で考えてみました。
=SMALL(A1:A31,MATCH(B1,A1:A31,1)+ISERROR(MATCH(B1,A1:A31,0)))

shinkun0114さんのアイデア、なるほどね。
じゃあ、配列にして並べ替えてしまえばよいわけですね。

=-VLOOKUP(-B1,SMALL(-A1:A31,ROW(A1:A31)),1)
配列数式なので、式を入れた後に、Shift＋Ctrl － Enterで入れてください。

お礼

なるほど、配列数式にすれば作業列は不要ですね。
いつもありがとうございます。

回答No.2

shinkun0114

ちょっと裏技チックですが、負の数字の場合、
絶対値の関係がひっくり返る現象を利用しては
どうでしょう？

　0 5 10 15　・・・
と並んでいる場合、検査値が8であれば、vlookupでは
5を返します。しかし、
　-15 -10 -5 0
と並んでいる場合、検査値が-8であれば、-10が帰ってきます。

１．負の検査範囲を作る
　　B1セルに「=-A1」を入力し、B31まで下にコピーします。
　　B1:B31をコピーし、「形式を選択して貼り付け」を選び、
　　[値(V)]を指定して、B1:B31に上書きで貼り付けます。
　　その後、データ並び替えで、昇順にしておきます。

２．検査値以上の最小値を検索する式
　　（検査値の位置をC1にした場合）
　　=-VLOOKUP(-C1,B1:B31,TRUE)
　　と入力します。

お礼

ありがとうございます。
ただ、作業列を別に作りたくないのです。
すみません。