Looking for an ARRAYFORMULA

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:

=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.

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.

Please give access to ariesarsenal@gmail.com. I will have a look.

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).

1 Like

hey there, I have given you access, really appreciate.

1 Like

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.

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.

1 Like

Here is a post written by @ThinhDinh

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

2 Likes

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!

1 Like

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.

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.

1 Like

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.

@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 :slight_smile: love that feature!

thanks a bunch!

1 Like

If you need our help regarding formulas and scripts feel free to reach out!

1 Like