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

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    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!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭


    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!

  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭

    @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?

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    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!

  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭
    edited 11/09/21

    @Brett Wyrick

    Does INDEX/MATCH work with two variables (Created Date and Pod #)?

    I don't see "= TODAY()" as part of your formula.

  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭
    edited 11/09/21 Answer ✓

    @Brett Wyrick

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!