Excelの指定する列から検索値に最も近い値と行数を抽出する方法について

Excel2013で質問です。
画像のような表があるのですが、指定する列内で検索値に最も近い数字と該当する行数が
抽出されるような関数を教えていただけますでしょうか。

指定する列、検索値は様々に変わりますが、仮にB列の105で指定した場合には
A列２行目の「105」ではなくB列1行目の「160」が近似値として、
またExcelのセルの行数ではなく、表内の行数である「１」が行数として
表示されるようにしたいです。

宜しくお願いします。

No.1 ベストアンサー 回答者： CoalTar 回答日時： 2017/06/29 17:27

Bの165の結果は？

Bの169の結果は？

表の数値は昇順として、たたき台
L14セルに
=IFERROR(MATCH(J14,INDEX(C4:G14,,MATCH(I14,C3:G3,0))),1)
K14セル
=INDEX(C4:G14,L14,MATCH(I14,C3:G3,0))

この回答へのお礼

回答ありがとうございます。
近似の扱いと順序は考慮しておりませんでしたが、頂いた計算式をもとに作成できました。
No.2の方と悩みましたが、最初にご回答いただいたNo.1さんにベストをつけさせていただきます。

お礼日時：2017/07/03 13:48

No.2 回答者： moto_koukousei 回答日時： 2017/07/01 16:37

No.1さんの回答で良いのだと思います。

あえて、妙な条件を加えてみました。
１）表の中には負数はなく、検索値も正数と限定する
２）表の中の数は、昇順、降順にはなっていない
３）検索値に最も近い数は、
　　列内が(8,18,57,96)で、
　　　　検索値75なら、57を選定(57→75=18:75→96=21)
　　　　検索値77なら、96を選定(57→77=20:77→96=19)
　　　　検索値99なら、96を選定

列内から指定値(A)を引いた差分の絶対値で一番小さいの(B)を選ぶ
(B+A)が列内にあれば(B+A)を探していた近似値(C)とする
(B+A)が列内になれば(B-A)の絶対値を探していた近似値(C)とする
列内での近似値(C)の位置(D)を表示する　　　⇒　L14
表示した位置(D)の指定列の値(C)を表示する　⇒　K14

L14の式
=IFERROR(
MATCH(J14+MIN(INDEX(ABS(INDEX(C4:G20,,MATCH(I14,C3:G3,0))-J14),,)),
INDEX(C4:G20,,MATCH(I14,C3:G3,0)),0),
MATCH(ABS(J14-MIN(INDEX(ABS(INDEX(C4:G20,,MATCH(I14,C3:G3,0))-J14),,))),
INDEX(C4:G20,,MATCH(I14,C3:G3,0)),0))
K14の式
=INDEX(C4:G20,L14,MATCH(I14,C3:G3,0))

ただこのような面倒でわかりにくいのは、普通はしないと思います。
適当に作業領域をつくって、そちらに数式で移して、必要なところを選んで、K14、L14に表示する方が実用的です。
近似についても、なにを近似とするかも、目的に応じて限度のようなものを考えないと、ただ一番近いのを自動的に出せばいいということはほとんどないと思います。

この回答へのお礼

回答ありがとうございます。
近似の扱いと順序は考慮しておりませんでしたが、頂いた計算式をもとに作成できました。
No.2の方と悩みましたが、最初にご回答いただいたNo.1さんにベストをつけさせていただきます。

お礼日時：2017/07/03 13:49