Match builtin Excel function

[agarwaldvk]

Does anyone know if the match built-in Excel function accept a rang
name for the lookup array argument (second argument of the matc
function) instead of a range reference.

If it does, the does it matter if the named range refers to a range o
the same or different worksheet to that on which the match function i
being used in?


If it doesn't, does anyone know of any get arounds because I have t
use a named range only since this range is a variable and is determine
elsewhere programmatically through VBA code.

Any assistance would be a big help!


Deepak Agarwa

 

[Ken Wright]

You won't break it by trying, honest

It works fine with a range name, regardless of where that range name is, eg:-

=MATCH(B1,MyRange,0)

*unless* you have the same name on more than one sheet, eg you may have MyRange
on a number of sheets:-

sheet1!MyRange
sheet2!MyRange
sheet3!MyRange
sheet4!MyRange

in which case the example formula given will refer to the range on *that* sheet,
and if you wanted it to refer to that range on any others heet you would have to
include the sheet reference in the formula, eg:-

=MATCH(B1,sheet3!MyRange,0)

 

[agarwaldvk]

thanks Ken

 

[Ken Wright]

You're welcome