If this then that, but if not that then this

[MattTom]

[TABLE="width: 200"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Month
[/TD]
[TD]Data
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Aug
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]July
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]June
[/TD]
[TD]6.12
[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to write a formula that will pull in the most recent data if available. So if C2 is empty, it will return C3, but if C3 is empty it will return C4. Thus, the formula would yield 6.12 in the example above.

Any help would be greatly appreciated!








[TABLE="width: 122"]
<colgroup><col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"><colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"><tbody>[TR]
[TD="width: 96, bgcolor: transparent"][/TD]
[TD="width: 67, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

If the values in column C are numbers formatted as dates then try this:
=MAX(C2:C100)

which returns the latest date in that range

 

[Scott R]

=INDEX(B1:B20, MIN(IF(B1:B20<>0, ROW(B1:B20))))
entered as an array formula with Ctrl+Shift+Enter

Edit:
=INDEX(B1:B20, MIN(IF(ISNUMBER(B1:B20), ROW(B1:B20))))
might be better if you have zero values

 

[MattTom]

=INDEX(B1:B20, MIN(IF(B1:B20<>0, ROW(B1:B20))))
entered as an array formula with Ctrl+Shift+Enter

Edit:
=INDEX(B1:B20, MIN(IF(ISNUMBER(B1:B20), ROW(B1:B20))))
might be better if you have zero values

Hi Scott. Thanks for the reply!

In your formula you're referencing the date column. Is it possible to only reference the data column (C) to pull in the contents of the first non-blank cell? FYI there won't be zero values.

Thanks!

 

[Scott R]

Just substitute C's for the B's in my formula.

 

[MattTom]

Just substitute C's for the B's in my formula.

I swapped the C's for B's, but I'm getting #REF ! (entered via ctrl/alt/enter). Keep in mind that my data series goes back 10+ years (instead of just the 3 months in my example).

Also, I have multiple sets of data. Some series may have a June value, while others may have a July or August value. My goal is to pull the most recent data point for each series.