To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in H6 is:
{=MAX(IF(...

To get the last row number in a range, you can use a formula based on the ROW, ROWS, and MIN functions. In the example shown, the formula in cell F5 is:
=MIN(ROW(data))+ROWS(data)-1
where "data" is the named range B5...

To copy values or generate references with a pattern like every 3rd row, every 7th line, etc. you can use a formula based on the the OFFSET and ROW functions. In the example shown, the formula in D5 is:
=OFFSET($B$5,(...

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=...

If you need to sum or add the bottom values in a range, you can do so with a formula that uses the SMALL function wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a...

To flag the last n rows in a range, you can use helper column with a formula based on the ROW and ROWS functions. In the example shown, the formula in cell E5, copied down, is:
=ROW()-ROW(INDEX(data,1,1))+1>ROWS(...

To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is:
{=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(...

To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function.
Assume you have a list of employee names together with hours worked on "Project X", and you want know how...

If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT.
It's also possible to use COUNTIF, as explained below.
Assume you have a...

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...

If you need to dynamically increment a calculation, so that a value automatically increments each time the formula is copied to a new row or column, you can use the ROW() or COLUMN() functions in your formula.
In the...

To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.
In the example shown, the formula in cell H6 is:
{=MAX(IF(...

To reverse a list (i.e. put the items in a list or column in reverse order) you can use a formula based on the INDEX, COUNTA, and ROW functions. In the example shown, the formula in D5, copied down, is:
=INDEX(list,...

To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, a new function available in Office 365 and Excel 2019. In the example shown, the formula in C5...

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