Return address of first item in a range

  • Thread starter Thread starter Ashley
  • Start date Start date
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
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the first & subsequent cells to contain text in a column & re 28
a twist to relative ranges 5
check for FALSE values in a range of cells in VBA 2
Locating the max value AND performing a fn on it only 2
VLOOKUP returning #N/A result 2
Find the first cell to contain text in a column 7
return cell address of longest text string in a range 2
VBA set thick borders around non empty cells in range 0

Back
Top