The INDEX and OFFSET functions can return a reference to a cell, something VLOOKUP can’t do. XLOOKUP can return a cell reference, let’s see one way to use it.

In the image below we have a typical budget layout.

We need formulas to return the month and Year to Date (YTD) figures in the columns O and P based on the current month selected in cell B1.

Current month

The current month calculation is easy. We could have done it with a HLOOKUP but I have used an XLOOKUP function in cell O4. The formula is

=XLOOKUP($B$1,$B$3:$M$3,$B4:$M4)

This formula looks up March 2020 from cell B1 in the range B3:M3 and returns the corresponding column from the range B4:M4. In this case column D or cell D4. Nothing amazing here, pretty much the same as a HLOOKUP.

YTD – returning a cell reference

XLOOKUP can do something that VLOOKUP only dreams of doing. In fact, most Excel functions can’t do this, but XLOOKUP can. It can return a reference to a cell.

This doesn’t sound too impressive, but in practice it allows you to create incredibly flexible formulas.

Let’s see how we can use this.

We need the YTD amount in column P based on cell B1. In row 4 we know we start in cell B4 but the end cell is determined by the entry in cell B1. How can we flexibly refer to the end cell of the range for the SUM functions?

Well I have already told you XLOOKUP can return a reference to a cell. So we can use it to define the end of the range we need.

In fact we can use the exact XLOOKUP function that we used in cell P4. The formula we need for the YTD amount is

=SUM(B4:XLOOKUP($B$1,$B$3:$M$3,$B4:$M4))

The SUM adds up the range starting at cell B4 and ending in the cell that XLOOKUP returns. XLOOKUP is not returning the value from cell D4 (284,953) it is returning a reference to cell D4. Hence the SUM adds up the range B4:D4.

The two functions commonly used for this type of calculation were INDEX or OFFSET. XLOOKUP is now probably the preferred solution.

As you can see in the image below, as you change the entry in cell B1, the report updates.