Index Collect value based on a date

Kayla

I want to index a value if the "This Sheet Date" on my final result sheet is greater than "Date Value Effective" on my database sheet.

Screenshots should help. I have a column showing the value that I want it to return. It's showing me the 2% because technically all of the "This Sheet Dates" are greater than the "Date Value Effective", but I want it to update if there is a more recent date entry.

It's right under my nose and I can't see it!

Paul Newcome

Give something like this a try...

=IFERROR(INDEX(COLLECT({Value}, {Manager}, @cell = [Employee Manager]@row), COUNTIFS({Date}, @cell<= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row)), "")

Since the reference sheet is being fed via copy row automation, we want to assume that all entries will be in chronological order from top to bottom with new entries going at the bottom of the sheet (so we don't have to actually interact with the reference sheet).

So what we do is COLLECT all of the {Value} where the {Manager} is what we are looking for. We then INDEX this list of values, and counting how many dates (for that manager) are less than or equal to the sheet date should give us the entry number from the COLLECT function to pull.

Example:

We COLLECT all of the values where the manager is John Smith. This gives us a list with 3 entries on it.

The date on the formula sheet is 06 December 2022. This means our COUNTIFS is going to output the number 2 which tells the INDEX function to pull the second entry from our list generated by the COLLECT function.

I haven't had any coffee yet today. The explanation may not make much sense, but the formula should work for you. If you need clarification on anything, please don't hesitate, and I will try to explain a different way (after I have had some coffee).

Michael Culley

What if you change the

[This Sheet Date]@row >= @ cell to

>=[This Sheet Date]@row

So basically {DateValueEffective},>=[This Sheet Date]@row

Kayla

Final Result - updated formula.png

@Michael Culley

Didn't work. This is what I get..

Michael Culley

Ohhh I see. Just curious, Is this sheet being populated via a form?

Michael Culley

I'll bet if you sort your TEST Database Log Changes by Date Descending it would work. If that sheet is being populated by a form you could have new entries be placed at the bottom instead of the top which would put the newer dates at the top.

Kayla

@Michael Culley, it's being populated with a copy row automation, so I cannot control the sort. But sorting doesn't do the trick with this either.

Michael Culley

@Paul Newcome Calling in an expert lol

Paul Newcome

Give something like this a try...

=IFERROR(INDEX(COLLECT({Value}, {Manager}, @cell = [Employee Manager]@row), COUNTIFS({Date}, @cell<= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row)), "")

Since the reference sheet is being fed via copy row automation, we want to assume that all entries will be in chronological order from top to bottom with new entries going at the bottom of the sheet (so we don't have to actually interact with the reference sheet).

So what we do is COLLECT all of the {Value} where the {Manager} is what we are looking for. We then INDEX this list of values, and counting how many dates (for that manager) are less than or equal to the sheet date should give us the entry number from the COLLECT function to pull.

Example:

We COLLECT all of the values where the manager is John Smith. This gives us a list with 3 entries on it.

The date on the formula sheet is 06 December 2022. This means our COUNTIFS is going to output the number 2 which tells the INDEX function to pull the second entry from our list generated by the COLLECT function.

I haven't had any coffee yet today. The explanation may not make much sense, but the formula should work for you. If you need clarification on anything, please don't hesitate, and I will try to explain a different way (after I have had some coffee).

Kayla

That worked and makes complete sense, but certainly nothing that would have crossed my mind! THANK YOU.

I am however not able to get rid of the error showing if the date doesn't match a date on the referenced sheet, even with IFERROR. I've never come across this before... I need the value to default to 1% if there isn't a match, and I planned to use IFERROR, but this formula doesn't like that for some reason. I haven't messed around with it, but if you see anything obvious - please share.

Kayla

I fixed it with this formula:

=IF(COUNTIFS({DateValueEffective}, @cell <= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row) = 0, 0.01, INDEX(COLLECT({VALUE}, {Manager}, @cell = [Employee Manager]@row), COUNTIFS({DateValueEffective}, @cell <= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row)))

Paul Newcome

Happy to help. 👍️

That's an odd error to run across. Usually that means you are pulling in the wrong type for the column type such as pulling a number into a date type column or a date into a text/number column, and the IFERROR should have gotten rid of that. 🤔

Glad to see you were able to get it sorted though.