How to use arrayformula with formulas that do not seem to support arrayformulas?

Question

If an array is provided as an input to a normally scalar argument of some functions like:

• INDEX
• GCD
• GOOGLETRANSLATE
• OFFSET
• QUERY
• IMPORTRANGE
• AND

It doesn't return an array (even if wrapped by ARRAYFORMULA); it only gets the value for the first item in the array. Take this example,

Key	Value
1	A
2	B
3	C
4	D
5	E
6	F
7	G

I want to get row 1 and row 5's Value. I tried

=INDEX(A2:B16,{1;5},2)

Syntax for INDEX is INDEX(array, row,column). When a array is provided as a row argument, It only returns the value for first item, 1 instead of returning the value for {1;5}.

Actual output	Expected output
A	A
	E

How to fix?

Answer 1

To achieve the result you're looking for, you can use BYROW to supply the argument once per array:

=BYROW({1;5},LAMBDA(row,INDEX(A2:B16,row,2)))

BYROW sends the array argument provided once per row to the function inside LAMBDA. As a general formula,

=BYROW(range, LAMBDA(row, your_formula(row)))

If you want to send two arguments, use MAP instead.

=MAP({1;5},{1;2},LAMBDA(arr_1,arr_2,INDEX(A2:B16,arr_1,arr_2)))

This will get row 1, column 1 and row 5, column 2 respectively.

Actual output
1
E

MAP supports unlimited number of arguments and therefore can be used with complex formulas.

Caveat: Only one value per function can be returned. If you want to return more values, use SPLIT/TRANSPOSE/SPLIT technique mentioned here