Hi Ben,
try something like this:
Lets say your table lies in the range A1:B5. And the lookup value lies in
A10, then use:
=IF(ISNUMBER(MATCH(A10,A1:A5,0)),VLOOKUP(A10,A1:B5 ,2),(A10-OFFSET($A$1,COUNT
IF(A1:A5,"<"&A10)-1,0))/(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),0)-OFFSET($A$1,C
OUNTIF(A1:A5,"<"&A10)-1,0))*(OFFSET($A$1,COUNTIF(A1:A5,"<"&A10),1)-OFFSET($A
$1,COUNTIF(A1:A5,"<"&A10)-1,1))+OFFSET($A$1,COUNTIF(A1:A5,"<"&A10)-1,1))
Explanation:
The above formula consists of the following parts:
1. The first MATCH statement checks if there is a perfect match of the
lookup value in the first column, if yes then it uses a VLOOKUP function to
get the corresponding value in columns B (note the use of 2 in the vlookup
to reference the column B. Since you have upto column E, change this number
to 3, 4, or 5 to reference columns C, D, or E)
2. The second part is the interpolation formula which is used incase there
is no perfect match. In the offset formula change the last attribute to suit
your column. For column B, 1 is used, so for E use 4. The second part works
for all values in the column A except for the very first value, for which
the vlookup in the first part is used.
Mangesh
"Ben" wrote in message
...
Mangesh,
I have come across your response to this thread and you're obviously the
person I need to communicate with. I have tried to use your formula but
as
yet have got nowhere.
I have a similar query, I need to interpolate for a value in a table in
this
case tank volumes and other info.
Depth of water is in 20 cm increments in col A with volume and other info
in cols B through E I would like to extract info from any given 1 cm
increment .
Alternatively I can do the maths if someone can tell me how to get the
cell
above and below my required value in col A for vlookup.
Many thanks
Ben
"mangesh_yadav" wrote:
You are probably looking for this (interpolation) formula:
=(C2-LOOKUP(C2,$A$2:$A$12))/(INDEX($A$2:$B$12,MATCH(C2,$A$2:$A$12,1)+1,1)-LO
OKUP(C2,$A$2:$A$12))*(INDEX($A$2:$B$12,MATCH(C2,$A $2:$A$12,1)+1,2)-LOOKUP(C2
,$A$2:$A$12,$B$2:$B$12))+LOOKUP(C2,$A$2:$A$12,$B$2 :$B$12)
Substitute A12 or B12 with your last row 151.
Mangesh
--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread:
http://www.excelforum.com/showthread...hreadid=380042