Index Collect value based on a date

Kayla · 12/29/22

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 · 12/29/22

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.

公式表上的日期是2022年12月06。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 · 12/29/22

What if you change the

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

>=[This Sheet Date]@row

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

Kayla · 12/29/22

Final Result - updated formula.png

@Michael Culley

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

Michael Culley · 12/29/22

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

Michael Culley · 12/29/22

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 · 12/29/22

@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 · 12/29/22

@Paul NewcomeCalling in an expert lol

Paul Newcome · 12/29/22

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.

公式表上的日期是2022年12月06。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 · 12/30/22

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 · 12/30/22

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 · 12/30/22

Happy to help.️

这是一个奇怪的错误在运行。通常,我ans 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.

Index Collect value based on a date

Best Answer

Answers

Help Article Resources

Categories