Excel中Index Match无法匹配第二个值问题求助

Hey there, I’ve run into this exact problem a few times with Excel’s array formulas—super frustrating when the first match works but the second won’t cooperate, especially when you’ve confirmed duplicates exist. Let’s break down the most likely fixes:

• Switch from MATCH to SMALL+IF for multiple matches
  Your first formula uses MATCH, which only grabs the first occurrence of your criteria. To pull the second (or Nth) match, you need to adjust the formula to target subsequent matches. Here’s the go-to approach:

  =INDEX(YourReturnRange, SMALL(IF((CriteriaRange1=YourCriteria1)*(CriteriaRange2=YourCriteria2), ROW(YourReturnRange)-MIN(ROW(YourReturnRange))+1), ROW(A2)))
  

  Don’t forget to press Ctrl+Shift+Enter after entering this (for pre-365 Excel versions). The ROW(A2) part tells Excel to fetch the second match—when you drag the formula down, it’ll automatically increment to ROW(A3) for the third match, and so on.

• Double-check your cell references (absolute vs relative)
  If you’re dragging the formula down, make sure your criteria ranges and return range use absolute references (with $ signs) so they don’t shift unexpectedly. For example, use $A$1:$A$100 instead of A1:A100—this keeps the range fixed as you copy the formula to other cells.

• Verify data formats match
  Sometimes values that look identical aren’t: one might be a text string and the other a number. Test this by using =ISTEXT(CellWithValue) and =ISNUMBER(CellWithValue) on your criteria and the values you’re matching against. If they don’t align, convert them to the same format—use =VALUE(TextCell) to turn text into numbers, or =TEXT(NumberCell, "0") to turn numbers into text.

• Check for hidden spaces
  Invisible leading/trailing spaces can break matches even if values look identical. Use the TRIM() function to clean up both your criteria and the range you’re matching against. For example, adjust your criteria to TRIM(YourCriteriaCell) and your range to TRIM(CriteriaRange1) in the formula.

• Confirm your array formula is applied correctly
  When entering the formula for the second match, don’t skip pressing Ctrl+Shift+Enter—Excel won’t recognize it as an array formula otherwise, leading to errors or wrong values. You’ll know it worked if you see curly braces {} around the formula in the formula bar (don’t type these manually—Excel adds them automatically).

Hope one of these fixes gets your second match working smoothly!

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