Returning the first value in a column of values

**archie999** · 04-05-2007, 10:37 AM

Hi,

I want to return the first value in a column of values

This would be very similar to =INDEX(F11:F18,1) where the list of values is stored in cells F11 to F18.

But I would like it to automatically return the value of the next cell if the first cell is blank. Or even return the value of the third cell if the first two are blank.

i.e.

F11 = blank
F12 = 34
F13 = 65
F14 = 523
F15 = 554
F16 = 5354
F17 = 5333
F18 = 51354

The function would return the value in F12 as F11 is blank. If there is a value in F11 then that would be returned.

Thanks for any help

**starguy** · 04-05-2007, 10:49 AM

Originally Posted by archie999

Hi,

I want to return the first value in a column of values

This would be very similar to =INDEX(F11:F18,1) where the list of values is stored in cells F11 to F18.

But I would like it to automatically return the value of the next cell if the first cell is blank. Or even return the value of the third cell if the first two are blank.

i.e.

F11 = blank
F12 = 34
F13 = 65
F14 = 523
F15 = 554
F16 = 5354
F17 = 5333
F18 = 51354

The function would return the value in F12 as F11 is blank. If there is a value in F11 then that would be returned.

Thanks for any help

this might help

=INDEX(F11:F18,COUNTBLANK(F11:F18)+1)

**BigBas** · 04-05-2007, 10:49 AM

Use this array formula (just use your applicable range)

=INDEX(A1:A7,MIN(IF(A1:A7<>"",ROW(A1:A7))))

Do note, this is an array formula, so you must press CTRL+SHIFT+ENTER

EDIT TO ADD: Starguy's formula would work as well, assuming that there are no blanks after the first number. For example, if you have Blank,blank, 3,4,5,blank,7 that formula would return 4. If this is not the case, I would suggest using his formula.