Dueling Excel 112


Two Way Lookup Cell Address to Create Dynamic Range: Formula or VBA

DESCRIPTION

See Mr Excel and excelisfun use two methods to lookup a cell reference in a two way table and create a dynamic range for adding: VBA UDF or ADDRESS function or INDEX function:
1) Mr Excel uses VBA to create a user defined function.
2) Excelisfun creates an array formula inside the ADDRESS function, and then INDIRECT and SUM Functions. See the functions: ROW, COLUMN and SUMPRODUCT.
3) Excelisfun creates an array formula inside the INDEX function, and then the SUM Function for adding the dynamic range.
4) Dynamic range for adding
5) Lower corner of dynamic range changes

Formula [1] =SUM(INDIRECT(“A13:”&ADDRESS(SUMPRODUCT((Matrix=A23)*ROW(Matrix)),SUMPRODUCT((Matrix=A23)*COLUMN(Matrix)),4)))

Formula [2] =SUM(A13:INDEX(Matrix,SUMPRODUCT((Matrix=A23)*(ROW(Matrix)-ROW(A13)+1)),SUMPRODUCT((Matrix=A23)*(COLUMN(Matrix)-COLUMN(A13)+1))))

Formula [3] =SUM(OFFSET(A13,,,SUMPRODUCT((Matrix=A23)*(ROW(Matrix)-ROW(A13)+1)),SUMPRODUCT((Matrix=A23)*(COLUMN(Matrix)-COLUMN(A13)+1))))

WORKBOOKS




Be the first to comment

Leave a Reply