Forum Discussion
How XLOOKUP arguments apply in this case
I'm not an Excel newbie, but I have never had cause to use XLOOKUP, INDEX, or MATCH before. I'm not understanding how the xlookup arguments would apply in my case or if index/match would be better. ...
Here is my solution. If there are ties then make sure there are enough empty cells below the formula.
=LET(
data,B2:AF13,
columns,COLUMN(data)-1,
rows,ROW(data)-1,
year,A1,
searchvalue,MAX(data),
matches,(data=searchvalue)*(rows+columns%%),
matches_list,TOCOL(matches),
filtered_matches,FILTER(matches_list,matches_list>0),
matches_count,COUNT(filtered_matches),
months,QUOTIENT(+filtered_matches,1),
days,MOD(filtered_matches,1)*10^4,
years,WENN(SEQUENCE(matches_count),year,0),
result,DATE(years,months,days),
result)