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)?
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)? 

migrated from stackoverflow.com Nov 3 '10 at 12:15This question came from our site for professional and enthusiast programmers. 


I'm assuming that A1:A5 are sorted in ascending order. If that is the case, you can use the following formula:
This will give an output of $A$4. Here's how it works:
Note that to use this reference value somewhere else, you will need to use: 


You could use an array formula to find the MAX of the rows
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. 


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. 


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

