Finding the first and last entry in a column

[Guest]

In a column (array $B$7:$B$54) I have entered all numbers 1 from B12:B24. I
would like a formula which recognises the first entry (on B12) and returns a
value from A12.

Also I would like a formula which recognises the last entry of the entries
(onB24) and returns the value from A24.

 

[Bob Phillips]

=INDEX(A7:A54,MIN(IF(B7:B54<>"",ROW(B7:B54)))-MIN(ROW(B7:B54))+1)

=INDEX(A7:A54,MAX(IF(B7:B54<>"",ROW(B7:B54)))-MIN(ROW(B7:B54))+1)

both are array formulae, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[T. Valko]

In a column (array $B$7:$B$54) I have entered all numbers 1 from B12:B24.

If I understand:

For the first entry:

=INDEX(A7:A54,MATCH(1,B7:B54,0))

For the last entry:

=INDEX(A7:A54,MATCH(2,B7:B54))

Or:

=INDEX(A7:A54,MATCH(10^10,B7:B54))

Biff