Index(Collect(Match)) Formula Error

Allison_S

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. :-) 

Paul Newcome

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.

L_123

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.

Allison_S

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. 

Allison_S

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))

Allison_S

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.  

Paul Newcome

The [Top 5 Order Entry] column... Is that a unique value or can that number be duplicated in the data sheet?

Allison_S

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. 

Paul Newcome

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.