エクセルのvlookup関数で、前後の値を参照したい（分かりにくくてすみません）

質問

A 　　　　 B 　　 　 C 　　　 D 　　　E 　　　 F 　　　G　　　　H
1 　　深さ　　荷重1　荷重2　　荷重3　　　　　　　　　深さ　　荷重1　
2 　 　1.5 　　100 　 　 150 　 　 200 　　　　　 　　　　2.5　　　135
3 　　 2.0 　 　120 　 　 180 　 　 300　　　　　　　　　　
4 　　3.0 　 　 150 　 　200 　 　 350

上記のようなA1からD4までの表があり、深さと荷重の種類によって値が決まります。
G2に深さを入力した時に、H2に荷重1の値が表示されるようにしたいのですが、G2の深さは表にある値とは限らず、「1.8」や「2.45」などの半端な数値を入力することもあります。その場合、H2の値は上下の深さに当たる荷重を比例配分するようにしたいのです（たとえば上のように、G2「2.5」ならH2は「135」と表示）。
また実際の表はもっと縦に長く、参照する深さの間隔もまちまちです。
私は単純なvlookup関数しか使ったことがなく、前後の２つの値を参照する方法などあるのでしょうか。
分かりにくい表と説明で申し訳ありません。
どなたか詳しい方、教えていただければ幸いです。

回答No.3

imogasi · ベストアンサー

VOOKUP関数もいいが、MATCH関数は何番目を返し、第３引数にー１，０，１の３つがあり、０は完全一致を探す場合です。本件のように間隔に落ちるかどうかを探すのは０以外で、本質問は昇順なので、指定した値以下の最大値を探す１を使います。
深さ
0
1.5
2
3
4.5
6.2
で
値が
G列　　H列　I列
1.2 2 3
1.6 3 4
3.1 5 6
5 6 7
4.4 5 6
2.5 4 5
ときH列は
=MATCH(G2,$A$1:$A$100,1)で下方向に複写すると上記H列のようになります。これに１プラスした行数（I列）との間に、落ちる値であることがわかります。
後は２番目と３番目の値をINDEX関数で採って、比例配分させればよいです。
また列方向でなく行方向でもMATCH関数は使えます。
H7に２．５が有るとして
=INDEX($B$1:$B$100,H7,1)+(INDEX($B$1:$B$100,H7+1,1)-INDEX($B$1:$B$100,H7,1))*((G7-INDEX($A$1:$A$100,H7,1))/(INDEX($A$1:$A$100,H7+1,1)-INDEX($A$1:$A$100,H7,1)))
で１３５になりました。
個人的好みでは、関数では長くなるのでVBAにして、式はセルから隠したいですね。

お礼

有難うございました。
私には高度すぎて読んでて頭がおかしくなりそうでしたが、
じっくり時間をかけて試してみたら、できました！！
すごくうれしいです。有難うございました！！！

回答No.2

mu2011

ご提示の表に最少値行と最大値行を設定するとして、次の方法で如何でしょうか。
H2セルに=IF(COUNTIF($A$2:$A$6,G2)=0,VLOOKUP(G2,$A$2:$D$6,2)+(OFFSET($A$2,MATCH(G2,$A$2:$A$6,1),1)-OFFSET($A$1,MATCH(G2,$A$2:$A$6,1),1))*(G2-OFFSET($A$1,MATCH(G2,$A$2:$A$6,1),)),VLOOKUP(G2,$A$2:$D$6,2))

お礼

有難うございます。
OFFSET関数やMATCH関数なんて使ったことがありませんでした。
何とか試してみたんですが、値がうまく比例配分されないんです。
私のやり方が間違ってるのかな・・・。

回答No.1

noname#204879

Sheet1　　　　　　　　　　　　　　　Sheet2
　　A　　　B　　　C　　　D　　　　　　　A　　　B　　　C　　　D
1　深さ　荷重1　荷重2　荷重3　　　　1　深さ　荷重1　荷重2　荷重3
2　 1.5　　100　　150　　200　　　　2　　-3　　150　　200　　350
3　　 2　　120　　180　　300　　　　3　　-2　　120　　180　　300
4　　 3　　150　　200　　350　　　　4　-1.5　　100　　150　　200

A1:D4 → list0　　　　　　　　　　　A1:D4 → list1

Sheet1!H2: =VLOOKUP(G2,list0,MATCH(H1,A1:D1,0))+(VLOOKUP(-G2,list1,MATCH(H1,A1:D1,0))-VLOOKUP(G2,list0,MATCH(H1,A1:D1,0)))*(G2-VLOOKUP(G2,list0,1))/(-VLOOKUP(-G2,list1,1)-VLOOKUP(G2,list0,1))

お礼

有難うございます。
理解するのに時間がかかりそうですが、がんばってみます！