From: Edu on 31 Mar 2010 14:33 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 From: Thanks on 31 Mar 2010 16:05 Provided that your (X,Y) values are constant with : Column labels in B1:D1 and Row labels in A2:A4, (X:Y) Values in B2:D3 your X Value in G1 and your (X,Y) in G2. =SUMPRODUCT((B1:D1=G1)*(B2:D4=G2)*A2:A4) The formula will return a 0 if the value is not found. You may want to add data validation to restrict entries for G1 and G2 "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 > . >  |