Finding values in a Matrix

Question

I have a matrix, inwhich I want to identify a column based on a row values, go down in that column until I find the value that I am looking for and then go across that row into the first column of that row and provide that value. How do I do that?

Answer 1

As the column for lookup is determined by the (x,1) and (x,y) is looked up in that columns, I think that bringing OFFSET() into the mix might be the best method. If I'm interpreting your description correctly, this is a simplistic representation of your data matrix (B2:F6) and known values (B9 & C9).

                

The formula in D9 that returns Y is,

=INDEX($B$3:$B$6,MATCH(C9,OFFSET($B$2,1,MATCH(B9,$C$2:$F$2,0),4,1),0))

INDEX function
MATCH function
OFFSET function

Edit: typed "The formula in B9 that returns Y is" insteaf of "The formula in D9 that returns Y is". Corrected description. Formula did not need modification.

Answer 2

Thank you for the kind words.

Answer 3

Jeeped,

Thanks a lot. It worked great!

Answer 4

Not sure how that would work. The function above would give me the output at position X and Y within the matrix. I know values at position (X,1) & position (X,Y) within the maxtrix and want function provide me with the value at position (1,Y). So if I use Match function within the Index function, I can determine X position with the value at function (X,1) but not sure how I can determine the Y position by knowing the X,Y value and then having that information provide me the value located at (1,Y) position.

Answer 5

You would use a form of INDEX(...MATCH(),MATCH()).

INDEX function
MATCH function

Post back with some specifics if you require more help.