Can’t pass a range to a lambda using Let and Makearray functions

by

The construct of INDEX() >>:<<INDEX() will work when applied to ranges. Not to arrays AFAIK. It will lead to these errors. Maybe try something like:

=LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(range,r,SEQUENCE(c))))))

This would resemble the construct of your inital formula. However, in the linked question you have mentioned that you’d like to use SCAN() in combination with BYROW(). You have noticed that nesting these lambda helper functions will result in an error. I just wanted to show you that it is possible with just a single SCAN() function:

=SCAN(0,A1:D2,LAMBDA(a,b,IF(COLUMN(b)=1,b,a+b)))

Or even less verbose:

=SCAN(0,A1:D2,LAMBDA(a,b,a*(COLUMN(b)<>1)+b))

I have been puzzling a bit and found that we could compare the row/column of the ‘b’ variable inside the lambda structure. Something I haven’t been aware of prior to….now. It does feel a bit glitchy but now we can actually use some sort of variation to the BYROW() helper inside SCAN() and do these calculation for each row of the intial array seperately.

More Related Contents:

  1. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  2. How to convert to percentage [closed]
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Error in finding last used cell in Excel with VBA
  6. Getting unique values in Excel by using formulas only
  7. Vlookup using 2 columns to reference another
  8. Excel: last character/string match in a string
  9. Simple Pivot Table to Count Unique Values
  10. Excel UNIQUE Across Columns
  11. Ignore Duplicates and Create New List of Unique Values in Excel
  12. Find last used cell in Excel VBA
  13. Two column lookup in table array using INDEX and MATCH
  14. Detect if range is empty
  15. If two cells match, return value from third
  16. Excel: Searching for multiple terms in a cell
  17. What does — do in Excel formulas?
  18. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  19. Countif With Multiple OR Criteria
  20. Copy a selected range to another worksheet
  21. CountIf With Filtered Data
  22. Convert from US to UK format
  23. Extract the last substring from a cell
  24. VBA Dialog box to select range in different workbook
  25. excel vlookup with multiple results
  26. How to roundup a number to the closest ten?
  27. Convert an Array Formula’s Text Results into a Usable Format
  28. Unique Rank value for a subgroup within a group
  29. VBA: Modify chart data range
  30. Get content of a cell given the row and column numbers

Leave a Comment