CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgs-vlookup
Ryan Steinke
Ryan Steinke

Reputation: 11

Google Sheets Vlookup not working when Apps Script adds a line to range

I've got a basic vlookup set up in a spreadsheet which is updated by a form. It is pulling data from a page that is updated by Apps script. My vlookup looks like this

=VLOOKUP($B$3,'Checklist Log'!A:AZ,2,false)

The 'checklist log' tab has column headers along row 1 and dates down column A which are populated by

=SORT(Sheet1!A2:A,1,FALSE)

from sheet1 where the form submits to.

Whenever a form is submitted my Apps script adds a row under row 1 and then copies the cells from the checklist tab and then clears the checklist tab.

I think a possible solution would be to make my apps script insert new cells from b2:AZ2 instead of the whole line but i can't seem to find code to do that. So how can I fix that?

Upvotes: 1

Views: 962

Answers (1)

DME
DME

Reputation: 11

Reference format: VLOOKUP(search_key, range, index)

Failing example: VLOOKUP(C5,G16:H18,2)

Passing example: VLOOKUP(C5,G16:H19,2)

I had a similar error when I attempted to lookup a value in the last row of the range (i.e., row 18). Based on other peoples' suggestions, I tried changing the formats of the values in the range cells, but to no avail. I then increased the range to include one additional row (i.e., range = G16:H19), and everything worked fine.

Upvotes: 1

Related Questions