You did not give your definition of "interpolate" or examples of desired
answers for several numbers. This may??? be what you want for col A when
your number is entered in c1

=INDEX(A:A,MATCH(C1,B:B,-1))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CAPTGNVR" wrote in message
...
DEAR BERNIE

Thanks for the response and the formula is beyond me to understand and it
is
amazing how nicely u have done it. Wish you could tell me what the
formula
does bcos I have left out this 'trend' etc thinking too much for graph
etc.

But the way you have cleverly mixed it up with 'offset' makes it curious
for
me to know in detail.

However I tried to see for other values and the final results are not
correct.
For ex when i enter 17520 in cell A1, #value error comes and when i enter
18650.1 it does not give 0.8 and instead gives .9946.

Finally if u can give me a short brief on how your formula is manipulated,
i
will much obliged.

brgds/captgnvr

"Bernie Deitrick" wrote:

Captain,

With 18725 in cell A1, and your table in C1:D6

=TREND(OFFSET($C$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),OFFSET($D$1,MATCH(A1,$D$1:$D$6,-1),0,2,1),A1)

This will return 0.50453648915186

HTH,
Bernie
MS Excel MVP


"CAPTGNVR" wrote in message
...
DEAR ALL

Can you pls give me how to write the INDEX AND MATCH function so that I
can
get the left value in col-D interpolated for a col-D value say 18725.

col-C col-D
0.20 18800.30
0.40 18750.00
0.60 18700.80
0.80 18650.10
1.00 18620.90
1.20 17520.80

Other options to use sort col-D in ascending and then shift the col-C
to
col-E will work. But pls advice only as above bcos I have hundreds of
columns like this and dont want to go around sorting etc. The data is
entered like this and want to proceed from here. Pls help this with
"INDEX
AND MATCH" function or through VB or with most appropriate way.

BRGDS/CAPTGNVR