Can Lambda functions use text references passed from the ByCol function?

Question

While writing a function that gets and transforms data from an array of text-based references (e.g. {"ref1", "ref2", ...}) I discovered the below problem.

=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(cellRef)))

Returns

FALSE

As far as I can tell, this is the simplest form of the problem where I replaced my actual data calculation with a simple check on the validity of the reference.

I cannot pivot away from iterating over text-based references as the real references are table columns (e.g. table1[A], table2[A], ...) stored in a 1-dimensional array. At first, I tried to replace my structured references with plain A1 notation, but that was only an intermediary step to the above issue—ISREF() within BYCOL(...Lambda(...)) returns FALSE.

I have tried using INDIRECT(), T(), INDEX(), and CELL() in various (perhaps silly) ways that include:

=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDIRECT(cellRef))))
=BYCOL({"A1"}, LAMBDA(cellRef, INDIRECT(ISREF(cellRef))))
=BYCOL({"INDIRECT(A1)"}, LAMBDA(cellRef, ISREF(cellRef)))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDIRECT(T(cellRef)))))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(CELL("address", cellRef))))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDIRECT(CELL("address", cellRef)))))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(CELL("address", cellRef))))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDEX(cellRef, 1))))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDEX(cellRef, 1, 1))))
=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDEX(cellRef, 0))))

But they all evaluate to FALSE. Note that the last few using INDEX() still fail. I was hoping this answer would help.

If there is some unique syntax required to make these functions work together or some logic that prevents you from iterating over references in this way, please let me know.

• Michal: =BYCOL({"A1"}, LAMBDA(cellRef, TYPE(cellRef))) returns the value of 64 which indicates an array which could be your starting point.
• nkalvi: Hey, could you please provide more details? I'm wondering whether we could use structured references in some way. BTW, have you tried =BYCOL(MAP(TEXTSPLIT("A1,B1", ","), LAMBDA(a, INDIRECT(a))), LAMBDA(a, a))?
• VBasic2008: Try =BYCOL({"A1","B1","AAA","D1048576","E1048577"}, LAMBDA(cellRef, ISREF(INDEX(INDIRECT(cellRef),1)))) yielding in {TRUE,TRUE,FALSE,TRUE,FALSE}.
• Façade: @Michal, thank you. When finding these solutions, I will be more methodical and patient going forward. Calling TYPE() never occurred to me but would have made the accepted solution easier to find.

Answer 1

No answer explains what is wrong in your formulas.

Your second formula is almost true, just you need to replace BYCOL with MAP:

=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDIRECT(cellRef))))
-->
=MAP({"A1"}, LAMBDA(cellRef, ISREF(INDIRECT(cellRef))))

The problem is that BYCOL provides the array of values per each source item:

{"A1","B1","AAA","D1048576","E1048577"}
-->
{"A1"}
{"B1"}
{"D1048576"}
{"E1048577"}

thus ISREF(INDIRECT(cellRef)) returns FALSE.

It can be resolved with INDEX which takes an element from the array

=BYCOL({"A1"}, LAMBDA(cellRef, ISREF(INDIRECT(INDEX(cellRef,1)))))

Evidently MAP resolves the problem simpler. It maps the source element to the out element applying the function.

• VBasic2008: Great! It may not be obvious to everyone but simply put, with MAP you don't need INDEX. It's kind of a revelation (at least to me). You explained why BYCOL doesn't work since it "...provides the array of values per each source item". Can you explain why MAP works without INDEX? Or do you have a link where this is addressed in more detail?
• nkalvi: @VBasic2008, explanation by rotabor and your question are helpful - thank you to both. From MS ref for BYCOL: LAMBDA(column) A column from array, while for MAP: ... mapping each value

Answer 2

Check Array For Valid Range References

• You were just an INDIRECT too short in your last three formulas.

Hard-Coded

• You could use...

  =BYCOL({"A1"}, LAMBDA(cellRef,ISREF(INDEX(INDIRECT(cellRef),1))))
  

  ... or more obviously

  =BYCOL({"A1","B1","AAA","D1048576","E1048577"},LAMBDA(cellRef,ISREF(INDEX(INDIRECT(cellRef),1))))
  

  ... yielding in

  {TRUE,TRUE,FALSE,TRUE,FALSE}
  

Dynamic

=A2:A3&"["&B1:G1&"]" 
=MAP(B2:G3,LAMBDA(cellRef,ISREF(INDEX(INDIRECT(cellRef),1)))) 
or:
=MAP(B2#,LAMBDA(cellRef,ISREF(INDEX(INDIRECT(cellRef),1)))) 

enter image description here

Answer 3

One way to handle structured references given as text via INDIRECT:

• Table1 & Table2 contain data
• Table3 contain names to the columns from the above

=REDUCE(
 "Handle table column refs",
 Table3[Col],
 LAMBDA(acc, cur, HSTACK(acc, INDIRECT(cur)))
)

Formula and result

• Façade: Insightful work. It doesn't exactly address the question, but instead gets the result I am ultimately looking for at this particular step in my formula.