7

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

4 Answers 4

5

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

1

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.

1

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.

0

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

You must log in to answer this question.

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