Excel 2013多列匹配搜索并返回对应非空电缆类型的公式需求

Here's a tailored formula that meets all your requirements—it searches both Route columns, skips rows with empty Cable types, and works in Excel 2013:

=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,3,(ROW($A$2:$A$8)-ROW($A$1))/((((($B$2:$B$8=$I$2)+($C$2:$C$8=$I$2))>0)*($A$2:$A$8<>""))),ROWS($J$1:J1))),"")

Breakdown of How It Works #

Let's break down the formula to understand each part:

• INDEX($A$2:$A$8, ...): Pulls the Cable type value from the matching row in your data range (adjust $A$2:$A$8 to match your actual data rows).
• AGGREGATE(15,3, ..., ROWS($J$1:J1)): Finds the nth valid row number (the 15 uses the SMALL function, 3 ignores errors, and ROWS($J$1:J1) increments the row count as you drag the formula down).
• (ROW($A$2:$A$8)-ROW($A$1)): Converts absolute row numbers to relative positions within your Cable type range (so row 2 becomes 1, row 3 becomes 2, etc.).
• Filter Condition:
  • ($B$2:$B$8=$I$2)+($C$2:$C$8=$I$2): Checks if either Route 1 or Route 2 matches your search value (cell $I$2). Returns 1 for matches, 0 otherwise.
  • >0: Converts the sum to a TRUE/FALSE value (TRUE if either route matches).
  • *($A$2:$A$8<>""): Ensures we only include rows where the Cable type is not empty (multiplies by TRUE/FALSE to filter out empty rows).
• IFERROR(..., ""): Returns a blank cell once all matching results are exhausted, instead of an error.

How to Use #

1. Enter your search value (e.g., 7) in cell I2 (adjust this reference if you use a different cell).
2. Paste the formula into your first results cell (e.g., J2).
3. Drag the formula down as far as needed—you'll see matching Cable types populate, with blanks appearing when there are no more valid matches.

Example Results for Your Sample Data #

When searching for 7, the formula will return:

• Row 1 of results: Search route for cables (from row 2, Route 2 matches)
• Row 2 of results: ΝΥΥ-J 3x1,5 (from row 4, Route 1 matches)
• Row 3 of results: ΝΥΥ-J 5x2,5 (from row 5, Route 1 matches)
• Rows 4+ will be blank (the row with Route 1 = 7 but empty Cable type is skipped)

Content of the question来源于stack exchange，提问作者Vangde