Replace #N/A with next Valid Data

[Amit Gandhi]

Hi Experts

I have excel data in column B. In some cells there is data #N/A

--> I want to Replace #N/A , with data in next cell.
--> If #N/A is in more than 1 cells (continuous) , then Replace them with next valid data.
--> If there is no Valid data in any next cell, then Replace them with Blank.

I have attached excel file which shows sample data and result required for better understanding.

Please help me here.

Regards

Amit

 

[pecoflyer]

Could you please add the formula that returns the N/A ( if any) in col B ?

 

[Khalid NGO]

Hi Amit,

Please check this {array formula}

=IFERROR(INDEX($B$4:$B$35,SMALL(IF(ISNUMBER($B$4:$B$35),ROW($B$4:$B$35)-MIN(ROW($B$4:$B$35)-1)),IF(ISERROR(B4),COUNTIF($B$4:$B4,">0")+1,COUNTIF($B$4:$B4,">0")))),"")

{array formula needs to be entered with Ctrl+Shift+Enter}

Regards,

 

[bosco_yip]

Or this normal formula……………….

=IF(ISNA(B4),IFERROR(1/(1/INDEX(B5:B$36,MATCH(1,INDEX(1-ISNA(B5:B$36),0),0))),""),B4)

Regards
Bosco

 

[Khalid NGO]

Nice Formula Bosco.

 

[Amit Gandhi]

Hi Amit,

Please check this {array formula}

=IFERROR(INDEX($B$4:$B$35,SMALL(IF(ISNUMBER($B$4:$B$35),ROW($B$4:$B$35)-MIN(ROW($B$4:$B$35)-1)),IF(ISERROR(B4),COUNTIF($B$4:$B4,">0")+1,COUNTIF($B$4:$B4,">0")))),"")

{array formula needs to be entered with Ctrl+Shift+Enter}

Regards,

Thanks Khalid, its working.

 

[Amit Gandhi]

Or this normal formula……………….

=IF(ISNA(B4),IFERROR(1/(1/INDEX(B5:B$36,MATCH(1,INDEX(1-ISNA(B5:B$36),0),0))),""),B4)

Regards
Bosco

Thanks Bosco, its working.

 

[Amit Gandhi]

Could you please add the formula that returns the N/A ( if any) in col B ?

Thanks Pecoflyer, its solved now.