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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!