Formula to return a value from another sheet based on multiple criterial

Options

I need to pull in values to one sheet (PMT Program Filing Tracker in the screenshot) based on matching two criteria in another sheet (Program Filings in the screenshot). I’m not sure of the best formula to do this.  For example (see screenshots), in the ‘PMT Program Filing Tracker’ sheet, I need to pull in a Status from the ‘Program Filings’ sheet based on matching the “Parent Program” and “State” in the ‘Program Filings’ sheet. I’ve manually filled in the result I want the formula to return in the ‘PMT Program Filing Tracker’ Program 1 line as an example.

PMT Program Filing Tracker.png Program Filing.png

Thanks in advance for the help!

Best Answers

  • Darren Mullen
    Darren Mullen Community Champion
    Answer ✓

    Kristi,

    You can use Join and Collect. If you could possibly have multiple matches, then you'd need to add some additional logic to pick the one you want, but in any case, you'd likely still need to use Collect. See here for a simple example:


    Darren Mullen, Author of: Smartsheet Architecture Solutions

    Get my 7 Smartsheet tips here

    Take your Smartsheet knowledge to the next level and become an expert. Join the Smartsheet Guru Elite

  • Andrée Starå
    Andrée Starå Community Champion
    Answer ✓

    Hi @Kristi R

    I hope you're well and safe!

    I'd recommend using an INDEX/COLLECT combination.

    Try something like this.

    =INDEX(COLLECT({Program Filings - Status}, {Program Filings - Parent Program}, 
    [Parent Program]@row, {Program Filings - State}, "FL"), 1)
    

    How many states/columns will you have? I'm asking because if you have a lot, we could simplify the formula using the first row to add the states.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!