# Array Formula creating more rows than it should **URL:** \ **Category:** Ask for Help\ **Created:** [October 26, 2020, 1:16am UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595 "2020-10-26T01:16:06Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 1:16am UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/1 "2020-10-26T01:16:06Z")
Hi, i created an arrayformula that works, but it creates more rows than it should, pls see this screen. The blank on the left shouldn’t create any rows on the right. ![Screenshot 2020-10-26 at 9.05.49 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/4/4b32697a9475e3a5444e556ba14b35e9d8a8e666.png) TIA. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [October 26, 2020, 1:26am UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/2 "2020-10-26T01:26:51Z")
You need another IF around your VLOOKUP to only put in a value of A1:A \<\> “”. ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 4:25am UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/3 "2020-10-26T04:25:38Z")
Hi Jeff, Thanks but my formula building skills are still noobish. I tried adding them like this: =arrayformula(IF(row(A:A)=1,“Codes Used”, IF(VLOOKUP(A1:A,Usage!A2:A,1,false))) But does not work. Could you point out exactly where do I put it? ---
**Author:** ![Pratik\_Shah](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pratik_shah/32/12416_2.png) [@Pratik\_Shah](https://community.glideapps.com/u/Pratik_Shah)\ **Post date:** [October 26, 2020, 5:02am UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/4 "2020-10-26T05:02:33Z")
What exactly you are trying to achieve? ---
**Author:** ![Marc-Olivier](https://avatars.discourse-cdn.com/v4/letter/m/ac91a4/32.png) [@Marc-Olivier](https://community.glideapps.com/u/Marc-Olivier)\ **Post date:** [October 26, 2020, 7:13am UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/5 "2020-10-26T07:13:29Z")
Just simply use the function = iferror(-; » ») wrapped around your array formula Replace the - by your array formula ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 12:30pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/6 "2020-10-26T12:30:07Z")
> [@Marc-Olivier](#): > > = iferror( Thanks but this problem leads to the the same, minus the N/A, which I use in pivot table elsewhere. ![Screenshot 2020-10-26 at 8.29.24 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/f/fc07a08abcefc68766f9da0da80a73f6e4f27fcd.png) ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 12:31pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/7 "2020-10-26T12:31:46Z")
This is a promo code dispenser. User sign up and get a code sent to their email. The codes are taken from another sheet, pre-randomised. The app also checks for codes that have been used. The purpose of this is to check against promo codes issued against redeemed, to have un-used codes. ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 12:35pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/8 "2020-10-26T12:35:06Z")
While this existing formula works, it generates additional rows which I don’t need. The extras are the ones I wish to have not generated. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [October 26, 2020, 12:40pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/9 "2020-10-26T12:40:50Z")
```auto =ARRAYFORMULA(IF(ROW(A:A)=1,"Codes Used", IF(LEN(A1:A)=0, "", VLOOKUP(A1:A,Usage!A2:A,1,false)))) ``` ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 12:47pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/10 "2020-10-26T12:47:00Z")
> [@Jeff\_Hager](#): > > ```auto > ARRAYFORMULA(IF(ROW(A:A)=1,"Codes Used", IF(LEN(A1:A)=0, "", VLOOKUP(A1:A,Usage!A2:A,1,false)))) > > ``` Hi Jeff, Thanks, the code does work a little better, but still generates blank fields at the bottom. This might be the best workaround as I dont want to hit the row limit of Glide. The last column on the right is using your formula. ![Screenshot 2020-10-26 at 8.45.48 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/4/4f86ace0a06ee08b6fbc54315ce2ac42e1f58ae8.png) ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 12:48pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/11 "2020-10-26T12:48:35Z")
here’s the link if anyone wants to take a look under the hood: > **[Voucher System](https://docs.google.com/spreadsheets/d/110F3EGJ1zpeV-N4dOU-cVI_5KGstJO07DSkKD1-pW5w/edit?usp=sharing)** > > Data > > Codes,Date/Time Created,Name,Email,Expiry Date,Count > a2609,10/25/2020,jun king 1,jk@epnox.com,10/25/2021,15 > a9017,10/25/2020,jessy,jessy@gmail.com,10/25/2021 > a7712,10/25/2020,jimmy,jimmy@gmail.com,10/25/2021 > a2642,10/25/2020,johnny... ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [October 26, 2020, 1:03pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/12 "2020-10-26T13:03:30Z")
When using arrayformulas, you will always have to delete the empty rows. Glide won’t seem them now that they are blank, but if you add new rows through the app, they will be placed at the bottom of the sheet. Here’s a tutorial on it’s use: > [@Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727): > > Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job. > > > **1/ Definition** > > > > Arrayformula is defined as “a range, mathematical expression using one cell range … And just my suggestion, unless you have a specific reason for performing the formula in the sheet…you could easily duplicate this same thing with a Relation and Lookup in the Glide data editor. It would be much faster as you wouldn’t have to wait for formulas to run in the sheet, which causes delays. ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 1:06pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/13 "2020-10-26T13:06:57Z")
i’ll take a read, thanks! ---
**Author:** ![kingzy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kingzy/32/15690_2.png) [@kingzy](https://community.glideapps.com/u/kingzy)\ **Post date:** [October 26, 2020, 1:19pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/14 "2020-10-26T13:19:17Z")
FYI, I just tested with the formula you gave, it doesn’t add new rows at the bottom, instead continues from my last. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [October 26, 2020, 1:33pm UTC](https://community.glideapps.com/t/array-formula-creating-more-rows-than-it-should/17595/15 "2020-10-26T13:33:33Z")
OK. If it works, that’s fine. It’s been a very very common issue for people to say the sheet isn’t syncing with glide only to find out they are using arrayformulas and their new rows are being placed at the bottom of the sheet. This is due to google not wanting to touch existing rows that have a formula being applied to it, although the value may be blank. That’s why it’s suggested to delete empty rows. If you run into it in the future, now you’ll know why.