excel - Replace OFFSET formula - Stack Overflow

admin管理员组

文章数量:1384358

=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

Share Improve this question Follow edited Mar 19 at 13:55 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 19 at 12:22 Deepak SugandhiDeepak Sugandhi 2333 bronze badges 2

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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.

本文标签： excelReplace OFFSET formulaStack Overflow