Finding cell value of first non-blank cell in a range

[G00DNESS]

Using Excel 2003, in Cell CZ32, I am trying to use a formula that will return the value of the first non blank cell in CM32:CY32. CM32:CY32 contain formulas. I will need to use this formula separately for rows 32--58. I'm using the following, but if the cells within the range are all blank it returns errors.

1.
=(INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)))
(and CTRL+SHIFT+ENTER)
Returns: Error #N/A

2.
=IFERROR(INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)),"")
(and CTRL+SHIFT+ENTER)
Returns: Error #NAME?

Any help with this is much appreciated.

 

[JoeMo]

IFERROR was introduced w/Excel2007 - so Excel 2003 doesn't recognize the name of that function. Try:

=IF(ISERROR(INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0))),"",INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)))

 

[G00DNESS]

IFERROR was introduced w/Excel2007 - so Excel 2003 doesn't recognize the name of that function. Try:

=IF(ISERROR(INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0))),"",INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)))

Thank you so much Joe! It worked quite well.

 

[JoeMo]

Thank you so much Joe! It worked quite well.

You are welcome- thanks for the reply.

 

[Aladin Akyurek]

Also, it might be of interest:

If a text-valued range:

=IF(COUNTIF(CM32:CY32,"?*"),INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)),"")

If a numeric range:

=IF(COUNT(CM32:CY32),INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)),"")

Otherwise:

=IF(COUNTIF(CM32:CY32,"?*")+COUNT(CM32:CY32),
  INDEX(CM32:CY32,MATCH(TRUE,CM32:CY32<>"",0)),"")