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

  • Thread starter Thread starter kang
  • Start date Start date
K

kang

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?
 
=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.
 
Thanks a million.
=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.
Click to expand...
 
Back
Top