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
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.