I am using the following Lookup formula in a Google Sheet: =LOOKUP(F2, AreaCodes)

I need that formula to be triggered when new rows are added via a Google Form.

I'm guessing the solution has something to do with ArrayFormula but when I try using =ArrayFormula(LOOKUP(F2, AreaCodes)) the values are not retrieved as new rows are added.

  • Welcome. Please add a brief description of your search/research efforts as is suggested in How to Ask and more details to be able to reproduce the problem (i.e describe what is AreaCodes). Also it could be a good idea to add a link to a demo spreadsheet shared with anyone with the link to view only. Commented Mar 20, 2020 at 18:27

1 Answer 1


You are using a formula =LOOKUP(F2, AreaCodes) and you want this to be updated as each Form Response is received.

You are using a named range (AreaCodes) as the search_result_array. When the range was created there were two options for establishing the range:

1 - Assign a starting row and an ending row (a fixed range). For example, the range might be "A2:B10". In this case, any new rows created by Form responses will fall outside the Named Range and will not be taken into account for the value of the formula.

2 - Assign a starting row, but do not assign an ending row (a pseudo-dynamic range). When this happens, the last row is assigned as the ending row. So, for example, the range might become "A2:B999".In this case, any new rows will fall within the named range and will influence the result of the formula.

Diagnose and fix your problem

If new Form Responses are not being updated by your existing formula, it means that the range applying to the Named Range is fixed.

To fix the problem: edit the Named Range, do not enter a closing row number and let the system automatically assign the last row as the ending number.

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.