Hi,
I have attached an example workbook where I have six separated columns with date/time values. In a seventh column, I am have a XLOOKUP formula that returns the heading of the column that contains the maximum value for that row.
I would like to have a formula that does the same, but which doesn't require to be copied down through the column; in other words a dynamic array version.
Does anybody know how to do that?
Regards,
Marbleking
Hi,
Try this:
=IFERROR(INDEX($A$1:$K$1,,MMULT(--(BYROW(A2:K12,LAMBDA(A,MAX(A)))=A2:K12)*(COLUMN(A2:K12)),SEQUENCE(COLUMNS(A1:K1),,,0))),"")
Thanks a lot, belinda200!
I don't have BYROW and LAMBDA in my Office 365 yet, but I guess it won't be too long.
Regards,
Marbleking
LAMBDA is in the 2020 release and BYROW in the 2021. If you sign up to the Insider Beta Channel, you can get your hands on them now and jump the queue.
https://learn.microsoft.com/en-us/of...5-apps-by-date
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Or tryBYROW and LAMBDA should be available to all EXCEL 365 usersFormula:
Please Login or Register to view this content.
Marbleking, you're welcome, hope you found how to activate these functions as they are supposed to be available if you are using MS365......
@ HansDouwe,
Can you please explain how the SORTBY works here?
when I try to apply it as a separate segment it returns an error.
So how does it work for you with the BYROW?
Thanks!
=INDEX(SORTBY(A1:K1,A2:K12,-1),1)
@Belinda,
The formula I opted is =BYROW(A2:K12,LAMBDA(r,INDEX(SORTBY(A1:K1,r,-1),1)))
If you ommit BYROW you should als ommit the LAMBDA and the first paramter of the LAMBDA.
And where that parameter is used in the rest of the function, you then have to be replaced by the first row of A12:K12 and then copy it down for all rows.
You also have to place the $ characters in the formula in such a way that only the row changes when you copy it down. In red is the replaced parameter r
In that case you get for the first row this function: =INDEX(SORTBY(A$1:K$1,A2:K2,-1),1). For the second row this function =INDEX(SORTBY(A$1:K$1,A3:K3,-1),1) and so on.
This function for the first row =INDEX(SORTBY(A$1:K$1,A2:K2,-1),1) sorts A2:K2 in a decending way and returns the corresponding values A1:K1.
Finally INDEX(<formula>,1) selects only the first value.
Last edited by HansDouwe; 11-09-2022 at 02:23 PM.
Got it! thank you so much for the explanation HansDouwe, I understand that now.
But does it work for Row 8??
An alternative:
=IFERROR(INDEX($A$1:$K$1,MATCH(MAX(A2:K2),A2:K2,0)),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
i think the OP wanted a dynamic array which "doesn't require to be copied down through the column"Originally Posted by Glenn Kennedy
Ah yes... I see that now. Yours seems fine. I was querying Hans, who (unusually) seems to have got it wrong. Normally he gets it absolutely correct!!
Here is an amendment for row 8:
=BYROW(A2:K12,LAMBDA(r,IF(SUM(r)=0,"",INDEX(SORTBY(A1:K1,r,-1),1))))
I cant complain, I learnt from him something new today : ) Thanks again Hans.
No it does not.But does it (formula post #5) work for Row 8??
But the formula of Belinda in post #12 or the formula below does:Formula:Please Login or Register to view this content.
Last edited by HansDouwe; 11-09-2022 at 04:19 PM.
Happy to see the lively discussions around this challenge.
Regards,
Marbleking
And, for my own education... another option:
=BYROW(A2:K12,LAMBDA(x,XLOOKUP(MAX(x),x,A1:K1,"")))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks