Looking for help - using index/collect potentials

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
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/04/22

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


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!