Multiple Results Formula

Hello! everyone

I was wondering if someone could help me with a formula

We have an audit schedule based on 5 years and I currently have the "Count" essentially telling me how many times a topic was audited in the past 5 years.

What I would like to generate is - "in which years was the exact same topic audited?"

which would help me in building my yearly audit schedule :)

example -

Audit Topic - "Slip and Fall Program"

How many times in 5 years was this program audited - 4 times

---------------I have it figured out till here-----------------

Seeing if the below formula exists

"In which years was this topic Audited - 2020, 2018, 2016, 2021"

I would truly appreciate your help. thank you very much!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You should be able to use an NIDEX/MATCH

    =INDEX({Source Sheet Previous Year Column}, MATCH([email protected], {Source Sheet Source Column}, 0))

    thinkspi.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot of your source data?

    thinkspi.com

  • Sorry, I don't truly understand the question - still new to smartsheet and figuring out all the ways i could make my audit schedule better

    I currently have 4 columns, which is drawing data from the Master Schedule.

    Audit Plan | Audit Topic | Times Executed | Year Executed


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What about the Master Schedule? How is that structured?

    thinkspi.com

  • below -

    Audit Plan for 2019 | Audit Source | Audit Description | Previous Years Audit |

    The " Previous Years" is a multi-drop down. This was all managed via Excel previously, included a lot of manual referencing back and forth to build the new schedule.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You should be able to use an NIDEX/MATCH

    =INDEX({Source Sheet Previous Year Column}, MATCH([email protected], {Source Sheet Source Column}, 0))

    thinkspi.com

  • Paul,

    That worked perfectly!!!

    Thank you so very much

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com