XLOOKUP: Search from the bottom and top of a list

More XLOOKUP!
This video shows how I would have completed a task 15 years ago if I had XLOOKUP available. Here, with a list of course completions, sorted by completion date, we need to calculate the number of days between the first completion and the last completion. And then see if there are any students who took more than the allowed 90 days.

This is complicated by 2 issues:
1. People taking the courses out of order.
2. Some people, not all, are required to take course 3A.

VLOOKUP can do the easy part and retrieve the first completion date for each person. XLOOKUP can do that. But XLOOKUP can also do the harder part of starting a search at the bottom of the list, and work upward. This is really exciting and shows how XLOOKUP is indeed the replacement for VLOOKUP.

Also in this video, we use dynamics arrays, the UNIQUE function, to extract the names of the students. UNIQUE, XLOOKUP! We’re really doing some Excel folks!

#XLOOKUP
#UNIQUE
#DynamicArrays

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2