INDEX MATCH MAX Pulling Randomly
I am pulling out my hair with this formula, I have used it in this sheet in a similar fashion and in othe sheets as well but for some reason I am only getting random values returned.
In the LVC.Helper Column:
=IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, INDEX([Label Version Code]:[Label Version Code], MATCH(MAX(COLLECT([Final Approval Date]:[Final Approval Date], [MLL ID]:[MLL ID], [MLL ID]@row)), [Final Approval Date]:[Final Approval Date], 0)), [Label Version Code]@row)
The goal is to check if the MLL ID is used more than once and if so look at the Final Approval Date column to figure out which is the most recent. If there is only 1 entry I want it to return the Label Version at the row. I am getting values from random places in the sheet it would seem. I ran it through Copilot and only got further from the answer. Any help is greatly appreciated!
The screenshots below are the only places with MLL IDs in the sheet.
C1124 appears in the sheet 4 times:
Please help me make sense of this! 👏 One more screenshot for good measure!
Best Answer
-
Ended up doing two helper columns:
Most Recent Date.Helper:
=MAX(COLLECT([Final Approval Date]:[Final Approval Date], [MLL ID]:[MLL ID], [MLL ID]@row))
LVC.Helper:
=INDEX(COLLECT([Label Version Code]:[Label Version Code], [MLL ID]:[MLL ID], [MLL ID]@row, [Final Approval Date]:[Final Approval Date], [Most Recent Date.Helper]@row), 1)
Hope this helps someone in the future!
Answers
-
Ended up doing two helper columns:
Most Recent Date.Helper:
=MAX(COLLECT([Final Approval Date]:[Final Approval Date], [MLL ID]:[MLL ID], [MLL ID]@row))
LVC.Helper:
=INDEX(COLLECT([Label Version Code]:[Label Version Code], [MLL ID]:[MLL ID], [MLL ID]@row, [Final Approval Date]:[Final Approval Date], [Most Recent Date.Helper]@row), 1)
Hope this helps someone in the future!
-
Glad you were able to figure this out - thanks so much for posting your solution!
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!