Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Nov 3 '10 at 12:15

This 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:

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .