求助：基于条件生成无空值动态数组的Excel公式问题

Hey there! Let's get this sorted for you. As an Excel newbie, those old array formulas can feel super confusing—especially when you're trying to work with dynamic lists. Your goal is to pull all values ending with "78" from a changing list and output them without empty gaps, so let's break down the fixes and better alternatives.

First, Why Your Original Formula Failed #

Your existing formula checks if values equal exactly "12378", but you need to match any value that ends with "78". That's the core issue! Also, if you're using a modern Excel version (365/2021), there's a way simpler dynamic array function that does this automatically, no messy array entry required.

Option 1: Modern Excel (365/2021+ with Dynamic Arrays) #

Use the FILTER function—it's made for exactly this kind of task. It will automatically spill all matching values into a clean, gap-free list, and update automatically when your source list changes.

Enter this formula in the first cell of your target column (e.g., B2):

=FILTER('2.Data'!$L$3:$L$300, RIGHT('2.Data'!$L$3:$L$300, 2) = "78", "No matches found")

• RIGHT(..., 2) grabs the last two characters of each value in your source range
• FILTER returns only values where those last two characters are "78"
• The final argument ("No matches found") is optional—replace it with whatever you want to show if there are no matches (leave it out to show #CALC! instead)

Option 2: Older Excel Versions (No Dynamic Arrays) #

If you're stuck with an older Excel version that doesn't support FILTER, we'll fix your original array formula. The key is updating the condition to check for values ending with "78", and remembering to enter it as an array formula.

Enter this formula in B2, then press Ctrl+Shift+Enter (not just Enter—this tells Excel it's an array formula):

=IFERROR(INDEX('2.Data'!$L$3:$L$300, SMALL(IF(RIGHT('2.Data'!$L$3:$L$300, 2) = "78", ROW('2.Data'!$L$3:$L$300) - ROW('2.Data'!$L$3) + 1), ROWS(B$2:B2))), "")

• Drag this formula down from B2 to as many rows as you think you might need (it will show empty cells once all matches are pulled)
• To exclude blank cells from your source list (just in case), modify the condition to:

  =IFERROR(INDEX('2.Data'!$L$3:$L$300, SMALL(IF((RIGHT('2.Data'!$L$3:$L$300, 2) = "78")*('2.Data'!$L$3:$L$300 <> ""), ROW('2.Data'!$L$3:$L$300) - ROW('2.Data'!$L$3) + 1), ROWS(B$2:B2))), "")
  

  The *('2.Data'!$L$3:$L$300 <> "") part adds an extra check to skip empty cells in your source range.

Quick Notes #

• If your source values are numbers instead of text, RIGHT() will still work (Excel automatically converts numbers to text for this function)
• Both solutions will update automatically if your source list changes (add/remove values that end with "78")

Give these a shot—hope they work smoothly for you!

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