One formula to look at a number of rows in column C and return the last entry

[suprsnipes]

Hi

I would like to use a formula to find the following;

Starting from cell c10 and ending at cell c100, I want to find where cell c10 is not equal to "" and the cell directly below in this case c11 is equal to "", if so I want to return the contents of cell c10. I want to repeat this process down to cell c100 only if the last entry and the cell below it is equal to "". The last entry could be cell c99 and cell c100 is equal to "" then I would like to return the contents of c99.

Regards,
suprsnipes

 

[jbeaucaire]

Something like:

=LOOKUP(2, 1/(C10:C100<>""), C10:C100)

Excel Workbook
	B	C	D	E	F	G	H
3
4		5				17
5		10
6		15
7		20
8		25
9		30
10		35
11		40
12		17
13
Sheet1

 

[Aladin Akyurek]

Hi

I would like to use a formula to find the following;

Starting from cell c10 and ending at cell c100, I want to find where cell c10 is not equal to "" and the cell directly below in this case c11 is equal to "", if so I want to return the contents of cell c10. I want to repeat this process down to cell c100 only if the last entry and the cell below it is equal to "". The last entry could be cell c99 and cell c100 is equal to "" then I would like to return the contents of c99.

Regards,
suprsnipes

If C10:C100 is numeric, the last numeric value can be retrieved with:

=LOOKUP(9.99999999999999E+307,C10:C100)

If C10:C100 is text, the last text value that is not a blank (i.e., "") can be retrieved with:

=LOOKUP(9.99999999999999E+307,SEARCH("?",C10:C100),C10:C100)

Any non-blank non-error value, again with:

=LOOKUP(9.99999999999999E+307,1/(1-(C10:C100="")),C10:C100)

The 2nd would actually do the same as the third.

 

[suprsnipes]

Perfect, thanks very much

 

[T. Valko]

Hi

I would like to use a formula to find the following;

Starting from cell c10 and ending at cell c100, I want to find where cell c10 is not equal to "" and the cell directly below in this case c11 is equal to "", if so I want to return the contents of cell c10. I want to repeat this process down to cell c100 only if the last entry and the cell below it is equal to "". The last entry could be cell c99 and cell c100 is equal to "" then I would like to return the contents of c99.

Regards,
suprsnipes

There are several ways to do this. The best solution depends on what type of data is in the range.

Is the data in the range:

text
numbers
dates
times
logical values like TRUE and/or FALSE

Are there any formulas in the range?

Do any of these formulas return formula blanks (="")?

Do any of these formulas return errors?

Could the data in the range be all of the above?

 

[Aladin Akyurek]

Perfect, thanks very much

You are welcome. Thanks for providing us feedback.

Here are some useful links on the matter:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)