Excel使用INDEX/MATCH提取多列多行匹配值求助：仅得首个匹配结果

Got it, let's tackle this problem—you're right that the basic INDEX/MATCH combo only grabs the first matching row, but there are a couple of reliable ways to pull all records where column H matches your value in $C$29.

Method 1: Use the FILTER Function (Modern Excel 365/2021+) #

If you have access to dynamic array functions (Excel 365, Excel 2021, or Excel for the web), this is by far the simplest solution. The FILTER function will automatically return all matching rows and spill them into adjacent cells without needing to drag formulas.

Just enter this formula in the top-left cell where you want your results to start (e.g., G31):

=FILTER(G13:H21, H13:H21=$C$29, "No matches found")

• G13:H21: The range of data you want to extract from
• H13:H21=$C$29: The condition (match values in column H to your target in C29)
• "No matches found": Optional message if there are no matching rows (you can leave this out to get #CALC! instead)

Once you enter it, Excel will automatically populate all matching rows below and to the right—no dragging required.

Method 2: INDEX + SMALL + IF Combo (Older Excel Versions) #

If you're using an older Excel version without dynamic arrays, you'll need an array formula to pull multiple matches. Here's how to set it up:

1. In the first cell of your results column (e.g., G31), enter this formula:

   =IFERROR(INDEX(G13:G21, SMALL(IF(H13:H21=$C$29, ROW(H13:H21)-ROW(H13)+1), ROWS($G$31:G31))), "")
   

2. Press Ctrl+Shift+Enter (not just Enter) to confirm it as an array formula. You'll see curly braces {} around the formula in the formula bar if done correctly.
3. Drag this formula to the right to cover column H (so you get both columns of data).
4. Drag the formula down until you start seeing blank cells—those mean you've pulled all matching rows.

Let's break down how this works:

• IF(H13:H21=$C$29, ROW(H13:H21)-ROW(H13)+1): Creates a list of row numbers (relative to your data range) where column H matches C29; non-matching rows return FALSE.
• SMALL(..., ROWS($G$31:G31)): Picks the 1st, 2nd, 3rd, etc., matching row number as you drag the formula down.
• INDEX(G13:G21, ...): Pulls the value from column G at the matching row number.
• IFERROR(..., ""): Leaves cells blank once there are no more matches to show.

Quick Tips #

• Make sure the data type in $C$29 matches the data type in column H (e.g., if H has text, C29 should be text, not a number formatted as text).
• For the array formula method, don't delete or modify the curly braces manually—always use Ctrl+Shift+Enter to update it.

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