can i use index collect to find a value based on a date?

Brandon Morales

through use of a form i have a sheet that will create rows with certain departments and issues they've encountered. i have a master sheet that i want to populate with the most recent issue encountered.

My thought is the first find the max date for a department and then use index(collect to collect the first issue that matches that max date. Will this work? when i try i get incorrect argument set..

Paul Newcome

Try putting a comma followed by a one in between the last two closing parenthesis.

), 1)

Michelle Choate 2

Can we see a screenshot of your sheet and then your formula?

Paul Newcome

It should be possible. I have done this plenty of times before. Are you able to provide some screenshots for context?

Brandon Morales

here is my formula

and here are the 2 columns-is the value to index/collect and the other the citeria.

in this caser im looking for the 1st job batch that matches my date

Paul Newcome

Try putting a comma followed by a one in between the last two closing parenthesis.

), 1)

Brandon Morales

IT WORKED!! THANK YOU!

What does the comma, and one represent? when this criteria is true? (just curious)

Paul Newcome

It is the second portion of the INDEX function. The COLLECT function generates a list from the first range based on the rest of the range/criteria sets within it. You then "wrap" the COLLECT function's list in the INDEX function to be able to pull a specific entry from that list. The comma starts the second portion of the INDEX function, and the one tells it to pull the first entry from the list.