Google Sheets如何将多列物品关联对应箱号并转为两列？

To get the paired box numbers and items in two columns, use this concise formula (replace A1:A5 with your box number range and B1:E5 with your items range):

=HSTACK(INDEX(A:A, TOCOL(ROW(B1:E5), 1)), TOCOL(B1:E5, 1))

How it works: #

1. TOCOL(B1:E5, 1) extracts all non-blank items into a single column (the 1 parameter ignores empty cells).
2. TOCOL(ROW(B1:E5), 1) generates the original sheet row number for each extracted item, in the same order as the items column.
3. INDEX(A:A, ...) pulls the corresponding box number from column A using those original row numbers.
4. HSTACK combines the box numbers and items into a two-column table.

Your formula =BYROW(TOCOL(B1:E5,1), LAMBDA(range,{range,ROW(range)})) was returning the row number of the resulting TOCOL column, not the original row where the item was stored. ROW(range) in the lambda refers to the position of the item in the new flattened column, not its location in the original dataset. That's why the box numbers didn't match correctly.

• Break formulas into components: Test each part separately (e.g., run TOCOL(B1:E5,1) alone to confirm items are extracted correctly, then TOCOL(ROW(B1:E5),1) to verify original rows are captured).
• Use LET for readability: Rewrite complex formulas with intermediate variables to isolate issues. For example:

  =LET(
    items, TOCOL(B1:E5, 1),
    original_rows, TOCOL(ROW(B1:E5), 1),
    box_numbers, INDEX(A:A, original_rows),
    HSTACK(box_numbers, items)
  )
  

• Check array dimensions: Ensure all arrays being combined (e.g., with HSTACK or VSTACK) have the same number of rows. Mismatched sizes will cause errors.
• Validate ignore parameters: For TOCOL, confirm you're using the right ignore flag (1 skips blanks, 2 skips errors, 3 skips both).
• Test with small datasets: Use a tiny subset of your data (e.g., 2 rows, 2 item columns) to debug before scaling to the full range.
• Leverage built-in help: Hover over any function name in Google Sheets to see syntax, arguments, and examples directly in the editor.

内容的提问来源于stack exchange，提问作者Contrick