Switching from Google sheets, need help with arrayformulas or combining range in formula

Hello,
Because of work I need to start using Excel 2016

One thing I was using a lot in Google docs was arrayformula. But I can not figure out how to use it in Excel (or with what formula I should substitute it)
For example in Google sheets this formula randomly picks an item from one range:

=VLOOKUP(RANDBETWEEN(row($A$1),row($A$27)),{arrayformula(row($A$1:$A$27)),$A$1:$A$27},2)

With comments:

=VLOOKUP(
    RANDBETWEEN(row($A$1),row($A$27)), #Picks a random number
    {arrayformula(row($A$1:$A$27)),$A$1:$A$27} #create range in which the first column is number from 1till27 and second is the actual column range with items to return
    ,2)

But I cant even make something like this {=SUM(G15:G24*H15:H24)} to work without using ctrl+shift+enter, If I try to type braces inside formula it gives me error

Your help would be much appreciated!
Juris