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:
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.
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.
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).
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.
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.
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!
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.
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.
@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!