# Summing a dynamic range in Excel with XLOOKUP

Accountants often want to sum data, such as year-to-date costs and rolling budgets, for a period of time. Excel XLOOKUP makes the process simpler.As accountants, we often want to sum data for a period of time, eg, sales for the last quarter, year-to-date costs, and rolling budgets/forecasts. We generally create these calculations "statically"; ie, the range is specified explicitly.

For example, we might have something like the following example:

Here, we have created a static formula for the year-to-date sales using the calculation

**=SUM(F4:J4)**

Next month, we would have to update this to

**=SUM(F4:K4)**

We can't simply sum the range in case other users populate row 4 with forecast estimates, etc. There is an alternative, and I thought I would go up to date and use Excel 365's **XLOOKUP** function.

As a reminder, **XLOOKUP** has the following syntax:

**XLOOKUP(lookup_value, lookup_vector, results_array, [if_not_found]**,** [match_mode], [search_mode])**

This function seeks out a **lookup_value** in the **lookup_vector** and returns the corresponding value in the **results_array**. It may seem complex, but most of the time you will require only the first three arguments:

**lookup_value:**This is required and defines what value you want to look up.**lookup_vector:**This reference is required and is the row or column of data you are referencing to look up**lookup_value**.**results_array:**This is where the corresponding item is you wish to return and is also required (even if it is the same as**lookup_vector**). This does*not*have to be a vector (ie, one row or one column of cells). It may be an array (with at least two rows and at least two columns of cells). The only stipulation is that the number of rows/columns must equal the number of rows/columns in the**lookup_vector**.

For the record, the remaining arguments are:

**if_not_found:**This optional argument allows you to replace the usual return of**#N/A**with something more informative like an alternative formula, text, or a value.**match_mode:**This argument is optional. There are four choices:**0:**Exact match (default).**-1:**Exact match or else the largest value less than or equal to**lookup_value**.**1:**Exact match or else the smallest value greater than or equal to**lookup_value**.**2:**Wildcard match. You should use the special character**?**to match any character and*****to match any run of characters.

What's impressive, though, is that for certain selections of the final argument (**search_mode**), you *don't* need to put your data in alphanumerical order. As far as I am aware, this is a first for Excel.

**search_mode:**This argument is also optional. There are again four choices:**1:**Search first to last (default).**-1:**Search last to first.**2:**What is known as a binary search, first to last (requires**lookup_vector**to be sorted). Just so you know, a binary search is a search algorithm that finds the position of a target value within a sorted array. A binary search compares the target value to the middle element of the array. If they are not equal, the half in which the target cannot lie is eliminated and the search continues on the remaining half, again taking the middle element to compare to the target value and repeating this until the target value is found.**-2:**Another binary search, this time last to first (and again, this requires**lookup_vector**to be sorted).

Let's have a look at **XLOOKUP **versus everyone's favourite function (except me),** VLOOKUP**:

You can clearly see the **XLOOKUP** function is shorter:

**=XLOOKUP(H52,F41:F47,G41:G47)**

Only the first three arguments are needed, whereas **VLOOKUP** requires both a fourth argument and, for full flexibility, the **COLUMNS** function as well. **XLOOKUP **will automatically update if rows/columns are inserted or deleted. It's just *simpler*.

We can use this to specify the start and end of our sum range as follows. Consider the following example:

The formula is "simply"

**=SUM(XLOOKUP(G18,H12:S12,H13:S13):XLOOKUP(G19,H12:S12,H13:S13))**

This is just two **XLOOKUP **functions joined together within a **SUM **function, specifying the start and end of the range. Indeed, if the First Date is *after* the Second Date, *it will still work*. The **SUM **function will work in "reverse order", too.

The dates may be varied, and the summation updates both automatically and correctly, as shown below.

Simple!

**Word to the wise**

For those who are getting upset at this point because they don't have access to Excel 365 and/or the **XLOOKUP **function, do not despair. The old faithful **INDEX(MATCH) **combo still works. It's just clunkier:

**=SUM(INDEX(H13:S13,MATCH(G18,H12:S12,0)):INDEX(H13:S13,MATCH(G19,H12:S12,0)))**

Lovely! Until next time …

*— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *Introduction to Financial Modelling

*. Send ideas for future Excel-related articles to him at*

*liam.bastick@sumproduct.com*

*. To comment on this article or to suggest an idea for another article, contact Jeff Drew at*

*Jeff.Drew@aicpa-cima.com*

*.*