The spreadsheet we are attempting to construct using Google Sheets takes form responses across multiple weeks (which come from the same form) and checks to see if that form has been completed for that specific week. The current formula looks something like this:

=IFERROR(If(AND(VLOOKUP(CONCATENATE(C3,"A"),'Form Responses 1'!D:D,1,False)=CONCATENATE(C3,"A"),VLOOKUP(CONCATENATE(C3,"A"),'Form Responses 1'!D:G,4,False)<>"Concerning"),"Done","Not Turned In"),"Not Turned In")

Which checks if the data is there or not. The problem with this, however, is that the formulas don't copy over, and the process is not as automated as it should be. The form creates a new row in the middle and forces us to copy everything down, making the automation pointless.

Please provide pointers on how to change this into a query function so that it will copy over.

  • Welcome to Web Applications Stack Exchange. Please take the tour to learn the ropes of this site. P.D. We had very similar questions with answers. I might add links / "mark" this question as duplicate later. For an explanation of what this means, please read Why are some questions marked as duplicate? Commented Jan 31 at 0:22
  • What is in cell C3? What do you mean when you say the "formulas don't copy over"? You should edit the question to include some sample data and an example of the expected result so we better understand the problem and can help you. It makes sense to you but we are missing some pieces.
    – Blindspots
    Commented Jan 31 at 1:19
  • I posted an answer that addresses your formula . If you edit your question to include the details requested in my previous comment I can help further.
    – Blindspots
    Commented Jan 31 at 2:37
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Jan 31 at 2:53

1 Answer 1


I have some recommendations for your formula:

  1. AND function
    1. The first VLOOKUP tries to match and return the search term in order to determine if the returned search term is equivalent to the search term. That is like IF(IF(1=1,1,0)=1,...) since the match was made, there's no need to return the matched value, simply matching it is enough. And if returning it there is no need to test if it matches the search term as it can't be otherwise.
    2. The second VLOOKUP performs the same lookup as the first, checking if the value C3&"A" can be found in Column D of 'Form Responses 1'. This 2nd VLOOKUP now renders the first completely redundant.
    3. So, all other things being equal, the AND function can be replaced by a single condition:
      VLOOKUP(C3&"A",'Form Responses 1'!D:G,4,0)<>"Concerning"
  2. The original formula can be simplified to:
    =IF(IFNA(VLOOKUP(C3&"A", 'Form Responses 1'!D:G,4,0)<>"Concerning"),
        "Done", "Not Turned In")
  3. It seems surprising that you would want to return the same result, "Not Turned In", regardless if the search value C3&"A" is not found or if it is found but "Concerning" is also found. If these cases should be handled differently, the IF function can be placed inside IFNA:
    =IFNA(IF(VLOOKUP(C3&"A", 'Form Responses 1'!D:G,4,0)<>"Concerning",
             "Done", "Not Turned In"), 
          "Search term not found") 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.