Forum Discussion
Choose MAX Date where it is less than or equal to another date
Hello,
I am able to select the Maximum date in several columns and bring back the corresponding column title using the formula below.
=CHOOSE(XMATCH(MAX(B6:I6),B6:I6,0,-1),"Interest","CI","AFP","Applied","Admitted","Pre-Enrolled","Enrolled","Student")
BUT, I want to bring back the Maximum date where it is LESS THAN OR EQUAL TO Column A. How did I fit this second part into the formula above?
Please see attached the Excel and the values in column J are the results I actually want.
This is just playing with dynamic array solutions. Each formula needs to be applied row by row. BYROW is the obvious solution but only accepts one array whereas MAP accepts multiple parameters but will not extract the row to search from an array.
= BYROW(dateArr, LAMBDA(keyDate, XLOOKUP("", keyDate, status,"-",-1,-1) ) ) = BYROW(firstDateArr:dateArr, LAMBDA(a, XLOOKUP(a firstDateArr, a dateArr, status,"-",-1,-1) ) )The trick in the second formula is the intersection of the original arrays with the dynamic row 'a'.
Alternatively, using MAP,
= MAP(firstDateArr,keyDatesϑ, LAMBDA(crit,datesϑ, XLOOKUP("", datesϑ(),status,"-",-1,-1) ) ) = MAP(firstDateArr,keyDatesϑ, LAMBDA(crit,datesϑ, XLOOKUP(crit, datesϑ(),status,"-",-1,-1) ) )The trick here is that the array of key dates has been passed as a thunk, so allowing MAP to work with it. In the formula, each scalar thunk is expanded to a row array by the term datesϑ().
keyDatesϑ = BYROW(dateArr,Thunkλ); Thunkλ = LAMBDA(x,LAMBDA(x))To create a two-dimension array of results is possible but lacks elegance.
8 Replies
Hi , I hope someone can help me to complete the formula - it does not work for LINES 6 AND 7 - because the date is repeated. COLUMN J shows the result I want to see. For example LINE 6 shows the date 28/02/2022 in the CI and the AFP columns. As AFP is later than CI, I want the result to be AFP.
I hope this makes sense! Thank you in advance for your help!
This is just playing with dynamic array solutions. Each formula needs to be applied row by row. BYROW is the obvious solution but only accepts one array whereas MAP accepts multiple parameters but will not extract the row to search from an array.
= BYROW(dateArr, LAMBDA(keyDate, XLOOKUP("", keyDate, status,"-",-1,-1) ) ) = BYROW(firstDateArr:dateArr, LAMBDA(a, XLOOKUP(a firstDateArr, a dateArr, status,"-",-1,-1) ) )The trick in the second formula is the intersection of the original arrays with the dynamic row 'a'.
Alternatively, using MAP,
= MAP(firstDateArr,keyDatesϑ, LAMBDA(crit,datesϑ, XLOOKUP("", datesϑ(),status,"-",-1,-1) ) ) = MAP(firstDateArr,keyDatesϑ, LAMBDA(crit,datesϑ, XLOOKUP(crit, datesϑ(),status,"-",-1,-1) ) )The trick here is that the array of key dates has been passed as a thunk, so allowing MAP to work with it. In the formula, each scalar thunk is expanded to a row array by the term datesϑ().
keyDatesϑ = BYROW(dateArr,Thunkλ); Thunkλ = LAMBDA(x,LAMBDA(x))To create a two-dimension array of results is possible but lacks elegance.
As variant
only max: =XLOOKUP(99999, B6:I6, $B$1:$I$1,, -1 ) max before: =XLOOKUP(A6, B6:I6, $B$1:$I$1,,-1)- Thank you both so much! mtarler @sergei
There is just one piece missing - for example line 6 should bring back the value AFP, because the student reached the stage CI and then AFP on the same day, AFP is the bigger stage. Max stage is sorted from left to right. 'Interest' in column B is the smallest stage, 'Student' in column I is the MAX stage. So if a date is repeated, I want it to take the repeated date in the stage furthest to the right.
Thank you in advance for your help!!! - oh wait, that's cool. I had assumed the match mode was like before where it assumed and required the lists to be sorted in specific order for specific match modes (i.e. -1 and 1), but it appears that is no longer the case. nice.
When I enquired whether it would be more efficient to use SORT before the lookup, Joe McDaid told me not to because XLOOKUP would take care of the sort automatically.
I have yet to look at the details but the present question might be answered by appending a further parameter, set to -1, to the XLOOKUP which returns the final match rather the first.
JennyHoA20181 Here is what you want:
=XLOOKUP(MAX(($B6:$I6)*($B6:$I6<=$A6)),$B6:$I6,$B$1:$I$1)and BTW you should update your other formula to:
=XLOOKUP(MAX($B6:$I6),$B6:$I6,$B$1:$I$1)see attached
