XLOOKUP – finding first or last value in data set

Yet another blog about the XLOOKUP function which is so powerful.

Say I have a list of customers and I want to find the date of their first order or their last order.

I have more than one order for several customers.

The syntax for the XLOOKUP function is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. 

It is the Search mode that we need to think about in this instance. This is input as:

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

In the example I am using my Order Dates are in sort order; if not, you would need to sort before running the XLOOKUP. To find the earliest Order Date, we use 1 as our search mode and to find the latest Order Date we use -1 as our search mode.

So my first formula is =XLOOKUP(I2,B2:B154,D2:D154,,0,1)

and my second formula is

=XLOOKUP(I2,B2:B154,D2:D154,,0,-1)

Other examples would be finding the first or last sales amount for a particular client, etc.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 for every time someone says "If only I'd known that." ....
This entry was posted in Excel 365 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s