Use IF/AND to find 2 values (TODAY's date and another cell), then return a different cell's value
How can I search a 'range' to return the value from Row 23 "Goal Date (PS)" (11/4/21) using an IF/AND based on Created date (TODAY) and Pod # (Pod 3)?
Best Answer
-
I figured it out! Thank you for your help.
=INDEX(COLLECT([Goal Date (PS)]:[Goal Date (PS)], Created:Created, TODAY(), [Pod #]:[Pod #], "Pod 3"), 1)
Answers
-
Within your formula, instead of "[Goal Date (PS)]:[Goal Date (PS)]", replace that with "[Goal Date (PS)]@row" if you're wanting the value from the row, otherwise replace it with "[Goal Date (PS)]23" if you specifically want the value from row 23. Does that work?
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
@Brett Wyrick Hi Brett! Thanks for speedy response.
If I use "@row" it wants to return the "Goal Date (PS)" value from Row 13, not Row 23.
No, I don't want to specifically code in Row 23 because the Row number will be different the next day. I want it to scan all Rows for Today's date and "Pod 3".
Is there a way to use the range?
-
I think I know what you're going for, but I'm a bit unsure still.
You'd probably want to utilize Index/Match functions.
Tell me if this formula gets what you're going for:
=INDEX([Goal Date (PS)]:[Goal Date (PS)], MATCH(LEFT([Primary Column]@row, 5), [Pod #]:[Pod #]))
Here's how INDEX/MATCH works, in a nutshell:
=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
-
Does INDEX/MATCH work with two variables (Created Date and Pod #)?
I don't see "= TODAY()" as part of your formula.
-
I figured it out! Thank you for your help.
=INDEX(COLLECT([Goal Date (PS)]:[Goal Date (PS)], Created:Created, TODAY(), [Pod #]:[Pod #], "Pod 3"), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!