# Looking for an ARRAYFORMULA **URL:** \ **Category:** Ask for Help\ **Created:** [June 4, 2020, 7:56am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270 "2020-06-04T07:56:45Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Clement\_YEROCHEWSKI](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_yerochewski/32/8500_2.png) [@Clement\_YEROCHEWSKI](https://community.glideapps.com/u/Clement_YEROCHEWSKI)\ **Post date:** [June 4, 2020, 7:56am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/1 "2020-06-04T07:56:45Z")
I have spent the past day looking for the correct arrayformula for a formula which works when copying it in the cell, but I would like to have an arrayformula instead. this is my formula I copy in each cell where I want the find the value: ```auto =IFERROR(INDEX(SORTN(FILTER(Cart!A$2:C, Cart!B$2:B > C3, Cart!A$2:A = A3), 1, 0, 2, TRUE), 3), "") ``` what I am trying to achieve is to the card\_id should be populated with `string from Order:A matches a string in Cart:A``&&``row with date from SORTED(Cart:B, byDate, TRUE) later than date from Order:C` I have made a sample with information and with the expected result using my formula. > **[TestOwner](https://docs.google.com/spreadsheets/d/1dI_9xOLCQiaXHfWUZHqHsoEdt0KO1M60zBI-vk6unYY/edit?usp=sharing)** > > order > > owner,Product,date,quantity,cart\_id > owner\_1,product\_1,6/3/2020 19:32:42,5,cart\_1 > owner\_1,product\_1,6/3/2020 20:32:42,5,cart\_3 > owner\_1,product\_1,6/3/2020 21:32:42,5,no card yet > owner\_1,product\_1,6/3/2020 23:32:42,5,no card... If you could help me on finding the properly ARRAYFORMULA, I would really appreciate! it’s killing me. Thanks! ps : let me know if you need access to the google spreadsheet. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 4, 2020, 8:28am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/2 "2020-06-04T08:28:41Z")
Please give access to [ariesarsenal@gmail.com](mailto:ariesarsenal@gmail.com). I will have a look. ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [June 4, 2020, 8:29am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/3 "2020-06-04T08:29:17Z")
Hello there @Clement_YEROCHEWSKI To be confirmed by spreadsheets buffs in this forum, but filter() already uses arrays by construction. I believe you will therefore have trouble combining filter() and arrayformula() to extend your formula across an array (in your case down a column). ---
**Author:** ![Clement\_YEROCHEWSKI](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_yerochewski/32/8500_2.png) [@Clement\_YEROCHEWSKI](https://community.glideapps.com/u/Clement_YEROCHEWSKI)\ **Post date:** [June 4, 2020, 8:55am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/4 "2020-06-04T08:55:10Z")
hey there, I have given you access, really appreciate. ---
**Author:** ![Clement\_YEROCHEWSKI](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_yerochewski/32/8500_2.png) [@Clement\_YEROCHEWSKI](https://community.glideapps.com/u/Clement_YEROCHEWSKI)\ **Post date:** [June 4, 2020, 8:57am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/5 "2020-06-04T08:57:31Z")
Hey there, I have been scratching my head on how to achieve it differently, but how can I return a range using anything else than filter(), I started playing around with lookup() but nah, nothing came out. thanks for helping. ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [June 4, 2020, 9:14am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/6 "2020-06-04T09:14:24Z")
If you want to take an entirely different route: Have you considered setting up an Apps Script to achieve the same result as an arrayformula() being pulled down a column? With the script, when a new row is created, your script would copy the arrayformula() from the row above. You wouldn’t need to write the script yourself. You should be able to find it on this forum or by googling it. ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [June 4, 2020, 9:17am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/7 "2020-06-04T09:17:08Z")
Here is a post written by @ThinhDinh [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) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 4, 2020, 9:18am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/8 "2020-06-04T09:18:14Z")
Yeah Clement can apply that script to just copy it down if his formula is already correct, but I’m trying to use an Arrayformula if it works. Thanks for the shout out! ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 4, 2020, 9:41am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/9 "2020-06-04T09:41:20Z")
After a while @Clement_YEROCHEWSKI I think this would be very hard to achieve with an ARRAYFORMULA setup, so I would recommend you to setup a script that triggers the copy down. I have written about it in the post Nathanael linked. If you need help about that feel free to message me. ---
**Author:** ![Spellytics](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/spellytics/32/2770_2.png) [@Spellytics](https://community.glideapps.com/u/Spellytics)\ **Post date:** [June 4, 2020, 11:02am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/11 "2020-06-04T11:02:09Z")
Hi Clement, I don’t know if I’m following this correctly. Are you trying to match the cart content with the owner based on the timestamp? Can you tell me a bit more on what you’re trying to do? i.e. what the formula is trying to achieve Maybe there’s an alternative. ---
**Author:** ![Clement\_YEROCHEWSKI](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_yerochewski/32/8500_2.png) [@Clement\_YEROCHEWSKI](https://community.glideapps.com/u/Clement_YEROCHEWSKI)\ **Post date:** [June 4, 2020, 11:09am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/12 "2020-06-04T11:09:44Z")
Hi, I am trying to find the cart which matches has the same email has my product AND which has a timestamp later than my product date, as we might find multiple carts, I need to sort result by cart.date and take the closest one to product date. ---
**Author:** ![Clement\_YEROCHEWSKI](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/clement_yerochewski/32/8500_2.png) [@Clement\_YEROCHEWSKI](https://community.glideapps.com/u/Clement_YEROCHEWSKI)\ **Post date:** [June 4, 2020, 11:11am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/13 "2020-06-04T11:11:47Z")
@ThinhDinh and @nathanaelb, thanks a lot guys, I am now using my formula within the script you have shared, it works like a charm and this is exactly what I needed, I mean the result is what I needed, so I am really happy, I might play around with script now 🙂 love that feature! thanks a bunch! ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 4, 2020, 11:12am UTC](https://community.glideapps.com/t/looking-for-an-arrayformula/10270/14 "2020-06-04T11:12:28Z")
If you need our help regarding formulas and scripts feel free to reach out!