CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
TheMaster
TheMaster

Reputation: 50452

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

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

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?

Upvotes: -3

Views: 778

Answers (1)

TheMaster
TheMaster

Reputation: 50452

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

Upvotes: 1

Related Questions