Lookup & Reference
Dynamic range between two matches: sum or extract rows between markers
Locate any Start milestone and End milestone with XMATCH and slice all rows inclusively between them using TAKE(DROP(...)).
Slice rows dynamically between two named milestones
Project budgets, General Ledger chart-of-accounts sections, and sensor logs often group rows between named boundary markers ("Phase2-Start" down to "Phase2-End") whose row positions shift whenever line items are inserted.
Finding the two positions with XMATCH, normalizing s = MIN(startPosition, endPosition) and e = MAX(startPosition, endPosition) so reversed inputs never break, and slicing TAKE(DROP(Values, s - 1), e - s + 1) extracts the exact inclusive sub-range cleanly!
| RowIdx | Milestone (A) | Budget (B) | Included in Phase2 Slice? |
|---|---|---|---|
| 1 | Discovery | $120 | Dropped (before s = 2) |
| 2 (s) | Phase2-Start | $250 | Yes |
| 3 | Core Build | $400 | Yes |
| 4 | QA Testing | $150 | Yes |
| 5 (e) | Phase2-End | $200 | Yes (Sum = $1,000) |
| 6 | Handover | $90 | Trimmed (after e = 5) |
Inclusive slice sum and row-count formulas
Using MIN(startPosition, endPosition) and MAX(startPosition, endPosition) makes the slice order-independent even if the user enters End before Start.
=LET(startPosition, XMATCH(D2, A2:A7), endPosition, XMATCH(E2, A2:A7), s, MIN(startPosition, endPosition), e, MAX(startPosition, endPosition), IF(s=1,SUM(TAKE(B2:B7,e)),SUM(TAKE(DROP(B2:B7,s-1),e-s+1))))List step names inside the matched boundary window
Extracts the text labels between s and e.
=LET(startPosition, XMATCH(D2, A2:A7), endPosition, XMATCH(E2, A2:A7), s, MIN(startPosition, endPosition), e, MAX(startPosition, endPosition), IF(s=1,TEXTJOIN(" -> ",TRUE,TAKE(A2:A7,e)),TEXTJOIN(" -> ",TRUE,TAKE(DROP(A2:A7,s-1),e-s+1))))Inputs and interpretation
XMATCH selects the first occurrence of a duplicate marker. The endpoints are inclusive; swapping them changes no rows in this policy. Missing markers return an error. Use aligned bounded ranges. The first-row branch avoids DROP(array,0), which is not portable across spreadsheet engines.
Try the example
Use the original values or change them to test your understanding.