Forum Discussion
Choose MAX date from several columns, based on TODAY's DATE
Here's what I think works, except for your column "Admissions File Pending" which you give as the "Stage" for a couple of entries, even though it's NOT the max date in the row.
Anyway, I've done it as three separate formulas first, and then a single nested monstrosity. First the three separate formulas:
- =MAX(B2:I2) gets the max date in a row
- =XMATCH(K2,B2:I2,0,-1) gets the right most column number for that max date (the "-1" goes from right to left through the range to get that match)
- =CHOOSE(L2,"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred") takes that right most column number and supplies the corresponding words from the header row.
Nested, they look like this
=CHOOSE(XMATCH(MAX(B2:I2),B2:I2,0,-1),"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred")
If this doesn't work for you, let me know where or how it's failing.
P.S. It was fun: first time I've tried to use XMATCH... turned out to be very useful.
Just one thing!
"except for your column "Admissions File Pending" which you give as the "Stage" for a couple of entries, even though it's NOT the max date in the row." - that's because i want to know the stage as of 03/06/2019 for rows where intake = 2019.2 and as of 03/06/2020 for rows where intake = 2020.2
XMATCH (and XLOOKUP) are quite new. They've been available to the "insiders" for some time, but to the rest of us ordinary people (subscribers to Microsoft 365) quite recently.
Also try to acquaint yourself with the Dynamic Array functions. FILTER, SORT, UNIQUE, etc A LOT of power in these.
- Now understanding what that is, here's a way to get Max Date dependent on the 2019.2 or 2020.2 reference:
=MAXIFS(B2:I2,B2:I2,"<"&IF(A2=$M$1,$M$2,$N$2))
Only one tweak to make it work easily, update your M1 and N1 headers to match your intake column, so 2019 will become 2019.2 and 2020.2. Now this way you can check if intake matches, use that date below. I updated Mathetes formula and it worked perfectly:
=CHOOSE(XMATCH(MAXIFS(B2:I2,B2:I2,"<"&IF(A2=$M$1,$M$2,$N$2)),B2:I2,0,-1),"Confirmed Interest","Admissions File Pending","Applied","Admitted","Pre-Enrolled","Enrolled","Closed","Deferred")
Still much more elegant than my nested IFS!Would you mind uploading the excel with the formula inside it, as when I add the formula to my excel, it doesn't seem to work? Thanks!
JennyHoA20181 Hi Jenny, I've attached the book with the formula in it. Note that I had converted your date format to MM/DD/YYYY so if you want it to read your way you'll probably have to just format the date columns again, but otherwise I think this should do it!
