Excel引用问题：按条件提取列数据时如何避免大量空单元格？

Got it, let's tackle this problem you're having—extracting matching data without all those annoying blank cells cluttering up your target table. I've run into this exact issue before, so here are a few solid solutions depending on your Excel version:

This is the cleanest, most straightforward fix if you're on a modern Excel version. FILTER automatically returns only rows that meet your criteria, no empty cells included.

For example, if you want to pull data from Column A where the corresponding cell in Column D is ≥10, enter this formula in the first cell of your target table (say, cell G1):

=FILTER(A:A, D:D>=10, "No matches found")

• The first argument (A:A) is the column you want to extract data from.
• The second argument (D:D>=10) is your condition.
• The third optional argument lets you set a message if there are no matching rows.

To extract multiple columns (e.g., Columns A-C where D≥10), adjust the first argument:

=FILTER(A:C, D:D>=10, "No matches found")

This will return entire rows of matching data, with zero blank rows in between.

If you're stuck with an older Excel that doesn't support FILTER, use a combination of INDEX, SMALL, and IF to filter valid rows manually.

Let's say you want to pull Column A data into Column G:

1. In cell G1, enter this array formula (press Ctrl+Shift+Enter instead of just Enter to activate it):

=IFERROR(INDEX(A:A, SMALL(IF(D:D>=10, ROW(D:D)), ROWS(G$1:G1))), "")

Breakdown of how this works:

• IF(D:D>=10, ROW(D:D)): Returns the row number for every cell in Column D that meets your condition; returns FALSE for non-matching rows.
• SMALL(..., ROWS(G$1:G1)): Pulls the 1st, 2nd, 3rd, etc., valid row number as you drag the formula down (the ROWS part increments automatically).
• INDEX(A:A, ...): Fetches the value from Column A at the valid row number.
• IFERROR(..., ""): Shows a blank cell instead of a #NUM! error once there are no more matching rows.

2. Drag the formula down Column G until you see empty cells—you'll only get rows that meet your criteria, no random blanks.

• Basic IF formulas (e.g., =IF(D4>=10,A4,"")): These check every row individually, so non-matching rows leave empty cells in your target table.
• VLOOKUP: This function is designed for matching a specific lookup value, not filtering all rows that meet a condition. It can't return multiple matching rows without extra work, and default behavior can lead to incorrect matches.
• INDEX alone: Without pairing it with a way to filter valid row numbers (like SMALL), INDEX can't skip non-matching rows automatically.

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