To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value in the array are:
=SUMPRODUCT((data=...

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions.
In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data))
How...

To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in K6 is:
{=SUM(--(MMULT(--((data)<70),TRANSPOSE(...

To get the max of every nth column, you can use an array formula based on the MAX, MOD, and COLUMN functions. In the example shown, the formula in M5 is:
=MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5))
Note:...

To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions.
In the example shown, the formula in C5 is:
=COLUMN(INDIRECT(B5&"1...

To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is:
{=INDEX(groups,MATCH(1,MMULT(--(names=...

To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data),...

To sum the last n columns in a table of data (i.e. last 3 columns, last 4 columns, etc.) you can use a formula based on the INDEX function. In the example shown, the formula in K5:
=SUM(INDEX(data,0,COLUMNS(data)-(K4-...

To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is:
{=SUM(--(MMULT(--(data=90),TRANSPOSE(...

To copy values or generate references with a pattern like every 3rd column, every 5th column, etc. you can use a formula based on the the OFFSET and COLUMN functions. In the example shown, the formula in C8 is:
=...

To count with repeating numbers (for example: 1111,2222,3333,4444, etc.) you can use a formula based on the ROUNDUP function, with help from either ROW or COLUMN.
In the example shown, the formula in C4 is:
=ROUNDUP...

To get a full set of relative column numbers in a range, you can use an array formula based on the COLUMN function.
In the example shown, the array formula in B4:H4 is:
{=COLUMN(B4:H4)-COLUMN(B4)+1}
On the worksheet...

To sum every nth column, you can use a formula based on the SUMPRODUCT, MOD, and COLUMN functions.
In the example shown, the formula in L5 is:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
How this...

To generate a fixed value every N columns (for example, a fixed expense every 3 months, a fixed payment every 6 months, etc.) you can use a formula based on the MOD function.
In the example shown, generate a value of...

You can get the last column in a range with a formula based on the COLUMN function.
In the example shown, the formula in cell F5 is:
=MIN(COLUMN(data))+COLUMNS(data)-1
where data is a named range for B5:D10
How...