How do I get a reference to the last occurrence of a value in an array in Excel?

Question

So if

A1:A5 = {3, 3, 4, 4, 5}

what function could I put into B1 to get a reference to A4 (the cell containing the last occurrence of 4)?

Answer 1

I'm assuming that A1:A5 are sorted in ascending order. If that is the case, you can use the following formula:

=ADDRESS(ROW(A1)-1+MATCH(4,A1:A5,1),COLUMN(A1))

This will give an output of $A$4.

Here's how it works:

MATCH(4,A1:A5,1) finds the index of the largest value that is <= 4, assuming that A1:A5 is sorted in ascending order. What this really means is that it finds the first value greater than 4, and simply returns the index before that index.

ADDRESS(row,col) converts a row number and a column number into a cell reference. For the column number, I simply used the column of the list: COLUMN(A1). For the row number, I used the index returned from the MATCH function as an offset from the beginning of the list (ROW(A1)-1). You could omit ROW(A1)-1 and it would still work in this case, but it would fail as soon as your list started somewhere other than row 1.

Note that to use this reference value somewhere else, you will need to use: INDIRECT(B1).

Answer 2

You could use an array formula to find the MAX of the rows

{=INDEX(A1:A5,MAX((A1:A5=4)*(ROW(A1:A5))),1)}

Enter with control+shift+enter. The MAX portion will return the largest row number of all cells that contain a '4'. That may be all you need, but I've wrapped that in an INDEX function that points to the cell in case you need that extra step.

Answer 3

There's an explanation here (search for "F94"), but be warned, it's not pretty!

Answer 4

This will handle it:

=INDIRECT(ADDRESS(1, INDEX(A1:A5,1, SUMPRODUCT(MAX((A1:A5=A4)*COLUMN(A1:A5))))))

For more info on the SUMPRODUCT function, look here.