Index(Collect(Match)) Formula Error
Hello all,
Hoping someone can help me, I keep getting a "#No Match" error for my formula. I have a neighboring column with top 5 orders and I am wanting to mirror the date that these top 5 occurrences happened (using a date column that already exists in my sheet). My formula is, "=INDEX(COLLECT(Date:Date, Hierarchy:Hierarchy, 1), MATCH([Top 5 Order Entry]@row, COLLECT(Date:Date, Hierarchy:Hierarchy, 1), 0))" I pulled only pull the top 5 days with a hierarchy 1 and I am wanting to return the date for these. Help please. :-)
Comments
-
Are you able to post some screenshots with sensitive data hidden? From the sounds of it, you should be able to remove the COLLECT functions to get the result you are looking for, but I can't be sure without more details.
Leaving the COLLECT functions in, I think you may need to change the range in your MATCH function to cover where the order entry is instead of the dates.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I like this formula, it's an interesting way of using collect. Well Done. That said,
I believe your issue is in your match statement. You are returning the date column with your collect. is [Top 5 Order Entry]@row a date or something else? If it isn't a date, then you are never going to find it on the array returned with collect with how you have it set up. The fix would be to change your return column in your collect.
-
Hi Paul,
Here are my columns, I attempted to adjust my formula per your suggestions, but now I am getting an "#incorrect argument" error. Attached is the screenshot.
-
I also attempted this formula, which in turn gave me "#invalid Column Value"
=INDEX(COLLECT(Date:Date, Hierarchy:Hierarchy, 1), MATCH([Top 5 Order Entry]@row, COLLECT([Combined Total Orders]:[Combined Total Orders], Hierarchy:Hierarchy, 1), 0))
-
Hi there, thank you for helping. All I am trying to do is return the corresponding date to the day that one of the top five order dates occurred. So the "Top 5 Order Entry" is the # of orders, and in the neighboring column I have dates (image below). I have tried a couple combinations following suggestions from community participants, but can't seem to get this to work properly.
-
The [Top 5 Order Entry] column... Is that a unique value or can that number be duplicated in the data sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Top 5 Order Entry would really never be duplicated unless we hit the exact same # of orders on numerous days (hasn't happened yet in 6 months) not to say it wouldn't happen, but not likely.
-
Ok. And I see the error in the [Top 5 Order Entry Date] field is an #INVALID COLUMN VALUE error. That column is set to date type?
Is there some way to have a unique row identifier on the data sheet? It would honestly make this MUCH easier... Even if you were to combine multiple cells across a row that would create a text string that will never be duplicated.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives