Counting the latest row of a number?

Hello,

what functions I have to use to solve this problem.
I have a table, with 3 columns (1,2,3). Below that are a lot of numbers (0 and 1).
I want to know the ROW of the latest number 1 in each column. Number 1 can be a duplicate in each column and I only want
to get the row of the latest number one ...

For better understanding I upload this excel document: one.xlsx

P.S. There is an example in the document how the results should be like.

In A24 use this array formula

=MAX((A2:A20=1)*ROW(A2:A20))

Confirm with Ctrl+Shift+Enter and not just Enter

Copy across

=lookup(9^9,1/(A2:A20=1),ROW(A2:A20))

Hi,

another approach could be:

Please Login or Register  to view this content.

Cheers

Hi, thanks for your kind feedback


This segment:

1/(A2:A20)

will return an array of either 1/1 and 1/0.

1/1 = 1

1/0 = #DIV/0!

LOOKUP reads an array of 1s and #DIV/0! errors. 2 is the lookup value. LOOKUP is searching a 2.
The lookup value is greater than any number in the array of 1s and #DIV/0! errors, then the formula will look for (match) the last number in the array that is less than the lookup value (2).

The only and the largest number produced by formula in the array is 1, so every number in the array (1,error,1,1,error) is less than the lookup value of 2 and returns the corresponding result as expected.

LOOKUP ignores and skips error.

Same results if you'd change 2 (the first argument of LOOKUP) with every number greater than 1.


=Lookup(7,1/(A2:A20),Row(A2:A20))

or

=Lookup(3,1/(A2:A20),Row(A2:A20))



Cheers

Edit: here you can find another example and same attempt to explain (in a poor English) =LOOKUP(2,...

http://www.excelforum.com/excel-form...ml#post3865208