Return first value in range from Row greater than current Cell

[rjhh2013]

Hi,

I have data exported which is not Excel friendly. I am trying to Populate a column (P) with the values from Column O. Unfortunately column O is full of blanks cells and the spaces between values ranges anywher between 3 and 15 blank cells.

I am looking for a formula that would do this: return first value from O:O with greater row number than current cell.

This is my last atempt - =IF(B8="","",INDEX(LuNam,MATCH(CELL("address"),LuNam,-1)))

LuNam=O:O

Please Help

 

[Peter_SSs]

This appears to fit your description. If it is not what you want, please give more details.

Formula in P2 is copied down.

Excel Workbook
	O	P
1
2	a	b
3	b	c
4		c
5		c
6	c	d
7		d
8	d	e
9	e
10
List

 

[rjhh2013]

That worked perfectly! Thanks for the help.

 

[rjhh2013]

This appears to fit your description. If it is not what you want, please give more details.

Formula in P2 is copied down.

List

	O	P

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 56px;"><col style="width: 56px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: center"]c[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"]c[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: center"]d[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]e[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]e[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula P2 =IFERROR(INDEX(O3:O$1000,MATCH(TRUE,INDEX(O3:O$1000<>"",0),0)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter_SSs,

Just looking through the formula so that I understand it, having the range move down with the cell is a simple and brilliant solution that hadn't occured to me.

A quick question about the "Match" part of the formula, Am I right in thinking that "TRUE" is telling excel to display the first value which fits the argument in INDEX?

Cheers,

Rob

 

[Peter_SSs]

That worked perfectly! Thanks for the help.

Great news. Thanks for letting us know.

 

[Peter_SSs]

A quick question about the "Match" part of the formula, Am I right in thinking that "TRUE" is telling excel to display the first value which fits the argument in INDEX?

O3:O$1000<>""
This returns a series of TRUE/FALSE values depending on whether each cell contains data.

INDEX(O3:O$1000<>"",0)
Returns all those TRUE/FALSE values as an array

MATCH(TRUE,INDEX(O3:O$1000<>"",0),0)
Matches the first TRUE value in that array, giving the (relative) row of the first non-blank cell.