咨询：Excel中替代Google Sheets特定Filter函数的等价公式

Absolutely! Let's break down how to replicate your Google Sheets logic in Excel, depending on which version you're using:

Excel now supports dynamic array formulas, so you can use a nearly equivalent FILTER function—just note the slight syntax difference for combining conditions:

• Return all matching rows (mirroring Google Sheets' core FILTER behavior):
  Use * to represent "AND" in array logic, then the formula will automatically spill results to adjacent cells:

  =FILTER(A:J, (B:B=B2)*(J:J="Yes"))
  

  Swap A:J with B:B if you only need values from column B instead of full rows.

• Check if more than 1 matching row exists (your original >1 logic):
  Skip FILTER entirely and use COUNTIFS for a simpler, more efficient check:

  =COUNTIFS(B:B, B2, J:J, "Yes")>1
  

  This returns TRUE or FALSE directly based on whether there are multiple matching records.

If you don't have access to dynamic arrays, use an array formula with INDEX + SMALL to list matching values:

1. In a blank cell (e.g., C2), enter this formula:

   =INDEX(B:B, SMALL(IF((B:B=B2)*(J:J="Yes"), ROW(B:B)), ROWS($C$2:C2)))
   

2. Press Ctrl+Shift+Enter (this triggers the array formula in older Excel versions).
3. Drag the formula down until you see #NUM!—that means you've listed all matching results.

Alternatively, you can use Excel's Advanced Filter feature:

• Set up a criteria range (e.g., in a blank area, add your column headers, then under the B column header enter =B2 and under J enter Yes).
• Go to the Data tab → Advanced, select "Copy to another location", define your data range, criteria range, and output location, then click OK.

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