# Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Hey Jeff, yeah, I think that’s what I’m trying to do. I was using a VLOOKUP but didn’t understand how it differed from Array but it sounds like that’s what I need. I currently have this entered and it’s grabbing the right thing but isn’t applying to new rows created by new comments. I also deleted all empty rows and columns.

=arrayformula(VLOOKUP(B2, Listings!A:C, 3, FALSE))

Array formulas require the use of row ranges for a column. You are just specifying a single row item. Try this:

``````=arrayformula(VLOOKUP(B2:B, Listings!A:C, 3, FALSE))
``````
3 Likes

Yes! That did it! I know I can like your post but can I give you a badge or something? I’ve been beating my head against the wall on this one! I’m finding the trouble comes when I know enough to figure out what formula or action I need but not enough to know what’s wrong about it when I’m looking at it.

Thank you so much!

3 Likes

I’ve been there. Know enough to be dangerous, or know exactly what you need or want, but not exactly how to do it.

1 Like

Hahaha dangerous is right

1 Like

Are you aware that you could start an arrayformula in the header row using this trick?

`={"YourHeaderHere";ARRAYFORMULA(Sheet1!A2:Z)}`

Note: Don’t forget the } at the end of the formula

4 Likes

Even better!? Don’t know this trick. Wow.

1 Like

What do I have to change in the script to make the formulas of 3 different rows in the same sheet autofill?

What type of formula are you trying to autofill?

I actually think I figured it out! thanks you!

1 Like

Hi there! Wondering if there is a way to insert through the “User input form” multiple options.

i.e Jack is the User & selects multiple fruits to buy.
Thus the data in GSheets should appear as Column A: User’s Name & Column B: List of Fruits.
Within Glide, how can Jack select "Bananas, Oranges, and more from the option set in order to have it populate into GSheets as a list (Apple, Pear, Banana, etc.)

I’ve been searching the forum but I feel I’m doing a newbie thing.

This concept app does exactly that (and more)…

1 Like

Sorry, did not understand how to use arrayformula to count the exact number of True in several columns. I wrote the numbers I expect in red square.

What layout do you expect? Do you want a checklist or do you want something like what Darren did in the link he provided?

Either way, the idea is to use a relation and have a joined list column linking all the items chosen together, then write them to the Sheet using a set column action.

2 Likes

Here’s the formula to do so.

1 Like

Ok I will try that strategy then. What I was expecting is for the User to fill out a form and can select multiple options at once from a list.
i.e. Food Cuisines App
Options: “French, Chinese, American, Peruvian”
User Selects : “French, Peruvian” as their 2 options

Then question is followed by Favorite Areas To Eat
Options: “Houston, Scottsdale, Salem”
User Selects: “Houston, Scottsdale” as their options

User’s Name
Users Food Cuisine
Users Favorite Places to Eat.

Not sure how to do it so that the User isn’t selecting from separate dropdowns each time in order to create a list

Best,