=LAMBDA(a,
LET(Range,IF(COLUMNS(a#)>1,a#,OFFSET(a,0,0,1,14)),
Range))(Q339)

The above formula is going to be used at many places. I need to eliminate OFFSET function to improve on speed. If target cell "a" is a spilling range then that range will be used, else the formula should return a range starting from target cell "a" to next 14 columns of that row.

=LAMBDA(a,
LET(Range,IF(COLUMNS(a#)>1,a#,OFFSET(a,0,0,1,14)),
Range))(Q339)

The above formula is going to be used at many places. I need to eliminate OFFSET function to improve on speed. If target cell "a" is a spilling range then that range will be used, else the formula should return a range starting from target cell "a" to next 14 columns of that row.

• excel
• excel-formula
• range
• offset

• So in all cases a is a spill range? – P.b Commented Mar 19 at 14:54
• In some cases it is not a spill, hence creating a range of single row & 14 columns. The resultant range will be used for further calculations. – Deepak Sugandhi Commented Mar 20 at 10:26

1 Answer 1

Use INDEX:

=LAMBDA(a,
LET(Range,IF(COLUMNS(a#)>1,a#,a:INDEX(A1:ZZ10000,ROW(a),COLUMN(a)+13)),
Range))(Q339)

The A1:ZZ10000 is a range large enough to encompass any references expected. The main thing is that it only works if the first reference is A1. The Second "end" reference can be anything to encompass the full field of choices.