动态表格中使用INDEX+MATCH函数匹配部分文本并提取多个匹配结果的方法

Got it, I’ve been in this exact situation before—trying to pull multiple matches from a dynamic table instead of just the first one. Here’s how you can get those ER contracts into A1, A2, and A3 smoothly:

First, let’s assume your list of contracts lives in a range (say, B2:B100). For clarity, I’ll refer to this range as ContractList (you can name it via the Formulas tab > Define Name, or just use your actual cell range like B$2:B$100 with absolute references to keep it fixed when copying formulas).

For Excel 2019 or Older (Array Formulas) #

You’ll need to enter these as array formulas (press Ctrl+Shift+Enter instead of just Enter after typing):

• Cell A1 (First match):

  =IFERROR(INDEX(ContractList, SMALL(IF(ISNUMBER(SEARCH("ER", ContractList)), ROW(ContractList)-ROW(INDEX(ContractList,1,1))+1), 1)), "")
  

• Cell A2 (Second match):

  =IFERROR(INDEX(ContractList, SMALL(IF(ISNUMBER(SEARCH("ER", ContractList)), ROW(ContractList)-ROW(INDEX(ContractList,1,1))+1), 2)), "")
  

• Cell A3 (Third match):

  =IFERROR(INDEX(ContractList, SMALL(IF(ISNUMBER(SEARCH("ER", ContractList)), ROW(ContractList)-ROW(INDEX(ContractList,1,1))+1), 3)), "")
  

For Excel 365/2021 (Simpler Spill Formula) #

If you have the latest Excel version, skip the individual cell formulas and use FILTER to auto-populate all matches:

=IFERROR(FILTER(ContractList, ISNUMBER(SEARCH("ER", ContractList))), "")

Drop this in A1, and it will automatically spill into A2 and A3 if there are 2-3 matches. No need for separate formulas!

Quick Breakdown of How It Works #

• ISNUMBER(SEARCH("ER", ContractList)): Checks each contract for "ER" (use FIND instead of SEARCH if you need case-sensitive matching).
• The IF function grabs the relative row number of each matching contract.
• SMALL(..., n) picks the nth matching row (1 for first, 2 for second, etc.).
• INDEX pulls the contract name from that row.
• IFERROR ensures blank cells instead of error messages if there are fewer than 3 matches.

内容的提问来源于stack exchange，提问作者Petter Løkken