How to use Lookup formula for new rows in Google Sheets submitted via Google Form?

Question

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.

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.