XLOOKUP and the Last Entry

• 2 mins read

In the image below I have payments coming in. If you need to identify the month of the last payment VLOOKUP can’t help.

XLOOKUP offers a way to solve this problem.

TRUE solution

Dynamic arrays allow us to refer to a range in the same way we used to refer to a cell. This enables us to create a range of results in memory that don’t exist on the sheet.

In the image below I have a formula in cell B3 that spills across. It returns TRUE for each column with a non-zero value and FALSE for each column with a zero value.

The formula returns a range, I can use this formula by itself in an XLOOKUP function to create a row of TRUE/FALSE results within the XLOOKUP. This means I can look up TRUE to find the last column.

XLOOKUP has an argument that looks up from the right in a horizontal lookup, not the left. So I can find the last TRUE in the range.

The image below shows the final formula.

The formula in cell O2 is:

=XLOOKUP(TRUE,B2:M2<>0,B1:M1,"No Payment",0,-1)

This XLOOKUP looks up TRUE in the range created by the reference B2:M2<>0 and returns the month from the B1:M1 range.

You can see that range of TRUE and FALSE entries being created inside the XLOOKUP function – see image below.

The fifth argument with 0 means an exact match.

The sixth argument with -1 means look from the right to the left to find the first match.

When payments update the formula updates – see image below.

Blanks and zeroes

This formula treats zero and blank cells the same, as you can see in the image below.

Text entries

Note that text entries in the range would trigger the XLOOKUP – see below.

No payments

If there are no payments in the range the built-in XLOOKUP error handler (fourth argument) kicks in – see below.