Return address of first item in a range

A

Ashley

I'm sure there must be a simple solution for this I cannot
think of.

I am looking for a formula that will return the value of
the first item in an input range. For example, I want to
put a formula in cell A10 that will return the first item
that is input in the range Range A1:A9 (not nesessarily
the first cell). If A4=200, A7=150 and all other cells are
empty, the formula should return 200 (the first item in
the column).

Thanks for your assistance.

Ashley
 
B

Bernie Deitrick

Ashley,

One way is to use the array formula (entered with Ctrl-Shift-Enter):

=INDIRECT("A"&MIN(IF(A1:A9<>"",ROW(A1:A9))))

This will return a #REF error is A1:A9 are all blank. To avoid that, you
can use the array formula

=IF(ISERROR(INDIRECT("A"&MIN(IF(A1:A9<>"",ROW(A1:A9))))),"All
Blanks",INDIRECT("A"&MIN(IF(A1:A9<>"",ROW(A1:A9)))))

HTH,
Bernie
MS Excel MVP
 
F

Frank Kabel

Hi
as alternatgive to Bernie's solution. Also an array formula:
=INDEX(A1:A9,MATCH(TRUE,A1:A9<>"",0))

Benefit: This is not a volatile formula. As with Bernie's solution to
prevent errors use:
=IF(ISNA(MATCH(TRUE,A1:A9<>"",0))),"",INDEX(A1:A9,MATCH(TRUE,A1:A9<>"",
0)))
also entered as array formula
 
Top