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

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

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

Solution 3:

assuming your array is running off of a match of column values, I would suggest you just reverse the column values so values in column 5 of the array would now be in column 1, and then use min() instead. Getting the first occurrence is always easier than the last.