Looking for help - using index/collect potentials

Mtmoroni

I am working on a complex workspace/sheets for a sales team with commissions and quotas. A while back, I was able to get a index/collect formula to pull in the correct tier for a quota plan:

=INDEX(COLLECT({Quota - Lauren 2021}, {Quota - Lauren Minimum$ 2021}, <=[Commission MRR]@row, {Quota - Lauren Maximum$ 2021}, >=[Commission MRR]@row), 1)

However, now - I have added an additional quota plan, that I need to match dates, or look between 2 dates to pull in a specific quota plan. I have created this page and looks like this:

So, What I want is if the date is 1/30/22, I want the tiers to pull in the Quota plan for 2021. But if the date is 2/3/22, I want Quota plan 2022 tiers.

These tiers is what to take the SALE amount and use a multiplier.

Any help would be appreciated!

Jeff Reisman

If I'm reading this correctly, you want to add date criteria so that you pull the correct Quota plan based on the sale date.

You should just be able to do this with some IF/ANDs. Try this, where the Date column is the date that is your criteria, and the ranges in the second INDEX have been updated to reflect 2022:

=IF(AND(Date@row >= DATE(2021, 2, 1), Date@row <= DATE(2022, 1, 31)), INDEX(COLLECT({Quota - Lauren 2021}, {Quota - Lauren Minimum$ 2021}, <=[Commission MRR]@row, {Quota - Lauren Maximum$ 2021}, >=[Commission MRR]@row), 1), IF(AND(Date@row >= DATE(2022, 2, 1), Date@row <= DATE(2023, 1, 31)), INDEX(COLLECT({Quota - Lauren 2022}, {Quota - Lauren Minimum$ 2022}, <=[Commission MRR]@row, {Quota - Lauren Maximum$ 2022}, >=[Commission MRR]@row), 1)))