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

by

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

More Related Contents:

  1. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  2. ArrayFormula and “AND” Formula in Google Sheets
  3. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  4. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  5. Conditional formatting based on another cell’s value
  6. Google Sheets importXML Returns Empty Value
  7. Get the last non-empty cell in a column in Google Sheets
  8. How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?
  9. How would I import YouTube Likes and Dislikes and a ratio from YouTube onto Google Sheets? [closed]
  10. How to SUM duration in Google Sheets?
  11. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  12. Selecting the last value of a column
  13. Difference between two dates expressed as years, months, days (in one column)
  14. Query is ignoring string (non numeric) value
  15. Repeat whole row N times based on column value in Google Sheets
  16. I get Formula parse error with all formulas
  17. IMPORTXML and right XPath from Bloomberg price [duplicate]
  18. What factors determine the memory used in lambda functions?
  19. How to list all permutations without repetition?
  20. Repeat each row N times in Google Sheets
  21. Google Sheets Formula for Extracting Domain From Website?
  22. How to create all possible pair combinations without duplicates in Google Sheets?
  23. Col BY causing parse error in Google sheets query
  24. Count cells that contain any text
  25. How to make a range repeat n-times in Google SpreadSheet
  26. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  27. How to create a runing total formula that expands automatically when new rows are added to my spreadsheet [duplicate]
  28. ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?
  29. No permission to call msgBox in Google Apps Scripting
  30. Periodically refresh IMPORTXML() spreadsheet function

Leave a Comment