Search code examples
google-sheetsgoogle-sheets-formulavlookup

pivot returning blank instead of 0 google sheet

I have a spreadsheet where I have an importrange and vlookup to another file where its looking up to a pivot table. Some data is blank in the pivot table and when I lookup in the formula, I have a result of blank even though I have set it to return to 0 by iferror.

Here's my formula:

=iferror(VLOOKUP(A5,importrange("12PaJfEC7Q7gOcCx2zlMHG3YybQuk1TSsNjZDw26qFRg","Converted Pivot!A:E"),3,false),0)

Solution

  • You may try:

    =let(Σ,ifna(vlookup(A5,importrange("12PaJfEC7Q7gOcCx2zlMHG3YybQuk1TSsNjZDw26qFRg","Converted Pivot!A:E"),3,),"no_match_found"),
       if(Σ="",0,Σ))
    • blank_value will now be shown as 0 & a non-match output error will be prompted with no_match_found