How can I cross reference information in a table?
This is a bit wordy, but using the example table as definition (A1:G7):
=INDEX(Sheet1!A2:A7,MATCH(A2,INDEX(Sheet1!B2:G7,,M ATCH(A1,Sheet1!B1:G1))))+
(A2-
INDEX(Sheet1!B2:G7,MATCH(A2,INDEX(Sheet1!B2:G7,,MA TCH(A1,Sheet1!B1:G1))),MATCH(A1,Sheet1!B1:G1)))
*(INDEX(Sheet1!A2:A7,MATCH(A2,INDEX(Sheet1!B2:G7,, MATCH(A1,Sheet1!B1:G1)))+1)
-INDEX(Sheet1!A2:A7,MATCH(A2,INDEX(Sheet1!B2:G7,,MA TCH(A1,Sheet1!B1:G1)))))
/(INDEX(Sheet1!B2:G7,MATCH(A2,INDEX(Sheet1!B2:G7,,M ATCH(A1,Sheet1!B1:G1)))+1,MATCH(A1,Sheet1!B1:G1))-
INDEX(Sheet1!B2:G7,MATCH(A2,INDEX(Sheet1!B2:G7,,MA TCH(A1,Sheet1!B1:G1))),MATCH(A1,Sheet1!B1:G1)))
Follows linear interpolation formula of:
Y = Ya+(X-Xa)*(Yb-Ya)/(Xb-Xa)
--
Best Regards,
Luke M
"Eric" wrote in message
...
I am writing a program in Excel and I want to be able to pull information
from a table, which is 15x11, in another worksheet. In the program,the
user
enters a value which will corresponds to a specific column in the table
(so
this is my x-value). Each column contain a series of increasing numbers.
I
want the program to interpolate within that specific column entered by the
user, to another value that the program had calulated previously, then
reference that to a value in the first column (y-value).
Example:
A1=4.5
B1=545
3.0 3.5 4.0 4.5 5.0 5.5
0 |
1 |
2 |
3 523
4 - - - 555
5
I want the program to return the value of 3.69.
|