Reverse Index Function

[KateB]

OK, this is a curly one! For the the following dataset, I need to do
a reverse index. Does anyone know if that's possible

10 20 50
5 29.66 32.76 36.11
6 34.69 38.32 42.11
7 39.54 43.68 48.16
10 44.25 48.89 53.91

For example, I need to interrogate the data from column "20" against a
value of 42. This would fall between rows 7 & 10, and interpolation
would give me a row value of 8.57, which is what i'm trying to
calculate (=(10-7)/)(44.25-39.54)*(42-39.54)+7)

I've found a way, if it was an exact match in the data, but not if
it's an inexact match. (http://www.excelforum.com/excel-general/
687000-lookup-inside-a-2d-table.html)

Any ideas? I'm stumped!!

Thanks!
Kate

 

[Bernd P]

Hello Kate,

Enter 20 into F1 and 42 into F2, then
=Interp(OFFSET(A2:A5,,MATCH(F1,B11,0)),A2:A5,F2)
will give you the right result 6.6865671641791 (its between 38.32 and
43.68, I think).

Interp you can find here:
http://sulprobil.com/html/interpolate.html

Regards,
Bernd

 

[Herbert Seidenberg]

Excel 2007
Curve fitting.
y = a - b*e^(-cx^d)
seems to fit the points quite well.
http://www.mediafire.com/file/djzj5dkzkwm/10_30_09.xlsx

 

[barry houdini]

Excel 2007
Curve fitting.
y = a - b*e^(-cx^d)
seems to fit the points quite well.http://www.mediafire.com/file/djzj5dkzkwm/10_30_09.xlsx

Assuming the same setup as Bernd, i.e. table in A15, 20 in F2, $2 in
F2 then use this formula in F3

=MATCH(F1,B11,0)

and then in F4 to get your result

=LOOKUP(F2,INDEX(B24,0,F3),A2:A4+(A3:A5-A2:A4)*(F2-INDEX
(B24,0,F3))/(INDEX(B35,0,F3)-INDEX(B24,0,F3)))

regards, barry

 

[barry houdini]

Assuming the same setup as Bernd, i.e. table in A15, 20 in F2, $2 in
F2 then use this formula in F3

=MATCH(F1,B11,0)

and then in F4 to get your result

=LOOKUP(F2,INDEX(B24,0,F3),A2:A4+(A3:A5-A2:A4)*(F2-INDEX
(B24,0,F3))/(INDEX(B35,0,F3)-INDEX(B24,0,F3)))

regards, barry

Sorry, that should be 42 in F2.....

 

[barry houdini]

Sorry, that should be 42 in F2.....- Hide quoted text -

- Show quoted text -

...and 20 in F1