ARRAYFORMULA itself doesn't exist in Excel, but they did recently add support for dynamic/spilled arrays[1]. Which brings it close to (if not at) parity with ARRAYFORMULA.
It's not even close to as handy as QUERY (in ease of use or intuitiveness), but they also recently introduced FILTER[2]. Which can be used in combination with other functions to replicate a subset of QUERY use cases.
My pet list of functions that have me reaching for Sheets are the regex ones[3][4][5]. Excel's search and replace functions support basic wildcards[6], but anything more sophisticated than that quickly becomes infeasible, impractical, or impossible to replicate using Excel function built-ins and requires dropping writing into Javascript or VBA to write up a custom function.
That said, Excel has had a pretty nifty and powerful ETL tool hidden inside of it for the past ~7 years called Power Query[7]. This is essentially an entire app-within-an-app (and is a data processing engine in many Microsoft data products), complete with it's own programming language[8] and compressed columnar data store format. For anyone with a development background, you can easily ramp up on Power Query's language and do 90% of the work you need to do there, and obviate the need for complicated and fragile formulas to begin with.
It's not even close to as handy as QUERY (in ease of use or intuitiveness), but they also recently introduced FILTER[2]. Which can be used in combination with other functions to replicate a subset of QUERY use cases.
My pet list of functions that have me reaching for Sheets are the regex ones[3][4][5]. Excel's search and replace functions support basic wildcards[6], but anything more sophisticated than that quickly becomes infeasible, impractical, or impossible to replicate using Excel function built-ins and requires dropping writing into Javascript or VBA to write up a custom function.
That said, Excel has had a pretty nifty and powerful ETL tool hidden inside of it for the past ~7 years called Power Query[7]. This is essentially an entire app-within-an-app (and is a data processing engine in many Microsoft data products), complete with it's own programming language[8] and compressed columnar data store format. For anyone with a development background, you can easily ramp up on Power Query's language and do 90% of the work you need to do there, and obviate the need for complicated and fragile formulas to begin with.
[1] https://support.microsoft.com/en-us/office/guidelines-and-ex...
[2] https://support.microsoft.com/en-us/office/filter-function-f...
[3] https://support.google.com/docs/answer/3098244
[4] https://support.google.com/docs/answer/3098292
[5] https://support.google.com/docs/answer/3098245
[6] https://support.microsoft.com/en-ie/office/using-wildcard-ch...
[7] https://support.microsoft.com/en-us/office/getting-started-w...
[8] https://docs.microsoft.com/en-us/powerquery-m/