convert cell address to corresponding cell

[dexchu2]

I have two columns in my worksheet that represent a graph: the A column has x values and the B column has y values. I want to be able to find the x value that corresponds to the max y value.

I am currently using:
=CELL("address",INDEX(B1:B2005,MATCH(MAX(B1:B2005),B1:B2005,0)))

This function gives me the cell location of the y max, but is there a way to convert this to the corresponding x location?

For example, if the above function outputs $B$1569, I want to know what the value of $A$1569 is.

Thanks for your help!

[teylyn]

Hi

if your output is a cell address, you can use a combination of INDIRECT() and OFFSET() to get the value in a neighbouring cell. If the output of your function is in cell A1, use something like

=OFFSET(INDIRECT(A1),0,-1)

The INDIRECT function will take the value in A1 and interpret it as a cell reference, which the offset function uses to lookup the value in the same row and one column to the left.

HTH

[dexchu2]

Awesome! Thanks a lot.

[teylyn]

Actually, the following should also work. Since you're already using the Index/Match combination to figure out the location of the maximum value, you can use almost the same formula to return the value in column A. Just adjust the range for the index like so:

=INDEX(A1:B2005,MATCH(MAX(B1:B2005),B1:B2005,0),1)