指定範囲から任意の近似値を探したい

質問

画像のようなものを作ろうと思っています。
ですが、vlookupやindex・match関数では私の力ではうまい具合にはいきませんでした。

どのように数式を入れるとよろしいのでしょうか？

回答No.4

tom04 · ベストアンサー

こんにちは！

G12セルに
=MIN(IF(B3:E15>G8,B3:E15))
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！

G14セルに
=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:B15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E3)),4)

という数式を入れてみてください。m（_ _）m

お礼

ありがとうございます、両方の式で私の期待していた値を出すことができました。
今回は、こちらをベストアンサーとさせてください。

回答No.7

bunjii

回答No.3です。
検索範囲を勘違いしていたようです。
検索範囲をB3～E15とする場合は次の式を使ってください。

=SUMPRODUCT(MIN((B3:E15>G8)*B3:E15+(B3:E15<=G8)*1000000))

また、検出された値のセルのアドレスを算出する数式を忘れていました。

=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:E15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E15)),4)

お礼

ありがとうございます、目当ての値を出すことができました。

回答No.6

mt2008

近似値が複数あった場合の事を考えると、セル番地を表示するよりは条件付き書式で表のセル色を変えた方が良くないですか。
別な用途の為にセル番地を表示しようとしているのでしたらごめんなさい。

お礼

ありがとうございます、これ単体で使うのではなく
これを利用して別のものを作ろうとしています。
ですが、たしかに色分けして見易くしたほうが確認も容易ですね、取り入れます。
お気遣いありがとうございます

回答No.5

tom04

No.4です。

投稿後気づきました。

No.1さん・No.2さんの回答とほぼダブっています。
どうも失礼しました。m（_ _）m

回答No.3

bunjii

質問の内容でVLOOKUP関数は一致する値またはより小さい値を検出することができます。
INDEX関数とMATCH関数の組み合わせでは同様な結果になりますがMATCH関数の返り値に1を加算すれば目的に合う結果になるでしょう。
他の方法としてはSUMPURODUCT関数とMIN関数を使って行番号を検出してINDEX関数に渡せば目的の値が得られます。

=INDEX(D3:D15,MATCH(G8,D3:D15)+1,1)
=INDEX(D1:D15,SUMPRODUCT(MIN((D3:D15>G8)*ROW(D3:D15)+(D3:D15<=G8)*1000)),1)

補足

回答なされた関数は予めD列にあることが分かっているものではありませんか？
今回は画像のものですと、B3:E15のものが対象です。

上は入力値を変えてみるとエラーがでて、
下は循環依存エラーがでてしまいました。

回答No.2

MackyNo1

近似値を表示するG12セルには以下の式を入力します。

=MIN(INDEX((B3:E15<G8)*100000+(B3:E15-G8),))+G8

セル番地を表示する目的がよくわかりませんが（おそらくもっと合理的な処理があると思いますが）、どうしてもセル番地を表示したいならG14セルには以下の式を入力します。

=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:E15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E15)),4)

補足

上の方の値、残念なことに配列の値が見つからないとvalueエラーがでてしまいました。

下のものは期待通りの値がでました、ありがとうございます。

回答No.1

kybo

以下の様に入力し、CtrlとShiftキーを押しながらEnterキーで確定

=MIN(IF(D3:D15>G8,D3:D15,""))

又は、D2、G7にそれぞれ同じ見出し（例えば、「数値」とか）を入れ、
G8は「>1180」といれ、以下の式でもできます。

=DMIN(D2:D15,1,G7:G8)

お礼

ありがとうございます、こんな単純な式でよかったのですね。期待していた値になってくれました。