Hello,
I'm using the xlookup function in order to date the day with a value greater than 450.
The problem is when a value is recurring more than once, it will pick the earliest find instead of the chronologically correct day.
How can i work around this?
See day column jumping from 261 to 25
Skærmbillede 2021-02-17 113458.jpg
(edit: Excel sheet attached)
Last edited by norgaards; 02-17-2021 at 10:28 AM.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks, now attached
you might try switching to an AGGREGATE approach
I5: =IF(J5="";"";AGGREGATE(15;6;$A$5:$A$735/($C$5:$C$735=J5);COUNTIF(J$5:J5;J5)))
copied down
another alternative, to leverage dynamic output, would be to expand your FILTER, e.g.
=FILTER(A5:C735;C5:C735>=450)
and then just hide/collapse (via Group) the unwanted columns in resulting table return
Last edited by XLent; 02-17-2021 at 08:07 AM.
edit: better than the above, you could use something like below (in UK format):
=FILTER(FILTER((A5:C735),C5:C735>=450),{1,0,1})
the 2nd FILTER hides the unwanted column (c/o 0 in 2nd position (col B))
you may need to modify / tinker with the delimiters to get it to work in your locale.
Thanks! I ended up just using the if function, works very well, as it is only for visual inspection and grouping of data.
Have a nice day.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks