=ARRAYFORMULA( LET( last , 'Last Rows'!$B$18 , rng , ROW(INDIRECT("AM3:AM"&last)) , sku…
- User
=ARRAYFORMULA( LET( last , 'Last Rows'!18 , rng , ROW(INDIRECT("AM3:AM"&last)) , sku , INDIRECT("AM3:AM"&last) , qty , INDIRECT("AO3:AO"&last) , tgt , INDIRECT("BH3:BH"&last) ,
num_rows, ROWS(sku), final_given, SCAN( MAKEARRAY(num_rows, 1, LAMBDA(r,c,0)), SEQUENCE(num_rows), LAMBDA(given_so_far_array, i, LET( current_sku, INDEX(sku, i), current_qty, INDEX(qty, i), current_tgt, INDEX(tgt, i), allocated_above_actual, SUM( IFERROR( FILTER(given_so_far_array, (sku = current_sku) * (rng < INDEX(rng, i)) ), 0 ) ), need, current_tgt - allocated_above_actual, this_given, IF(need <= 0, 0, MIN(current_qty, need)), IFERROR(REPLACE(given_so_far_array, i, 1, this_given), given_so_far_array) ) ) ), final_given) ) i am getting error: Result should be a single row.
Assistant
The "Result should be a single row" error in Google Sheets often occurs when using array formulas (
ARRAYFORMULA,LET,LAMBDA,SCAN, etc.) in conjunction with functions that expect or return results in a specific shape (e.g., a single row or column).In your case, the issue stems from how
SCANinteracts withARRAYFORMULAand theLETfunction.The Problem:
SCANReturns a 2D Array: Even thoughMAKEARRAY(num_rows, 1, ...)creates a 2D array (N rows, 1 column), andREPLACEalso operates on this 2D structure,SCANitself is designed to return an array that matches the shape of its second argument (SEQUENCE(num_rows)). SinceSEQUENCE(num_rows)is a column vector (N rows, 1 column),SCANdutifully returns a column vector (N rows, 1 column).ARRAYFORMULAExpects a Single Row or Column: When you wrap everything inARRAYFORMULA, Google Sheets sometimes gets confused about the dimensionality of the final result.ARRAYFORMULAis generally used to "spill" results into multiple cells, but in certain contexts (especially withLETand deeply nested lambdas), it can misinterpret the shape.
The Fix:
You don't actually need
ARRAYFORMULAhere.LET,SCAN, and the lambdas are already handling the array operations. Simply removeARRAYFORMULA:=LET( last , 'Last Rows'!$B$18 , rng , ROW(INDIRECT("AM3:AM"&last)) , sku , INDIRECT("AM3:AM"&last) , qty , INDIRECT("AO3:AO"&last) , tgt , INDIRECT("BH3:BH"&last) , num_rows, ROWS(sku), final_given, SCAN( MAKEARRAY(num_rows, 1, LAMBDA(r,c,0)), SEQUENCE(num_rows), LAMBDA(given_so_far_array, i, LET( current_sku, INDEX(sku, i), current_qty, INDEX(qty, i), current_tgt, INDEX(tgt, i), allocated_above_actual, SUM( IFERROR( FILTER(given_so_far_array, (sku = current_sku) * (rng < INDEX(rng, i)) ), 0 ) ), need, current_tgt - allocated_above_actual, this_given, IF(need <= 0, 0, MIN(current_qty, need)), IFERROR(REPLACE(given_so_far_array, i, 1, this_given), given_so_far_array) ) ) ), final_given )Why This Works:
LETis Inherently Array-Aware: Modern Google Sheets functions likeLET,LAMBDA,MAP,SCAN,REDUCEare designed to handle arrays natively. You don't needARRAYFORMULAto "enable" array behavior anymore.SCANReturns the Correct Shape: SinceSEQUENCE(num_rows)is a column (N x 1),SCANreturns a column vector. Google Sheets will automatically "spill" this column vector into the sheet, starting from the cell where you enter the formula.- No Dimensionality Conflict: By removing
ARRAYFORMULA, you avoid any potential misinterpretation about whether the result should be a row, column, or something else.LET+SCANclearly communicate that the output is thefinal_givenarray, which is N x 1.
Additional Tip:
If you ever encounter shape issues, you can explicitly force the result to be a column using
TOCOL(turn into column) orTOROW(turn into row). For example:=TOCOL( LET( ... (your entire formula here) final_given ), 1 /* Ignore blanks */ )This wraps your
LETresult inTOCOL, guaranteeing a column vector output. Similarly,TOROWwould flatten it into a single row.Try the simplified version without
ARRAYFORMULA. It should work seamlessly and fill the column with yourfinal_givenvalues.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.