INDEX MATCH MAX Pulling Randomly

Dakota Haeffner_N

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!

Dakota Haeffner_N

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!

Dakota Haeffner_N

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!

Georgie

Hi @Dakota Haeffner_N,

Glad you were able to figure this out - thanks so much for posting your solution!

Georgie