Hello All,
I am struggling with constructing a dynamic formula for a company budget file. I installed an offset formula with match functions for both the row and column. Is it possible to install a match function for the offset reference?
The formula I want is something like : =sum(offset(MATCH FUNCTION FOR REFERENCE),(MATCH FUNCTION FOR ROW), (MATCH FUNCTION FOR COLUMN), height, width)
I am getting errors when I try to input a match function for the reference point.
Hi,
You can use a construction for the reference parameter of OFFSET, provided that construction resolves to a worksheet reference.
Since MATCH only ever returns a numeric or an error, this would not be a valid option on its own; a combination of INDEX and MATCH, however, would be, since here iNDEX can be coerced into resolving to a worksheet reference, for example:
=OFFSET(INDEX(A1:A10,MATCH("X",B1:B10,0)),7,0)
would resolve to, assuming the first occurrence of an "X" in column B is in B3:
=OFFSET(INDEX(A1:A10,3),7,0)
which is then equivalent to:
=OFFSET(A3,7,0)
i.e. the value in cell A10.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks