Search code examples
google-sheetsformattinggoogle-sheets-formulaformulagoogle-query-language

How to have this formatted data return empty given this is VLOOKUP + TEXT + QUERY in Google Sheets?


This is the formula, currently. It outputs 12/30/1899, but it actually returns 0 and since TEXT() is forcing it into a data, this is how it shows. How to have this ARRAYFORMULA() populate the values and empty when there is no date?

Formula:

=IF(I4<>"","Factura (USD)",
  ({"Wire Transfer Date";
        arrayformula(IFNA(
            TEXT(
            VLOOKUP($J$10:$J900,
              query(Datasets!S3:U,"select S, U"),2,0),
            "mm/dd/yyyy")
         )
       )
    }
  )
)

Current output, which should be empty instead:

enter image description here


Solution

  • try:

    =IF(I4<>"", "Factura (USD)", {"Wire Transfer Date"; 
     ARRAYFORMULA(SUBSTITUTE(IFNA(TEXT(VLOOKUP($J$10:$J900, 
     QUERY(Datasets!S3:U, "select S,U"), 2, ), "mm/dd/yyyy")), "12/30/1899", ))})