title: Handling and modifying the exception returned when a Google Sheets =query() returns #N/A
body: <p>I have a query that runs on Google Sheets against a table where the user has separate input that can modify values in logic checks to a TRUE FALSE result on that table. It is possible that the user has a selection that returns all FALSE for a where statement within the query.</p>
  
  <p>The output I get is #N/A, I want to be able to create an exception where I can identify that it's returning #N/A, and change the output to blank or "No results found".</p>
  
  <p>My users are not very tech savvy, so I need it to be legible to someone without coding knowledge or experience.</p>
  
  <p>Normally when I'm returned an error from a function in Google Sheets, or Excel for that matter, I use the <code>=Iferror()</code> function to control the output of an error exception. This does not seem to work, either because the function iferror was made with a scope that falls outside of =query, or because of some other behind the scenes structural problem.</p>
  
  <pre><code>FALSE,  PIG,    $5.50
  
  FALSE,  APPLE,  $0.50
  
  FALSE,  CAR,    $25.00
  
  FALSE,  HORSE,  $20.00
  </code></pre>
  
  <p>I thought then maybe I could add an <code>if()</code> statement, and put the query in a logic statement checking for the value "N/A":</p>
  
  <pre><code>if(query(A:B,"select B where A =TRUE")="#N/A","No results found",(A:B,"select B where A = TRUE"))
  </code></pre>
  
  <p>This did not work. The value that was returned was "#N/A" still. So I thought maybe the value type of the exception wasn't technically a string value, so it wouldn't accept the value of a non-string #N/A to equal "#N/A", so I wrapped the first query in a <code>concatenate()</code> in an attempt to force it into a singular variable/cell value. I know this works in turning the query it runs into a singular string if it has results, but doing this:</p>
  
  <pre><code>if(concatenate(query(A:B,"select B where A =TRUE"))="#N/A","No results found",(A:B,"select B where A = TRUE"))
  </code></pre>
  
  <p>This still returns "#N/A"</p>
  
  <p>I thought maybe the actual value it's throwing back is a NULL type value, and it has some clever behind the scenes shenanigans to make it display "#N/A" when the where function filters out all results. so I changed the parameter it's checking against in the if statement from "#N/A" to "" as follows:</p>
  
  <pre><code>=if(concatenate(query(lookup!Z2:AN,"select Z where AN = TRUE"))="","No results found",query(lookup!Z2:AN,"select Z where AN = TRUE"))
  </code></pre>
  
  <p>This still returns "#N/A"</p>
  
  <p>I thought maybe the <code>isblank()</code> function would have some mechanism in it to find that it's blank, changing it to:</p>
  
  <pre><code>=if(ISBLANK(concatenate(query(lookup!Z2:AN,"select Z where AN = TRUE"))),"No results found",query(lookup!Z2:AN,"select Z where AN = TRUE"))
  </code></pre>
  
  <p>This also still returns "#N/A"</p>
  
  <p>I'm at a loss. I'm expecting it to throw me the exception I'm qualifying: "No results found" but this never comes. Obviously, I don't understand the nature of how the <code>=query()</code> function is handling data on the back end of things. It must be doing something on a separate layer of operations or something, or have a totally different scope, but how am I supposed to figure out what that variable/process is?</p>