Google Sheets – Conditional IMPORTRANGE

formulasgoogle sheetsgoogle-sheets-arrayformulaimportrange

In Google Sheets, I'm using IMPORTRANGE to pull in data from to a large sheet.

One field is a dollar amount. I would like to import the dollar amount and replace any zero amount with a specific dollar amount. For example, if the number is $100, I want to import $100. But if the number is $0, I want to import it but replace the $0 with $50.

Best Answer

  • you can use simple IF formula for that, like:

    =IF(IMPORTRANGE("ID", "Sheet1!A1")="$0", "$50", IMPORTRANGE("ID", "Sheet1!A1"))

    =ARRAYFORMULA(IF(IMPORTRANGE("ID", "Sheet1!A1:A")="$0", "$50", 
                 IMPORTRANGE("ID", "Sheet1!A1:A")))

  • to avoid #N/A wrap it into IFERROR

Related Topic