Index Match Returning Unexpected Value
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!
Best Answers
-
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.
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
@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! 😁
Answers
-
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.
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
@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! 😁
-
Glad you figured it out. Happy to help!
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!