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!
Answers
-
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)))
Regards,
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!