Excel分组数据提取求助：输入组ID获取完整组数据

Hey there! Let's solve this problem where you need to pull all rows associated with a specific 3-digit group ID (including the group header and all its member entries)—since VLOOKUP only returns one matching result, it won't cut it here. Below are two practical solutions tailored to different Excel versions:

Solution 1: Use the FILTER Function (Excel 365/2021 or later) #

This is the simplest approach if you have a modern Excel version with dynamic array support. Let's assume your raw data is in columns A to D (with headers: id, name, group_id, member_id), and you enter your target group ID in cell F2.

In an empty cell (say G2), enter this formula:

=FILTER(A:D, C:C=F2, "No matching group found")

How it works: #

• FILTER scans column C (your group ID column) for matches with the value in F2
• It returns all entire rows where the group ID matches, including both the group header row and all member rows
• The last argument ("No matching group found") is optional—it shows a message if there's no match

Solution 2: Compatibility Formula (INDEX + SMALL + IF) #

If you're using an older Excel version that doesn't support dynamic arrays, use this array formula combination. Again, assume raw data is in A:D, target group ID in F2, and you'll start extracting in G2:

1. In G2, enter this formula (for older Excel, press Ctrl + Shift + Enter to confirm as an array formula; modern Excel will handle it automatically):

=IFERROR(INDEX(A:A, SMALL(IF($C$2:$C$100=F2, ROW($C$2:$C$100)), ROWS($G$2:G2))), "")

2. Drag this formula across columns H, I, J (to match your raw data columns)
3. Drag the entire row down until you see blank cells (this will pull all matching rows)

How it works: #

• IF($C$2:$C$100=F2, ROW($C$2:$C$100)) identifies all row numbers where the group ID matches
• SMALL sorts those row numbers in ascending order, so we get the first match, then the second, etc.
• INDEX pulls the value from the corresponding row in column A (and we repeat this for other columns)
• IFERROR ensures blank cells show up once all matches are extracted

Important Notes #

• Format Group IDs as Text: Since group IDs are 3-digit numbers (like 002, if applicable), format the group ID column as text to prevent leading zeros from being dropped. Right-click the column > Format Cells > Text.
• Adjust Ranges: In the formulas above, replace $C$2:$C$100 with the actual range of your group ID column (make sure it covers all your data rows).

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