Dynamic range between two matches

In this example, we have dates in B5:B16 and sales in C5:C16. Both ranges are named ranges. The goal is to create a dynamic range between two specific dates: the start date in cell F5 and the end date in cell F6. We then use a formula in F8 to sum the dynamic range, and a formula in F9 to count the dynamic range. In the current version of Excel, this problem can be easily solved with the XLOOKUP function. In older versions of Excel without XLOOKUP, you can use INDEX and MATCH. Both approaches are described below. This problem is a nice demonstration of how both XLOOKUP and INDEX return a valid reference that can be used like any other cell reference.

Range operator

One of Excel's most common operators is the colon (:) which is also known as the "range operator". The range operator (:) is used to construct ranges. For example, to create a reference from cell A1 to cell A9, you would use a range like this:

=A1:A9

To sum all values in this range, you would simply embed the range in the SUM function:

=SUM(A1:A9)

In this problem, we want to do essentially the same thing. However, the catch is that we want the range to be dynamic so that the reference to A1 and A9 are the result of user input. Conceptionally, the generic syntax for what we want looks like this:

=SUM(lookup1:lookup2)

In the code above, lookup1 should return a reference to the first cell in the range (the start date in F5), and lookup2 should be a reference to the last cell in the range (the end date in F6).

XLOOKUP function

The trick in solving this problem is to understand that the XLOOKUP function returns a reference and not just a value. This is not obvious in Excel, because even though XLOOKUP returns a reference, Excel will then immediately return the value at that reference. Nevertheless, the reference is there and can be used in other ways. In this case, we use this feature to assemble a range based on the results from two separate XLOOKUP formulas like this:

=SUM(XLOOKUP(F5,date,sales):XLOOKUP(F6,date,sales))

Inside the SUM function, notice we are using two separate XLOOKUP formulas joined with the range operator (:). The first XLOOKUP formula locates the start date in F5 in column B and returns a reference to the corresponding cell in column C:

XLOOKUP(F5,date,sales) // returns C5

With the lookup_value provided as F5, the lookup_array given as date (B5:B16), and the return_array given as sales (C5:C16), XLOOKUP matches January 1 in cell B5 and returns a reference to cell C5 as a result. The second XLOOKUP formula is configured in the same way, except the lookup_value is F6 (the end date):

XLOOKUP(F6,date,sales) // returns C10

With June 1 in cell F6, the result from XLOOKUP is a reference to C10. Putting this all together, the formula in F8 is evaluated by Excel like this:

=SUM(XLOOKUP(F5,date,sales):XLOOKUP(F6,date,sales))
=SUM(C5:C10)
=7075

When the dates in F5 or F6 are changed, XLOOKUP returns new references and the range is dynamically updated. The SUM then returns a new result. To count results instead of summing results, just replace the SUM function with the COUNT function. In the worksheet shown, the formula in cell F9 looks like this:

=COUNT(XLOOKUP(F5,date,sales):XLOOKUP(F6,date,sales))

For a detailed overview of XLOOKUP, see How to use the XLOOKUP function.

INDEX and MATCH

In older versions of Excel without the XLOOKUP function, this problem can be solved in the same way with an INDEX and MATCH that uses the same structure:

=SUM(INDEX(sales,MATCH(F5,date,0)):INDEX(sales,MATCH(F6,date,0)))

This works because the INDEX function, like XLOOKUP, returns a reference when the array provided to INDEX is a range. Notice the range operator (:) sits between two separate INDEX and MATCH formulas. The first INDEX and MATCH formula locates the date entered in F5 (January 1) in column B and returns a corresponding reference from the sales amounts in column C:

INDEX(sales,MATCH(F5,date,0)) // returns C5

The result is a reference to cell C5 since cell B5 contains January 1. The second INDEX and MATCH formula is the same, except that the lookup value comes from cell F6:

INDEX(sales,MATCH(F6,date,0)) // returns C10

The result is a reference to cell C10 since cell B10 contains June 1. Excel evaluates this formula in the same way as the XLOOKUP version above, with exactly the same result:

=SUM(INDEX(sales,MATCH(F5,date,0)):INDEX(sales,MATCH(F6,date,0)))
=SUM(C5:C10)
=7075

If the dates in F5 and F6 are changed, the INDEX and MATCH formulas return new references, and the range they create is dynamically updated. The SUM function then returns a new result. To count results instead of summing results, just replace the SUM function with the COUNT function:

=COUNT(INDEX(sales,MATCH(F5,date,0)):INDEX(sales,MATCH(F6,date,0)))

For a detailed overview of INDEX with MATCH see: How to use INDEX and MATCH.