Excel多工作表重复编号索引问题：公式出现#REF!错误求助

Hey there, let’s work through that frustrating #REF! error you’re hitting with your array formula. I’ve dealt with similar cross-sheet INDEX/SMALL issues before, so let’s break down the root causes and get you a working solution.

Common Causes of the #REF! Error #

• Mismatched Range Sizes: Your original formula uses a fixed range 'ODG Jobs'!A1:Q33 for INDEX, but checks the entire C:C column for matches. If a matching row falls outside rows 1-33, INDEX tries to reference a cell that doesn’t exist in its defined range, triggering #REF!.
• Static Row Counter: Using ROW('ODG Jobs'!1:33) means you’re forcing the formula to check up to 33 rows even if there are fewer matches. When the SMALL function returns a row number beyond your INDEX range, it breaks.
• Truncated Formula Syntax: Your posted formula cuts off mid-way (SMALL(IF('...))), which would definitely cause syntax errors and #REF!.

Corrected Array Formula (Legacy Excel) #

If you’re using an older Excel version (pre-365/2021), use this adjusted array formula (remember to press Ctrl+Shift+Enter after entering it, not just Enter):

=IFERROR(INDEX('ODG Jobs'!$E:$E, SMALL(IF('ODG Jobs'!$C:$C=$B3, ROW('ODG Jobs'!$C:$C)), ROWS($A$1:A1))), "")

Key Improvements: #

• Switched to full-column reference $E:$E for the INDEX output (matches your original 5th column in A1:Q33) to avoid range limitations.
• Replaced the static ROW('ODG Jobs'!1:33) with ROWS($A$1:A1)—this dynamically increments as you drag the formula down, so it only requests the 1st, 2nd, 3rd, etc., matching row instead of forcing up to 33 rows.
• Used IFERROR to cleanly return an empty string when there are no more matches, instead of nested ISERROR+IF.

Better Alternative for Modern Excel (365/2021+) #

If you have access to dynamic array functions, ditch the complex array formula entirely and use FILTER—it’s simpler, faster, and automatically spills all matching results:

=FILTER('ODG Jobs'!$E:$E, 'ODG Jobs'!$C:$C=$B3, "")

Just enter this in a single cell, and Excel will populate all matching order quantities below it without needing to drag the formula.

Quick Tips to Avoid Future Errors #

• Limit Ranges for Performance: If full-column references slow down your sheet, replace $C:$C and $E:$E with a defined range like 'ODG Jobs'!$C$1:$C$1000 (adjust the row number to match your actual data size).
• Verify Part Number Match: Double-check that $B3 contains the exact part number format (no extra spaces) as the values in 'ODG Jobs'!C:C—mismatched formatting can cause unexpected missing results.

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