View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Interpolation with VLOOKUP

Easiest with a few helper cells. If you don't like that, you can always
compact them to one formula, but I think this way it is more clear what
happens.

I assume your table (without headers) in A1:B4 and the item to look for in
C1

In D1:
=MATCH(C1,A1:A4)
In E1:
=INDEX(A1:A4,D1)
In E2:
=INDEX(A1:A4,D1+1)
In F1:
=INDEX(B1:B4,D1)
In F2:
=INDEX(B1:B4,D1+1)

Your formula:
=E1+(C1-F1)/(F2-F1)*(E2-E1)

Gives 143.5161

Is that what you were looking for?

--
Kind regards,

Niek Otten


"Dave_AD" wrote in message
...
I'm trying to construct a workbook with the ability to interpolate between
2
values.
If we consider two columns of data, for example:

A B
100 97
110 120
135 135
157 166

What function or nested functions will return an "A" value if I input a
"B"
value of 147 ?
--
Dave_DD