Interpolation with VLOOKUP

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

Hi!

Depends on what result you're after:

If you think the result shoud be 135:

D1 = 147

=INDEX(A1:A4,MATCH(D1,B1:B4))

If you think the result should be 157:

The table would need to be sorted in descending order

=INDEX(A1:A4,MATCH(D1,B1:B4,-1))

Biff

"Dave_AD" <DaveAD@discussions.microsoft.com> wrote in message
news:9B2AD859-FEA4-4403-8921-1E14191B4AC3@microsoft.com...
> 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

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" <DaveAD@discussions.microsoft.com> wrote in message
news:9B2AD859-FEA4-4403-8921-1E14191B4AC3@microsoft.com...
> 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

Thanks for the help. It works like a charm.
--
Dave_DD


"Niek Otten" wrote:

> 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" <DaveAD@discussions.microsoft.com> wrote in message
> news:9B2AD859-FEA4-4403-8921-1E14191B4AC3@microsoft.com...
> > 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
>
>
>