Index Match Returning Unexpected Value

Dakota Haeffner_N

I feel like I've posted 1000 Index Match questions this year!

I have a formula (below) that is returning and individual who has the same Date Assigned field but not that same MLL ID. The MLL ID is supposed to be the thing that stops the date range from looking at all the cells. I'm trying to get Ciara as a returned value because she is the most recent individual who has had a date assigned.

AT.Helper Column: =IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, INDEX([Assigned To]:[Assigned To], MATCH(MAX(COLLECT([DA.Helper]:[DA.Helper], [MLL ID]:[MLL ID], [MLL ID]@row)), [DA.Helper]:[DA.Helper], 0)), [Assigned To]@row)

DA.Helper Column: =IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, MAX(COLLECT([Date Assigned]:[Date Assigned], [MLL ID]:[MLL ID], [MLL ID]@row)), [Date Assigned]@row)

Any help would be greatly appreciated!

AravindGP

Hi @Dakota Haeffner_N

Your DA Helper formula is updating the latest date for a particular item (in this case the MLL ID), across all rows which have the same MLL ID.

Your AT Helper formula is trying to update the result for the maximum date for a given MLL ID and find the assigned to related to it. As the DA Helper is updating the same date across all rows with the same MLL ID, the result for your AT Helper also becomes the first name that appears against the MLL ID.

I am not very sure of what you're trying to achieve, but if you can explain a bit further as to what you're trying to do, I can try to help out with a formula.

Dakota Haeffner_N

@AravindGP Hey Sorry I did a poor job explaining this.

So the AT.Helper column is the column in question. We have a Master Label Library (MLL) that has the MLL ID as the auto-generated row ID. This ID is put into this Completed Label Revisions Sheet to pull in data to the Master Label Library. The goal of the AT.Helper is to check to see if the MLL ID is used more than once on the sheet and if it is then I want it to look at the Assign To column and match it to the most recent Date Assigned which should be displaying in the DA.Helper Column. If there is just 1 MLL, it just shows who is in the Assigned To at that row.

While typing this I figured it out lol..

OLD: =IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, INDEX([Assigned To]:[Assigned To], MATCH(MAX(COLLECT([DA.Helper]:[DA.Helper], [MLL ID]:[MLL ID], [MLL ID]@row)), [DA.Helper]:[DA.Helper], 0)), [Assigned To]@row)

NEW: =IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, INDEX([Assigned To]:[Assigned To], MATCH(MAX(COLLECT([DA.Helper]:[DA.Helper], [Date Assigned]:[Date Assigned], [DA.Helper]@row)), [DA.Helper]:[DA.Helper], 0)), [Assigned To]@row)

Thanks for the help! Literally couldn't of done it without ya! 😁

AravindGP

Hi @Dakota Haeffner_N

Your DA Helper formula is updating the latest date for a particular item (in this case the MLL ID), across all rows which have the same MLL ID.

Your AT Helper formula is trying to update the result for the maximum date for a given MLL ID and find the assigned to related to it. As the DA Helper is updating the same date across all rows with the same MLL ID, the result for your AT Helper also becomes the first name that appears against the MLL ID.

I am not very sure of what you're trying to achieve, but if you can explain a bit further as to what you're trying to do, I can try to help out with a formula.

Dakota Haeffner_N

@AravindGP Hey Sorry I did a poor job explaining this.

So the AT.Helper column is the column in question. We have a Master Label Library (MLL) that has the MLL ID as the auto-generated row ID. This ID is put into this Completed Label Revisions Sheet to pull in data to the Master Label Library. The goal of the AT.Helper is to check to see if the MLL ID is used more than once on the sheet and if it is then I want it to look at the Assign To column and match it to the most recent Date Assigned which should be displaying in the DA.Helper Column. If there is just 1 MLL, it just shows who is in the Assigned To at that row.

While typing this I figured it out lol..

OLD: =IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, INDEX([Assigned To]:[Assigned To], MATCH(MAX(COLLECT([DA.Helper]:[DA.Helper], [MLL ID]:[MLL ID], [MLL ID]@row)), [DA.Helper]:[DA.Helper], 0)), [Assigned To]@row)

NEW: =IF(COUNTIF([MLL ID]:[MLL ID], [MLL ID]@row) > 1, INDEX([Assigned To]:[Assigned To], MATCH(MAX(COLLECT([DA.Helper]:[DA.Helper], [Date Assigned]:[Date Assigned], [DA.Helper]@row)), [DA.Helper]:[DA.Helper], 0)), [Assigned To]@row)

Thanks for the help! Literally couldn't of done it without ya! 😁

AravindGP

Glad you figured it out. Happy to help!