Hi - Dave here.
Happy Friday!
One feature of XLOOKUP that most people don't know about is that it returns a
reference, not just a value.
This is somewhat confusing because normally Excel immediately returns the
value at the reference so you never actually see the
reference. Nevertheless, the reference
is there and can be used in some interesting and useful ways.
One example is to create a dynamic range based on two XLOOKUP formulas, as seen below. When a user enters a Start and End date, XLOOKUP finds the dates in column B and returns a corresponding
reference from column C.
We then join the two references with a colon (:) to create a normal range and give that range to the SUM function. The result is a simple dynamic range with variable endpoints. And although this example uses dates, you could match other values as well.
[
Download the workbook and read the full explanation]
In older versions of Excel without XLOOKUP, you can use the same idea with INDEX and MATCH. This works because INDEX, like XLOOKUP, actually returns a
reference. The link above explains both formulas and includes the workbook. Download the workbook and try it out yourself.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
