CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Edyphant
Edyphant

Reputation: 175

Google Sheet Issue in If function with array formula

Am facing issue in google sheet "if" function as discussed below:

In Cell B4 of the Google Sheets (link is given below) am using below formula and then by draging the formula am getting correct result :

=if(isblank(C4) , B3 , C4 )

But when I use the same formula with ARRAYFORMULA am getting the error. The ARRAYFORMULA is applied in cell A4:

=ARRAYFORMULA(if(isblank(C4:C) , A3:A , C4:C ))

Help on how to fix the error with ARRAYFORMULA in above case would be greatly appreciated.

Below is the link of the sheet:

https://docs.google.com/spreadsheets/d/1fB31msHFWDVv9eweb2H0XJ6zDfpKjxEZG9bpXb36_44/edit#gid=0

Upvotes: 0

Views: 900

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

Limit the number of rows and apply this formula in third row wherever you need This is explaned here https://infoinspired.com/google-docs/spreadsheet/fill-blank-cells-with-the-values-above/ The reason is that when lookup find an error in the key, it takes the number immediatly lower. It's why we divide the row by 0 when the cell is empty.

=ArrayFormula(lookup(row(C3:C),row(C3:C)/if(C3:C<>"",1,0),C3:C))

Upvotes: 2

Swen
Swen

Reputation: 587

To make it work go to
"File" --> "Spreadsheet settings" --> "Calculation" --> "Iterative calculation"

There you press On and nothing else.
It now works.

Good read: Choose how often formulas calculate

Upvotes: 0

Related Questions