How do I use a row value as a variable to refer to an address in excel

Question

I have a cell that uses match function to find the location from a column and I want to use that location value to refer to another column.

For example:

• cell G1 stores the value 49 from the function Match(223, D:D, 0)

• G2 will refer to column E and use the value from G1 for row

• so G2 should return E49. If the value of G1 was 55 G2 would be E52.

How would i do that?

Answer 1

INDEX would be a better method - INDIRECT is volatile, while INDEX isn't.
=INDEX($E:$E,MATCH(223,$D:$D, 0)) or =INDEX($E:$E,$G$1) (return a reference to the 49th row in column E).

http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

Answer 2

What you could try is an INDIRECT function.

Try this in cell G2:
=INDIRECT("E"&G1)