Excel公式求助：拼接3字符串后拖拽实现行号自动递增

Got it, let's tackle this drag-and-drop increment issue you're facing. The core problem here is that your third string ("17") is hardcoded as static text—Excel has no way to know it should increment when you drag the formula down. Let's adjust things to make that row number dynamic, and simplify the formula for better readability too.

Step 1: Break Down the Root Issue #

Your current formula locks the row number as "17" because it's a plain text string. To get it to update with each row you drag, we need to replace that static value with a formula that calculates the correct row number based on where the formula lives in your sheet.

Step 2: Add Dynamic Row Number Logic #

Instead of "17", use the ROW() function to generate a row number that increments automatically. Here's the key calculation:

• If your formula starts in row 1 (e.g., cell A1), the first target row is 17. For each row below, we add 1 to that number. The dynamic row number becomes 17 + (ROW() - ROW(A1)).
  • ROW() returns the current row of the formula cell.
  • ROW(A1) references the starting row of your formula (adjust this if you start in a different row—like ROW(B5) if your formula begins in cell B5).
  • Subtracting these gives how many rows you've dragged down, and adding that to 17 gives the exact target row you need.

Step 3: Updated Formula (Text Output) #

If you just want the concatenated reference string (e.g., "Sheet2!B17", "Sheet2!B18"), use this formula:

="Sheet2!"&SUBSTITUTE(ADDRESS(1,MATCH("String to Search For", Sheet2!$13:$13,0),4),1,"")&(17 + ROW() - ROW(A1))

Step 4: Updated Formula (Live Cell Reference) #

If your end goal is to pull the actual value from that Sheet2 cell (instead of just the text reference), wrap the whole thing in INDIRECT() to convert the string into a live cell reference:

=INDIRECT("Sheet2!"&SUBSTITUTE(ADDRESS(1,MATCH("String to Search For", Sheet2!$13:$13,0),4),1,"")&(17 + ROW() - ROW(A1)))

How It Works When Dragging #

• As you drag the formula down, ROW() increases by 1 each time, so 17 + (ROW() - ROW(A1)) becomes 18, then 19, and so on—perfect for your 5000-row drag.
• The $ in Sheet2!$13:$13 locks the search range to row 13, so your column letter won't change as you drag down (which I assume is what you want, since you're searching for a fixed string in that row).

Quick Simplification Tip #

I swapped CONCATENATE with & here—Excel treats both identically, but & makes the formula easier to read without wrapping everything in a function call.

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