Lookup within a 2 dimensional array
 

Hi guys,

Here's a challenge I hope you can help me solve.


Say I have a table of values with X and Y headers and a 3 x 3 array
hoding parameters that are a function of X and Y, call these f (X, Y)
as below.


Y
0.1 0.2 0.3
1 10 15 20
X 2 15 20 30
3 20 30 40


Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
(X, Y) of 20, and the formula needs to return the value of X.


From the table above the result I would want to extract is X = 2. The
formula needs to look down the second column (Y=0.2) until it finds f
(X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
where X and Y are known and the value of f(X, Y) is required, but I
am
finding this trickier.


Please assume the values are always exact matches. I may require
bilinear interpolation if the numbers are not exact - but I can do
that myself.


Any suggestions are greatly appreciated.


Thanks
Edu

Lookup within a 2 dimensional array
 

See your other post...

"Edu" wrote:

Hi guys,

Here's a challenge I hope you can help me solve.


Say I have a table of values with X and Y headers and a 3 x 3 array
hoding parameters that are a function of X and Y, call these f (X, Y)
as below.


Y
0.1 0.2 0.3
1 10 15 20
X 2 15 20 30
3 20 30 40


Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
(X, Y) of 20, and the formula needs to return the value of X.


From the table above the result I would want to extract is X = 2. The
formula needs to look down the second column (Y=0.2) until it finds f
(X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
where X and Y are known and the value of f(X, Y) is required, but I
am
finding this trickier.


Please assume the values are always exact matches. I may require
bilinear interpolation if the numbers are not exact - but I can do
that myself.


Any suggestions are greatly appreciated.


Thanks
Edu
.

Lookup within a 2 dimensional array
 

I put your table in A1:B4 (so row 1 reads: blank | 0.1| 0.2 | 0.3
In F1 I entered 0.2 and in G1 I entered 20 (your lookup values)
In H2 I used =MATCH(F1,B1:D1,0) to find that 0.1 occurs in the second item
of the Y range (value 2)
In I1 I used =MATCH(G1,INDEX(B2:D4,,H1),0) to fins where the 20 occurred
(again 2)
In J1 I used =INDEX(A2:A4,I1) to retrieve the X value
Putting this together in one formula
=INDEX(A2:A4,MATCH(G1,INDEX(B2:D4,,MATCH(F1,B1:D1, 0)),0))
To make this easier to follow I then used named ranges
=INDEX(xvector,MATCH(datalook,INDEX(mydata,,MATCH( ylook,yvector,0)),0))
xvector is the 1,2,2 in column A
yvector the 0.1, .2 .3 in row 1
mydata is the 3 by 3 table
datalook is the number (20) to be found in mydata
ylook is the value (0.2) to be located in yvector

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Edu" wrote in message
...
Hi guys,

Here's a challenge I hope you can help me solve.


Say I have a table of values with X and Y headers and a 3 x 3 array
hoding parameters that are a function of X and Y, call these f (X, Y)
as below.


Y
0.1 0.2 0.3
1 10 15 20
X 2 15 20 30
3 20 30 40


Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
(X, Y) of 20, and the formula needs to return the value of X.


From the table above the result I would want to extract is X = 2. The
formula needs to look down the second column (Y=0.2) until it finds f
(X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
where X and Y are known and the value of f(X, Y) is required, but I
am
finding this trickier.


Please assume the values are always exact matches. I may require
bilinear interpolation if the numbers are not exact - but I can do
that myself.


Any suggestions are greatly appreciated.


Thanks
Edu

Lookup within a 2 dimensional array
 

=INDEX(A1:A4,MATCH(20,INDEX(A1:D4,,MATCH(0.2,A1:D1 ,)),))



"Edu" wrote:

Hi guys,

Here's a challenge I hope you can help me solve.


Say I have a table of values with X and Y headers and a 3 x 3 array
hoding parameters that are a function of X and Y, call these f (X, Y)
as below.


Y
0.1 0.2 0.3
1 10 15 20
X 2 15 20 30
3 20 30 40


Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
(X, Y) of 20, and the formula needs to return the value of X.


From the table above the result I would want to extract is X = 2. The
formula needs to look down the second column (Y=0.2) until it finds f
(X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
where X and Y are known and the value of f(X, Y) is required, but I
am
finding this trickier.


Please assume the values are always exact matches. I may require
bilinear interpolation if the numbers are not exact - but I can do
that myself.


Any suggestions are greatly appreciated.


Thanks
Edu
.

Lookup within a 2 dimensional array
 

Perfect, thanks for your help guys. Much appreciated.
E

Lookup within a 2 dimensional array
 

On Mar 31, 2:30*pm, Edu wrote:
Perfect, thanks for your help guys. Much appreciated.
E


You guys are really good.