use the result of match function as a part of row address
 

I want to use the result of match function as a part of row address.
that is;
if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0))
things like that.

is it possible?

use the result of match function as a part of row address
 

=INDEX($A$1:$A$7, MATCH(B7,$B$1:$B$7,0))
or
=INDIRECT("A"&MATCH(B7,$B$1:$B$7,0))

but I usually prefer index for referencing another cell or doing some sort
of lookup as Indirect is volatile (recalculates every time XL does) and, if
you were to move the data in column A (cut/paste or inserting a column and
shifting column A to column B), the indirect function will not adjust itself
to reference the new location of the data. Or, depending on your data, that
may be exactly what you want.





"kang" wrote:

I want to use the result of match function as a part of row address.
that is;
if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0))
things like that.

is it possible?

use the result of match function as a part of row address
 

="$A$"&MATCH(B7,$B$1:$B$7,0)


"kang" wrote:

I want to use the result of match function as a part of row address.
that is;
if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0))
things like that.

is it possible?

use the result of match function as a part of row address
 

Thanks a million.

JMB wrote:
=INDEX($A$1:$A$7, MATCH(B7,$B$1:$B$7,0))
or
=INDIRECT("A"&MATCH(B7,$B$1:$B$7,0))

but I usually prefer index for referencing another cell or doing some sort
of lookup as Indirect is volatile (recalculates every time XL does) and, if
you were to move the data in column A (cut/paste or inserting a column and
shifting column A to column B), the indirect function will not adjust itself
to reference the new location of the data. Or, depending on your data, that
may be exactly what you want.





"kang" wrote:

I want to use the result of match function as a part of row address.
that is;
if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0))
things like that.

is it possible?